1,489 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
225
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
159
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
97
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
110
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
2
votes
1
answer
74
views
Table update efficiency, Oracle 11G
I am a relatively new developer in PL-SQL Oracle11g.
As part of my studies, I am in a debate with the lecturer regarding the effectiveness of the UPDATE operation on a table.
In the following code, I ...
- 21
0
votes
0
answers
203
views
Execution Plan keeps showing a missing Index when I have the index
I have already created the missing index on the table and the plan keeps telling me there is a missing index?
<MissingIndexes>
<MissingIndexGroup Impact="10.6268">
<...
- 17
2
votes
1
answer
441
views
Force Index seek rather than scan to get next record in T-SQL
I have a database with many tables. I need a query to read the next record in key order, given the keys of the current record. [NOTE: I am emulating an old C-ISAM based system, which reads records one ...
- 25
1
vote
1
answer
316
views
Full table scan in nested query in Snowflake
After particular time on solving task and reviewing Snowflake documentation I noticed potential improvement in query for readability and possibly performance improvement. My query is using nested ...
3
votes
1
answer
227
views
Why is Query Optimizer Underestimating and Causing Data Spill to tempdb?
I have a very simple query that I am testing by running ad-hoc.
The execution plan indicates that data is spilling into tempdb because of poor estimates.
I have been unable to resolve any of the three ...
- 33
0
votes
0
answers
89
views
Elapsed Time in QEP returned from sys.dm_exec_query_plan_stats is different from the one in Actual Execution Plan returned by SSMS
I am trying to use the LAST_QUERY_PLAN_STATS property to save the QEP Actual of each executed statement in my repository,
using DMV sys.dm_exec_query_plan_stats I am able to intercept it and save it ...
- 1
0
votes
0
answers
79
views
Why do I get a Clustered Index Scan instead of a Table Scan? [duplicate]
I have these simple tables, one without indexes and the other with only a primary key and clustered index on id:
CREATE TABLE [dbo].[MyTableWithoutPk]([id] [int] NOT NULL, [category_id] int NULL)
...
- 37.3k
2
votes
1
answer
597
views
Do I trust "cost" or "actual time" more when using Postgres EXPLAIN ANALYZE?
I'm trying to improve the performance of one of our most expensive queries. I'm running EXPLAIN ANALYZE on the sandbox postgres 15 database, which has a much smaller dataset than the production ...
0
votes
0
answers
65
views
Mysql select with WHERE (a, b) in (('str1', 'str2'), ...., ('str6000', 'str6000')) taking 30 seconds to execute
I have table of the following structure:
create table my_table
(
id int auto_increment
primary key,
name varchar(255) null,
vendor_name ...
0
votes
1
answer
220
views
Simple insert times out in application, but fast in SSMS
In my application I have a simple insert, when captured with profiler it looks like this
insert into ford.tblFordCompoundFlowVehicle
(FordCompoundFlowID, CompoundVehicleID, SortOrder, Status1ToSend, ...
- 12.3k
1
vote
1
answer
181
views
Discrepancy between cost and row count estimates in nested loop
I have this snippet of a PostgreSQL explain plan I'm trying to comprehend:
-> Nested Loop (cost=0.57..933455.16 rows=11 width=122) (actual time=3.710..497.990 rows=86102 loops=1)
-> ...
- 1,147
0
votes
2
answers
201
views
Used statistics from XML plan cache for query with given QueryPlanHash
I'm using SQL Server 2016/2019. I want to extract (for given @QueryPlanHash BINARY(8) = 0x397CEDB37FA0E1D2) from execution plan XML in cache - which statistics was used to generate that plan.
This ...
- 21
1
vote
2
answers
1k
views
How to make Postgres use an index for a set of values?
I have a table with ~35M rows, and trying to find "processed" records to remove from time to time. There are 14 valid statuses, and 10 of them are processed.
id uuid default uuid_generate_v4(...
- 6,848
0
votes
1
answer
198
views
MySQL(5.7.26) query optimization, why select * is much faster that select id in my leading wildcard query?
I have a leading wildcard query I know it is hard to optimize. I know if I use only the trailing wildcard I can do some optimization. But our client wants the leading wildcard because when searching ...
- 12.9k
1
vote
0
answers
43
views
postgresql EXPLAIN gives seq scan rows = 12 for filter id < 10
I ran explain command on this query:
EXPLAIN
Select * from project_project where id < 10 FOR UPDATE
And the plan output:
"LockRows (cost=0.00..6.57 rows=12 width=18305)"
" -> ...
- 4,161
0
votes
1
answer
95
views
How to ensure use of an index?
I have the following query:
SELECT cp.ID_Case
FROM dbo.CaseParty cp (NOLOCK)
JOIN dbo.Client cli (NOLOCK) ON CASE
WHEN cli.ClientType = 'atty' AND cp.ID_ClientAttorney = cli.ID_Client THEN 1
...
- 62.2k
0
votes
0
answers
118
views
Non-deterministic execution plan on Postgres
Fairly simple schema:
CREATE TABLE IF NOT EXISTS Queue(
Id BIGSERIAL NOT NULL PRIMARY KEY,
SendAt TIMESTAMP(3) NOT NULL,
Payload TEXT NOT ...
- 2,545
3
votes
2
answers
195
views
Estimated execution plan alternatives
As far as I understand, given a query, Oracle tries different approaches based on some heuristics, where I guess the most driving factor is minimizing the number of rows to work with, and then chooses ...
- 12.5k
0
votes
2
answers
139
views
Complicated column dependencies in postgreSQL
I have two columns: column1 with two distinct values (0, 1) and column2 with 3 distinct values ('A', 'B', 'C'). Value in column1 is always 0 for 'A' and 'B' in column2, but if the value in column2 is '...
- 33
2
votes
1
answer
767
views
How to get EXPLAIN output with a SELECT statement?
I'm trying to grab a query plan for my query. However, I do not have direct access to the db and can only run SELECT statements on my end.
Is there a way to wrap SELECT around EXPLAIN, maybe even ...
0
votes
0
answers
84
views
sum of cpu cost present in every node in the actual query plan vs, STATISTICS TIME ON, both have big difference number
in general Why does the sum of CPU times from individual nodes in the query's execution plan differ significantly from the total execution time reported by SET STATISTICS TIME ON? assume the query ...
-1
votes
2
answers
131
views
Understanding the Execution Plan Discrepancy in a MySQL Query Involving Left Joins and Filtering
I wrote a SQL query to fetch all movies and, if present, include the name 'Brad Pitt' for movies where he's a cast member. The query is as follows:
select m.title, p_cast.person_name
From movie m ...
-1
votes
1
answer
519
views
Optimize MariaDB simple query and what in fact cost is in explain json format statement
I have 2 questions:
Is it any way to optimize or rephrase this query that I will be able to execute it? I was waiting for over 2 days and the query was still executing...
How does the "cost&...
- 85
0
votes
1
answer
58
views
Why is this Postgresql UPDATE statement so slow even when it doesn't update any rows? [closed]
I see that a full table scan is planned, however it's never executed and the UPDATE takes a long time anyway. Why??
Here's the EXPLAIN output
Update on public.hone_cohortuser (cost=3180.32..8951.51 ...
- 1,484
1
vote
1
answer
96
views
cache position when same dataframe name used
I have dataframe called source_dataframe which is referenced by multiple places in the pyspark code. Hence, I was planning to cache the data frame source_dataframe, so that cached referenced would be ...
- 377
1
vote
1
answer
244
views
How do I make postgres use an index when the query condition references a subquery?
I have a table 'analytics_event' (with 9.7M rows) which I need to query for records 'created' after a given time. The table has various indexes which are being ignored in favour of a seq scan over the ...
- 13
2
votes
1
answer
441
views
plan_handle is always different for each query in SQL Server Cache
I'm evaluating some Plan Cache behaviors and this is my scenario.
I'm running the following two queries separately:
SELECT TOP 10 *
FROM dbo.Countries CT
LEFT JOIN dbo.Continents CN ON CT.ContinentId=...
- 31
1
vote
1
answer
279
views
What does Type "ref|filter" mean in mariaDB Explain Output
Trying to troubleshoot a MariaDB query. We did an explain select on the select. A couple of the rows in the output have value ref|filter for the type column.
What does this mean? The MariaDB docs ...
- 6,812
0
votes
0
answers
135
views
Select query takes too long to execute
Below is the select query which takes 1515 seconds to finish. Can some one help me improve the query performance by looking into the execution plan provided below.
As mentioned in the execution plan, ...
- 97
2
votes
1
answer
987
views
PostgreSQL Hash Join vs Nested Loop and hash index
I'm testing how join works with hash index in PostgreSQL 16.2. Here is a test table. Just 2 columns with numbers in text format.
create table join_test (
pk varchar(20),
fk varchar(20));
...
- 370
-1
votes
1
answer
148
views
How do I test the performance of an SQL query with parameters in SSMS?
Using ActivityMonitor, I got an SQL query that is slow.
It looks like this:
SELECT 1, 2, 3,
FROM MyTable
WHERE Column1 = @parameter1 AND Column2 = @parameter2
How do I execute the query with ...
- 27.8k
2
votes
0
answers
319
views
Performance Issues After Migrating from MariaDB 10.3.39 to 10.6.16 Due to Changes in Query Execution Plan
We recently migrated our database from MariaDB version 10.3.39 to 10.6.16 and have encountered significant performance issues with many of our older, mostly unoptimized queries. We've isolated one ...
- 21
1
vote
1
answer
4k
views
Exact meaning of actual times in PostgreSQL explain analyze
There are many places that talk about this question but none fully explain it - all leave it ambiguous. Examples:
PostgreSQL's own documentation
pgMustard's page on the topic
From what I've been ...
- 1,333
1
vote
2
answers
426
views
How to improve sort performance in a huge dataset of 20 million rows?
So I have this query which is extremely slow to run -
SELECT
"conversation".*,
"teamBotLocation"."id" AS "teamBotLocation.id",
"teamBotLocation&...
- 13
1
vote
0
answers
109
views
PostgreSQL query plans when using unnest() in SELECT list vs in subquery
I have two queries that would produce equivalent results (though the subquery one is sorted). However, it seems like the one that uses subquery is faster and I'm not sure why. Could someone explain ...
- 891