Skip to main content

Questions tagged [execution-plan]

The strategy selected by the query optimizer to process a query.

Filter by
Sorted by
Tagged with
0 votes
0 answers
17 views

Working around Materialize node in PostgreSQL explain plan

I'm currently trying to optimize the following query for a school assignment: SELECT f.aircraft_code, s.seat_no, SUM(tf.amount) total_amount FROM seats s JOIN flights f ON s.aircraft_code =...
sotnififu's user avatar
4 votes
1 answer
112 views

Why does adding an empty columnstore index to my tables consistently make execution plans ignore bitmaps?

Problem I am using a Standard Edition SQL Server 2019 box. Given the edition, compatibility levels should not be relevant (the level-dependent batch mode features are disallowed on Standard). On this ...
J. Mini's user avatar
  • 537
-1 votes
1 answer
24 views

How do we optimize a transaction-log VIEW in MySQL that will use a LIMIT when executed?

Problem / Observation A query that performs well outside of a view (with a limit 20), uses a suboptimal query plan when placed inside of a view if the limit 20 is not included in the view definition ...
Rob R's user avatar
  • 9
2 votes
1 answer
37 views

Oracle optimizer does not use column group information from multiple tables and misses correlation

I have a problem where the execution of a query is too slow in oracle 12c standard edition. I figured out, the optimizer estimates wrong cardinalities because it misses the correlation between two ...
egen's user avatar
  • 21
2 votes
1 answer
163 views

Why is 'ParameterCompiledValue' missing from execution plan XML?

In this question Is it parameter sniffing? there is an execution plan attached with the XML. Why is ParameterCompiledValue missing from the XML of the execution plan? <ParameterList> ...
SqlWorldWide's user avatar
  • 13.5k
0 votes
1 answer
32 views

Question related to Database Design in SQL Server

I have two tables: Products and ProductVersions. The table structures are as follows: Products Table: ProductId RegionId ActiveProductVersionId isDisabled ApplicationId ProductVersions Table: ...
lifeisajourney's user avatar
0 votes
1 answer
29 views

Citus: Parallel worker not launching

We are performing Citus benchmarking and came across a situation where the planner makes the parallel execution plan but during the execution no parallel workers are launched. The cluster has 3 worker ...
goodfella's user avatar
  • 168
7 votes
1 answer
249 views

Filter by partition number when the table is partitioned by computed column

I've created 2 identical tables partitioned by an integer column. In one of the tables the column is computed, in the other table the column is not computed. When I query the table with computed ...
Artashes  Khachatryan's user avatar
1 vote
1 answer
100 views

Gather Operator Performance Regression

Had some success improving query performance with parallelism, following guidance from here and pgtune. For reference this is analytical workload, not transactional. First plan, running v16 on EC2 r5b....
Mark Storey-Smith's user avatar
0 votes
2 answers
32 views

Postgres query planner suddenly changing his mind

My Postgres table has a range column containing timestamps with time zone. I have created an index on the lower bound of the range, like so: CREATE INDEX bdg_sys_period_start_idx ON building USING ...
Istopopoki's user avatar
0 votes
0 answers
20 views

window function does not use index

I have a query that uses a window function like this: SELECT stage_account_060_exploded_all_dates.date AS date , stage_account_060_exploded_all_dates.client_id AS client_id ...
Bart Jonk's user avatar
  • 317
7 votes
1 answer
627 views

Execution slow in stored procedure; fast when run ad hoc

I am dealing with an issue that I can't seem to solve. I was given this stored procedure that has speed issues. It takes 35 seconds or so. When run adhoc, it completes instantly. This made me think it ...
anakata's user avatar
  • 73
1 vote
1 answer
110 views

Why does MySQL choose such a poor query plan for "IN (SELECT NULL WHERE FALSE)"

We have some code that goes through a SQL generator to produce parameterized IN clauses of the form WHERE id IN (?, ?, ?, ...) AND .... If the list is empty, the generator cannot issue IN () since ...
ChaseMedallion's user avatar
0 votes
1 answer
139 views

Azure SQL Managed Instance: 1. excessive initial compile times leading to app timeouts and 2. partition maintenance: long-running, bloats data file

We are having a couple of issues preventing us from going live with an on-prem SQL 2016 Std Ed to Azure SQL Managed Instance Business Critical tier re-platform, and I was wondering if anyone had ...
Mike Petrak's user avatar
4 votes
1 answer
96 views

Why is my SQL Server prefering to pick one plan over the other when there are 2 executions plans for the same query in the cache?

I have a 2019 SQL Server with a database with 'FORCE PARAMETERIZTION' set to true and a nightly job which does sp_updatestats on it. One of the application has a feature which has two modes, a "...
QFirstLast's user avatar
0 votes
3 answers
48 views

Why is the Postgres query planner choosing the wrong strategy?

I have a Postgres/Postgis table with ~50M rows. I am making requests similar to this: select * from building b where ST_Intersects(b.point, some_fixed_geometry) order by id limit 20 offset X; The ...
Istopopoki's user avatar
9 votes
1 answer
688 views

Extra NESTED LOOP / INNER JOIN causing NO JOIN PREDICATE warning

I have the following tables in my database. LastNames Table CREATE TABLE [dbo].[LastNames]( [LastNameID] [int] IDENTITY(1,1) NOT NULL, [LastName] [varchar](50) NOT NULL ) ON [PRIMARY] GO ...
John K. N.'s user avatar
4 votes
2 answers
177 views

Index seeks and predicate push down

I have a query of the following form: IF EXISTS ( SELECT 1 FROM ( SELECT RowID, OETID FROM @InMemoryTableTypeTable i UNION ...
Dan Def's user avatar
  • 165
0 votes
1 answer
27 views

SQL Server management Studio does not show Index recommendation

I have installed SQL Server management studio version 20 (and also tried with 19 and 18) and surprisingly unable to see the Missing Index Hints while explaining a SQL Query. Whenever i receive a SQL ...
Parth's user avatar
  • 1
0 votes
1 answer
54 views

How is Memory Grant Desired Memory calculated

I have the following query in the StackOverflow2010 database: SELECT Id, OwnerUserId, CreationDate, Body FROM Posts ORDER BY LastEditorDisplayName The execution plan shows me ...
SE1986's user avatar
  • 2,000
0 votes
1 answer
124 views

Why is COMPATIBILITY_LEVEL: 160 using Clustered Index Scan?

Disclaimer: I'm a developer dwelling in databases, so my knowledge is limited We recently upgraded our MSSQL Server from 2012 to 2022 and in that regard also updated the COMPATIBILITY_LEVEL from 110/...
Dumpen's user avatar
  • 109
1 vote
0 answers
44 views

Selecting joined column kills index and results in very slow query

I recently added a JOIN to a query look up one of three names based on id. Normally this has little to no effect on performance but for some reason in this case when I SELECT the column in question ...
Eaten by a Grue's user avatar
0 votes
0 answers
34 views

Performance - where clause not using index due to null verification, how do i use the index seek in this code without doing code duplication? [duplicate]

question : how do i use the index seek in this code, without doing code duplication? I've got this code : CREATE procedure [dbo].[GetActiveEntitiesByRecord] ( @MainId bigint , @...
dexon's user avatar
  • 47
2 votes
1 answer
183 views

Error: type of parameter n (X) does not match that when preparing the plan (Y)

I'm encountering an issue with a PL/pgSQL function where it returns different data types based on a condition, and it's resulting in a type mismatch error. Here's a simplified version of the function: ...
Roberto Iglesias's user avatar
1 vote
2 answers
88 views

QueryStore plan forcing errors

We have a weekly stored procedure that ocassionaly picks up wrong execution plan that causes much longer execution times. I've tried to force correct execution plan but still every few weeks the ...
Andrzej Błoch's user avatar
0 votes
2 answers
118 views

Finalize GroupAggregate parent using Partial HashAggregate child nodes in Postgres query plan

I have seen in some query plans that the parent is a Finalize GroupAggregate but its child nodes are Partial HashAggregates. When does this make sense? As an example, I have a query that resembles ...
ahron's user avatar
  • 813
0 votes
2 answers
89 views

PostgreSQL plan cache and variable number of arguments

Query: select * from table where col in ( values (?), (?), (?) ) The query text is generated, and number of (?) is variable and up to 10000. It works just fine, just wondering, whether Postgres ...
Mikhail Boyarsky's user avatar
1 vote
0 answers
135 views

Why is PostgreSQL performing a sequential scan, except when my CTE is materialized?

The issue My application is encountering performance issues on a query that, to the best of my understanding, should be performant. In this post, I have simplified the query and schema while still ...
sgielen's user avatar
  • 111
1 vote
1 answer
143 views

Terrible performance joining a view to a fast subquery / CTE

I have a PostgreSQL (v15) DB view that rolls up a bunch of data per user for a single organization, to display a report of data like fees owed/paid per user, etc. This is executed with an org ID and ...
Brian Moeskau's user avatar
0 votes
2 answers
106 views

Cheaper query plan takes much longer to execute

I am running a query like explain (analyze, buffers) select col1, col2, count(col3) as c from table1 group by 2, 1 order by 2, 1 When the work_mem is set at 4 MB, the plan looks like this: "...
ahron's user avatar
  • 813
0 votes
1 answer
83 views

Is it possible to use both GIN index and pgvector index for querying in postgres?

I have a table like below in postgres: create table posts ( id bigserial, tags text[], content text, content_embedding vector(512) ); create index on posts using GIN(tags); -- from ...
Nguyen Nguyen's user avatar
0 votes
2 answers
75 views

Query is much slower with indexes

For a select query on a table with a few million rows, I notice that the performance is much slower with indexing. I have replicated the problem on an example table with fewer columns: CREATE TABLE ...
ahron's user avatar
  • 813
0 votes
1 answer
36 views

Evaluation order (commutativity/associativity) of ANDed terms in Postgres WHERE clause

Is Postgres' query planner able to rearrange terms in a WHERE clause according to the rules of commutativity and associativity? For example, do the following WHERE clauses all result in a query plan ...
Luke Hutchison's user avatar
3 votes
1 answer
515 views

Understanding and Optimizing Duplicate Query Hashes Across Multiple Databases in SQL Server

I'm working with a SQL Server instance that hosts thousands of databases. Each database corresponds to a different customer, but they all share an identical schema. Recently, I've observed that many ...
Just a learner's user avatar
0 votes
1 answer
40 views

Vastly different query performance on GIN indexed table using different search words

Problem Text searches in a Postgres table with a simple GIN index ordered by creation timestamp have wildly different latencies based on the keyword that is being searched. While searches for frequent ...
Stefan FPF's user avatar
0 votes
1 answer
221 views

Postgres filter timestamptz column by date very slow (possibly index not used?)

I have the following table with 462541359 rows. create table "Prices" ( "Id" bigint generated by default as identity constraint "PK_Prices" ...
derteufelqwe's user avatar
3 votes
2 answers
558 views

How to delay convert() evaluation until after join

The following query converts a single string of packed CSV that represents 13k rows by 2 columns. Column A is a bigint. Column B is a smallint. declare @dataCsv nvarchar(max) = '29653,36,19603,...
crokusek's user avatar
  • 2,120
1 vote
0 answers
67 views

Postgresql 15 query optimization question - why does a cast fix this?

Ever since we upgraded to PG15, we have some queries that just aren't performing well. The optmizer is picking poor strategies even for simple left joins (the exact same query & data in MySQL runs ...
Jon Sherrill's user avatar
1 vote
1 answer
203 views

Best Practices for searching SQL Server NVARCHAR col using WHERE LIKE wildcard term

I have a pretty simple table SQL Table (currently Azure SQL, may stay there or end up in local SQL Server 19 instance if that matters), about 100,000 records, that's mostly for reading but will ...
mcsov's user avatar
  • 15
0 votes
2 answers
204 views

Aside from explicitly flushing the cache or demanding recompiles, what recompiles a full stored procedure in SQL Server 2019?

I recently finished reading Plan Caching in SQL Server 2008 and I have become confused. It seems that, short of totally flushing the plan cache or explicitly demanding a stored procedure to be ...
J. Mini's user avatar
  • 537
1 vote
2 answers
192 views

Optimize query with many joins

The developers came asking to improve the query performance. I tried using CTEs instead of lateral joins and EXISTS, testing covering indexes, additional filtering. No significant performance benefits....
George K's user avatar
  • 2,296
2 votes
1 answer
298 views

optimize SELECT MAX() with GROUP BY in Postgres?

I'm trying to speed up the following query in postgres: select MAX(msg."timestamp") AS latestDate, msg.channel_id from message msg group by msg.channel_id the explain is as such: Finalize ...
tuskiomi's user avatar
  • 123
5 votes
0 answers
103 views

Row estimates incorrect when using RLS and current_setting in policy

Context Postgres 14.4. I have a table which has RLS enabled, and I am using values pulled from current_setting in order to determine which rows should be returned when querying the table: create table ...
cjheppell's user avatar
  • 213
6 votes
1 answer
438 views

How does SQL Server optimize JOIN on hierarchyid::IsDescendantOf()?

I have table with tree structure (defined by hierarchyid column) and I want to select all descendants of particular record. For that, I am using hiearchyid.IsDescendantOf() method. I expected, that ...
Yano_of_Queenscastle's user avatar
0 votes
1 answer
54 views

Database Structure/Planning Queries

I'm looking for any guidance possible and not necessarily asking for "the solution" as such. In a MySQL database, I want to understand how to do a capacity analysis for a company. Such that ...
Ed Stephenson's user avatar
1 vote
1 answer
158 views

Why index scan can be slow in Postgres?

I have a query: WITH route_ids_filtered_by_shipments AS ( SELECT DISTINCT rts.route_id FROM route_to_shipment rts JOIN shipment s ON rts.shipment_id = s....
Nikita Glukhov's user avatar
2 votes
0 answers
72 views

Why does the query plan change dramatically and never complete with a single additional where clause?

The following postgres query changes quite a bit by a single additional WHERE condition. std.a133_findings has an index over (audityear, dbkey), std.a133_findings_augmented has uniqueness constraint ...
diyer0001's user avatar
0 votes
3 answers
210 views

Why sql server prefer index scan over the seek?

Is how my table looks: CREATE TABLE [dbo].[ClosedTaskCustomFields]( [ClosedTaskId] [uniqueidentifier] NOT NULL, [CustomFieldId] [uniqueidentifier] NOT NULL, [Value] [nvarchar](450) ...
IngBond's user avatar
  • 103
0 votes
1 answer
69 views

Outliers produce a bad query plan

I have table document with ~2.5 millions rows and more than 100 columns. I provide only used in query columns: CREATE TABLE document ( id serial PRIMARY KEY, organizationid INTEGER, status_1 ...
Bunny Boss's user avatar
1 vote
2 answers
66 views

Is there a "correct" way for an optimizer to process a subquery if the outer query has additional where filters?

This topic came up for me as the result of investigating an error in Redshift. We have a table containing a column of numbers represented as text, but for rows where no value is present a single ...
MattB's user avatar
  • 111

1
2 3 4 5
25