Questions tagged [execution-plan]

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

147 questions with no upvoted or accepted answers
Filter by
Sorted by
Tagged with
5 votes
0 answers
660 views

Hash join hint returns an error when using local variable

The query below is using the StackOverflow2010 sample database: DECLARE @Id INT = 18471 SELECT c.UserId FROM dbo.Comments AS c INNER HASH JOIN dbo.Users AS u ON c.UserId = u.Id AND c.UserId = @Id ...
JieLong's user avatar
  • 167
5 votes
0 answers
476 views

Postgresql row-level security generates different query plan from manually inserted WHERE clause

(Copied from my post to the postgresql mailing list https://www.postgresql.org/message-id/CADBa3wZpuYXwdry2g68NxYmAXOPvx0DLvdAU4kdo2GoPtxKu+Q@mail.gmail.com. Unfortunately I need at least 10 ...
Charles Cooper's user avatar
4 votes
0 answers
67 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
  • 303
4 votes
0 answers
414 views

Why adding select into table is changing execution plan?

I am having following query: DECLARE @VersionsIds TABLE(VersionId INT) INSERT @VersionsIds(VersionId) VALUES (520) SELECT vEps.MasterPolicyId -- INTO #Temp FROM [Events].[vwEventMasterPolicy_Search] ...
Vojislav Depalov's user avatar
4 votes
0 answers
687 views

PostgreSQL predicate not pushed down (through join conditions)

Consider the following data model in a PostgreSQL v13 system; Here, parent table dim contains a small set of reference data, and child table fact contains a much higher volume of records. A typical ...
Michiel T's user avatar
  • 141
4 votes
0 answers
1k views

PostgreSQL: filtering on array and ordering produces wrong plan / bad index choice

This is the story if a nightmare... I have this table: Table "subscriptions" Column | Type | Collation | Nullable | ...
collimarco's user avatar
3 votes
0 answers
261 views

Execution Plan changes between good and bad query plans (Azure SQL Server)

I've done my best to follow Why is my query suddenly slower than it was yesterday? The ShowPlanXML parses fine with SQL Sentry Plan Explorer but not via brentozar.com/pastetheplan (not sure why) so I ...
Vince's user avatar
  • 175
3 votes
2 answers
2k views

Dealing with suboptimal postgres query plans related to nested loops

I'm developing an application that dynamically builds up SQL queries and executes them against postgres 13. Some of the queries are very slow because they use nested-loops but the query planner ...
valenterry's user avatar
3 votes
0 answers
608 views

MariaDB - Query to find unused indexes

I need to find unused index on a MariaDB database and do not have 'userstat' enabled, I found this query which is supposed to find unused indexes, but I am unable to get a full understanding of this ...
DB guy's user avatar
  • 69
3 votes
0 answers
476 views

Time to generate execution plan for select into from a view takes significantly more time than select

I have a view, vw_example, that is quite complicated and has multiple joins across multiple databases. This view has been causing significant delays when used as part of other queries, and we've ...
Simon's user avatar
  • 131
3 votes
0 answers
89 views

Poor query planning in Postgres 10

The query planner in Postgres10 seems to be ignoring the fact that my tables are clustered. I have to explicitly put "hints" in my query to have it perform the correct type of join. Here's my ...
offbynull's user avatar
  • 131
3 votes
0 answers
1k views

Why is Postgres choosing a Hash-Join instead of a Nested Join that is 200x faster?

I run the following query against Postgres 9.5 which returns article information along with the currently valid price: WITH selected_article_rm(id) AS ( VALUES ('80512e52-a406-4a9b-a8df-...
blubb's user avatar
  • 256
3 votes
0 answers
753 views

Searching between two BIGINT

I have a table defined as: CREATE TABLE [dbo].[IPS_INTEGERS]( [id] [int] IDENTITY(1,1) NOT NULL, [network_start_integer] [bigint] NULL, [network_last_integer] [bigint] NULL, [...
Jurgen Harms's user avatar
3 votes
0 answers
111 views

Bad Plan after a couple of days

I have this query that runs fine for a couple of days (from the application) and then slows down to where it can't even finish. If I rebuild the plan, it flies. There is a signifigant amount of data ...
SqlSandwiches's user avatar
3 votes
0 answers
451 views

Postgres - Partitioning old tables, partition query planning, optimisation

I've been trying to optimise our database, but not being a DBA and not having done a whole lot of tuning before I'm struggling a little with "too many options". I've already run pg_tune and bumped a ...
Steve Pike's user avatar
3 votes
1 answer
947 views

Query with conditional JOIN running slow

I have a query the first part of which looks like this - With CTE AS ( select DISTINCT A.col1, A.col2, . . . from tableA A JOIN tableB B ON ( ...
vir's user avatar
  • 41
2 votes
0 answers
61 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
2 votes
1 answer
115 views

random_page_cost and memoize plan relation

Postgres 15.2. I have the following simple query: SELECT mark.last_modified FROM mark INNER JOIN element ON mark.element_id = element.id INNER JOIN model ON element.model_id = model.id WHERE ...
Cowabunga's user avatar
  • 135
2 votes
0 answers
333 views

Postgres: One-to-many relationship with WHERE, ORDER BY and LIMIT not scalable?

I'm currently using Postgres 12. A few years ago, I set up the following model: a lot table with a one-to-many relationship to a line table. My idea was to mutualize data that are common for multiple ...
K.Banks's user avatar
  • 21
2 votes
0 answers
19 views

set options for replicating slow queries in SSMS

I am investigating a slow ad-hoc query(about 45 seconds) from our application. I am not able to replicate the slowness in ssms however to investigate indexing, reads, etc. I've attempted to use the ...
DBA Greg14's user avatar
2 votes
0 answers
120 views

Huge Difference in IO and CPU for same query

I saw that same query got more than two execution plans. Query is included inside procedure which runs once a day every day. All tables in query got from 2000 to 50000 additional records every day. I ...
adam.g's user avatar
  • 345
2 votes
0 answers
570 views

Correlated subquery with common table expressions in SQLite

Yesterday I achieved a 500x performance boost after rewriting an EXISTS with IN. I did some investigation to find out why and finally came to the following example. Basically, we draw 5 lucky numbers ...
nalzok's user avatar
  • 131
2 votes
1 answer
61 views

Advantage of querying data outside a filtered index

Is there any way to take an advantage of having a filtered index to speed DML queries targeting data sets outside that index? My playground: Table with billion rows and 100 columns Unique clustered ...
Bartosz X's user avatar
  • 503
2 votes
1 answer
2k views

MariaDB Inner join is slow

Here is my SQL (with explain): EXPLAIN SELECT * FROM GBI gbi INNER JOIN VF_KRED kred ON gbi.vendor = kred.vendor; Giving below the outcome: Now, the select SQL takes ~10 seconds to produce the ...
Akshay Lokur's user avatar
2 votes
0 answers
84 views

Is it possible to replace count distinct inside Correlated sub-queries?

I'm trying to optimize the query bellow. The execution Plan is showing multiple sorting before aggregations (I think this is due to the count distincts in the correlated sub-queries)... Is it ...
inux's user avatar
  • 21
2 votes
0 answers
717 views

Is there a way to make postgres count planned number of rows when using recurcive CTE?

Postgres has no ideas about expected rows when using recursive CTE. Here is an expamle: create table test_recursive ( id serial primary key, parent_id int null references test_recursive, name text ...
duschatten's user avatar
2 votes
0 answers
136 views

Plan cache mystery

I work on SQL Server 2016 and i had severe performance issues with few queries. So I posted several posts in here and got solution/suggestion etc and greatly improved the execution time and learned a ...
user9516827's user avatar
  • 1,315
2 votes
0 answers
87 views

Problem query - execution plan

I have a problem with a long running query, I'm not a DBA but it's become my problem. I've been doing a lot of reading over the last few weeks but for the life of me can't figure out why this query is ...
SomeoneEls's user avatar
2 votes
0 answers
871 views

Why does this SQL Server execution plan contain a snapshot cursor for a select statement?

Here is the situation. I have a fairly simple query. It returns ~22k rows: select * from tbl_A When I run this query in SSMS (2017 7.6), I get a fairly simple plan: SELECT <= Clustered Index Scan (...
Jacob H's user avatar
  • 710
2 votes
0 answers
600 views

Poor SQL query optimization of Recursive CTE with iTVF, environment specific

I'm developing a recursive CTE which utilizes an inlined TVF. In our dev environment, the recursive CTE executes exactly as expected. In our production environment, the first iteration of the CTE ...
Apneal's user avatar
  • 141
2 votes
0 answers
140 views

What can I do to determine why my query occasionally hangs (currently hung for over 3 days)

I have a query in my data warehouse load that occasionally hangs. Traditionally, it takes only a few minutes to run, however sometimes it can take a few hours. I'm unsure whether this is the query ...
Lock's user avatar
  • 213
2 votes
0 answers
191 views

SQL Query produced by Umbraco app & NHibernate ORM - help!

https://www.brentozar.com/pastetheplan/?id=SJ1BhSun This is a sample of a query that this CMS called "Umbraco 5" runs against its backend DB, which is currently hosted on SQL 2008R2. This query runs ...
NateJ's user avatar
  • 804
2 votes
0 answers
477 views

Execution plan of query inside pl/pgsql function

Briefly, we have a pl/pgsql function which collects some intermediate data in several temporary tables ("on commit drop") and then returns REFCURSOR which joins those temporary tables with some static ...
pfunk's user avatar
  • 21
2 votes
0 answers
350 views

Does Oracle ADO.NET varchar parameter size influence query plan reuse?

I am considering to apply a fix which will generate constant parameter sizes for (n)varchar parameters. The application is based on NHibernate with the old OracleClientDriver (using Microsoft Oracle ...
Erik Hart's user avatar
  • 510
2 votes
0 answers
560 views

Multi-Statement Template Plan Guide

A piece of, um, software seems to be issuing ~20k queries of this form every few seconds: DELETE FROM macVendor WHERE companyId=N' 08-00-'; INSERT INTO macVendor (companyId,organization) VALUES (N' ...
Fowl's user avatar
  • 131
1 vote
0 answers
72 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
0 answers
40 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
0 answers
41 views

Wrong query optimization plan with InnoDB

After switching DB engine from MyIsam to InnoDB on MariaDB 10.3.39 we noticed endless queries and huge disk space usage. The problem are some queries that are wrong optimized with the new engine. ...
LateAtNight's user avatar
1 vote
0 answers
118 views

Compile time of update query is high on sqlserver 2019 vs sqlserver 2016

A dev team announced performance degradation after migrating their database from SQLServer 2016 to SQLServer 2019. So I started tracing the database using SQL profiler. It turns out duration, cpu ...
Peter Mobiel's user avatar
1 vote
0 answers
114 views

How to reduce high cost of sort on execution plan

I have a very large query with three UNION ALL and it takes 30 minutes to execute. Analyzing the execution plan I see that it has a high cost in SORT and Filter. I tried to generate the showplan in ...
Carlos D's user avatar
1 vote
0 answers
59 views

Improve cardinality estimation when two joined tables have a condition on an identical column

I have a table foo, with an association table foo_associations with columns foo_parent and foo_child. foo has an column called department_id. Any association made through foo_associations is ...
Joe's user avatar
  • 269
1 vote
1 answer
177 views

Testing the estimated query plan of a portion of a trigger that uses inserted/deleted

I'm performance tuning a SQL Server trigger that has several statements using the "inserted" and "deleted" tables. How can I break out those individual queries from the trigger for ...
skeletank's user avatar
  • 301
1 vote
0 answers
51 views

Wrong query plan for GIN index

I have the following repeatable case. I have an uuid[] column with a gin index, which stores all unqiue uuids, without doubles and repeats. Test table: CREATE TABLE test ( id serial not null, ...
Sober Proger's user avatar
1 vote
0 answers
107 views

Optimizer Occasionally chooses a bad plan - how to tune query to ensure it is consistent

I have a query in my environment which occasionally gets a "bad" plan and takes a long time to run. We can see from the query store output below that the query normally uses plan 25493651 ...
SE1986's user avatar
  • 1,960
1 vote
1 answer
3k views

MySQL does not use index, does full table scan instead

EDIT: See further developments below. I have an InnoDB table with the following schema: CREATE TABLE `data` ( `id` BIGINT UNSIGNED NOT NULL AUTO_INCREMENT, `app_id` CHAR(27) NULL DEFAULT NULL,...
Duru Can Celasun's user avatar
1 vote
2 answers
125 views

How to make Postgres use the correct index here?

Here's a test setup: CREATE TABLE t ( filename int, cropped bool not null default false, resized bool not null default false, create_date date not null default '1970-01-01' ); INSERT ...
AndreKR's user avatar
  • 577
1 vote
1 answer
46 views

Azure MI - clustered and nonclustered indexes with the same columns - different execution plans

I have a problem to understand why I am getting 2 different execution plans based on whether clustered index is involved or not. It's happening in Azure managed instance, I have no possibility to test ...
Sranda's user avatar
  • 360
1 vote
0 answers
549 views

Query Planner has bad row estimate for second-degree joined link table and uses inefficient nested join

Note: This is a toy database example I came up with and a simplification of a real design, so please disregard from commenting about the way the database is structured. Each Group has precisely one ...
Joe's user avatar
  • 269
1 vote
0 answers
924 views

Which is the better way to select the last entry row from a table in PostgreSQL?

I need to test an attribute from the last entry row from a table to trigger a trigger function on PostgreSQL 9.6. Just below, there is a little piece of my table. It's doesn't have a timestamp column, ...
Francisco Camello's user avatar
1 vote
1 answer
152 views

Postgresql (11.9) LIKE query not using text_ops index

I've seen related questions but I think this should be working and isn't. I have a text_ops function based index on column to do LIKE searches, but it only picks up the index with an equals: CREATE ...
nfdavenport's user avatar