1,488 questions
0
votes
1
answer
216
views
Make Postgres query fast without UNION
The ORDMBS is PostgreSQL 17.5 on x86_64-suse-linux-gnu, compiled by gcc (SUSE Linux) 7.5.0, 64-bit.
I have big table (about 150 GB), partitioned.
Full table description:
CREATE TABLE table_partition
(...
1
vote
0
answers
78
views
Postgresql explain hangs on query from table with subpartitions
I'm trying to understand what is going on with the explain. I've got Postgresql 14, partitioned table with subpartitions and two queries.
First one works not optimal and I'm trying to rewrite it but ...
- 21
0
votes
0
answers
48
views
Why isn't MySQL using appropriate indexes when available? When forced to use them, the performance difference is dramatic; is this normal?
I've got the following SELECT statement where all columns are properly indexed with the column they're joining on. I.e. all PKs & FKs have a corresponding, single-column index shown later ending ...
- 887
1
vote
1
answer
67
views
force postgresql client to output valid json after EXPLAIN
when in psql:
postgres=# EXPLAIN (ANALYSE, FORMAT JSON) SELECT a FROM t;
QUERY PLAN
-------------------------------------------------------
[ ...
- 181
2
votes
1
answer
127
views
Allow query planner to eliminate a left join even when not joining on a unique key
A nice feature of SQL Server's query planner is that if you do not use any columns from a joined table, and the join does not affect the cardinality of the query, it can be eliminated altogether. You ...
- 1,622
3
votes
3
answers
229
views
T-SQL query very slow when using index on Date column
I have a table PaymentItems with 8 million rows. 100'000 rows have the foreign key PaymentItemGroupId = '662162c6-209c-4594-b081-55b89ce81fda'.
I have created a nonclustered index on the column ...
- 939
1
vote
2
answers
164
views
Query hint for IndexScan shows no effect, table still uses SeqScan
We have two tables in a Postgres DB. Table1 is a small temp table, Table2 is a huge regular table (> 1 million rows):
-- small, temp:
CREATE TEMPORARY TABLE Table1 (
uid VARCHAR(15)
, idx INTEGER
...
- 39
1
vote
0
answers
52
views
Why is SORT GROUP BY used in a SQL Plan for a query without a Group By (Oracle Database)?
I'm executing a query as follows:
SELECT aad.TABLE_A_ID,
JSON_OBJECT(
'details' value (SELECT JSON_ARRAYAGG(
JSON_OBJECT(
'domId' value aadd.TABLE_B_ID
)
) ...
- 601
-3
votes
1
answer
72
views
Query Plan for different parameter length [closed]
I have a parametized query that receives a string as input. This string varies in length, but never smaller than 10 digits, never longer than 14.
When I look at sys.dm_exec_query_stats I can see that ...
- 11.5k
3
votes
3
answers
227
views
Difference between SQL Operations: MERGE STATEMENT and MERGE?
What’s the difference between dba_hist_active_sess_history.sql_plan_operation's MERGE STATEMENT (line Id 0) vs. MERGE (line Id 1)?
For example, consider the following abbreviated execution plan and ...
- 550
0
votes
1
answer
97
views
oracle sql query brings the result for a long time
I have written a query in oracle sql as follows. But the sql query brings the result for a long time. For example, when searching for a customer, the result comes in 4 minutes. Please help me to ...
- 19
1
vote
1
answer
83
views
Same schema on two different servers show different COST and (%CPU) in the Explain plan
We have two servers having duplicate schemas. We are seeing a difference in the COST and (%CPU).
Here are the plans:
For the first server (with 12 cores as it's a development environment):
------------...
- 27
0
votes
1
answer
98
views
When the index is the same, the database version is the same, but the same SQL statement has different execution plans on different hosts
I am currently using the version 8 of Dameng database. In SQL query sentences, I am dealing with a very large table. I have created the same unique index on this table in the development environment, ...
1
vote
1
answer
98
views
Why does the query optimer choose a serial plan when filter made less restrictive
I'm working through Iztik Ben-Gan book T-SQL Querying.
I have some trouble understanding why making a filter less restrictive will make the optimzer move away from a parallel plan to a serial one.
The ...
1
vote
1
answer
112
views
Fetch the top 5 efficient query plans for a specific object in SQL Server
I am trying to analyze the performance of different query execution plans for a specific object in SQL Server. My goal is to retrieve the top 5 most efficient query plans so that I can compare them ...
- 107