Questions tagged [execution-plan]

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

Filter by
Sorted by
Tagged with
-2 votes
0 answers
26 views

When is a multi-column index generally used by a RDBMS?

Say we got a table with columns: A, B, C, D, E I am confused as to what specifically the rules are as to when an index is used by the RDBMS in the general sense. What I think the answer is: For some ...
Sp Jan's user avatar
  • 1
1 vote
0 answers
41 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
29 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
76 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
84 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
78 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
  • 803
0 votes
2 answers
39 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
87 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
86 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
83 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
  • 803
0 votes
1 answer
38 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
69 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
  • 803
0 votes
1 answer
31 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
440 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
37 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
91 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
556 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
48 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
120 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
189 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
  • 135
1 vote
2 answers
183 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,271
2 votes
1 answer
128 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
4 votes
0 answers
79 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
6 votes
1 answer
418 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
53 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
133 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
63 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
162 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
62 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
61 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
4 votes
4 answers
2k views

What's the order of operations in PostgreSQL?

I'm a DB student, and I executed the following query to learn a couple things at the same time (LEFT/RIGHT JOIN, UNION, WHERE + RegEx). What's troubling me is the order of execution. I have two tables,...
elkronequis's user avatar
0 votes
1 answer
102 views

Is WHERE [FOO] IN (SELECT [BAR] FROM [...]) guaranteed to give the same execution plan as the equivalent WHERE EXISTS?

In the past, there was a lot of paranoia around IN. In particular, I remember claims that it can be optimised differently to an equivalent WHERE EXISTS. Is this claim still true? Or is it now ...
J. Mini's user avatar
  • 135
-2 votes
1 answer
48 views

Why force plan is not working with failure "NO_PLAN"?

We have query which is inserting data to heap: INSERT INTO [heap] ( 80 COLUMNS... ) SELECT 14 columns 65 NULL VALUES , '...
adam.g's user avatar
  • 355
0 votes
1 answer
295 views

query bad performance

Is there anything I can do to improve this query performance? I have included the execution plan and the Server Profiler output. Is there a index that can help? I see there is a Index Seek that is ...
Jefferson's user avatar
0 votes
0 answers
29 views

Slow execution even with proper indexes defined

I am using AWS Aurora Serverless V2 with MySql. I have a table with 10M rows as shown below. CREATE TABLE 'VCont' ( 'id' varchar(64) NOT NULL, 'version' int NOT NULL, 'colId' bigint unsigned ...
Ravindra's user avatar
0 votes
1 answer
209 views

DISTINCT with LIMIT performance

It is important to note that combining DISTINCT with LIMIT can have a negative impact on performance. This is because the database engine must first find all of the non-duplicate rows in the result ...
Linh OR's user avatar
0 votes
2 answers
40 views

MySQL is not utilizing the index when the IN operator is used

I have a mysql 8 query like below. the ch_site_user table is indexed on user_id, site_id, status. Explain plan shows the correct index is used. But usersite.site_id IN (2315, 2316, 2324, 2323, 2320, ...
Sreenivas Kolanthayappan's user avatar
1 vote
2 answers
119 views

Why can SQL Server not perform a TOP N SORT between an Index Seek and the Key Lookup?

I am looking into a minor performance issue, where an optimizer tool is basically saying, "hey just go ahead and include all the columns on this table in this index" which is a horrible ...
Morgeth888's user avatar
0 votes
2 answers
158 views

In SQL Server, if I have two otherwise identical stored procedures with different names, will they use the same execution plan?

Azure SQL Managed Instance. Here is the scenario. I have a stored procedure that produces a count of rows that match some criteria. But, depending on the Customer that is chosen, the statistics can be ...
CB_Ron's user avatar
  • 291
0 votes
2 answers
189 views

Performance tuning of SQL Server query, with LEFT OUTER JOIN vs INNER JOIN

In my scenario i need to optimize a stored procedure, that i used to import data into DB from an exchange DB. I am relly puzzled because, the solution with INNER JOIN is lower than the LEFT OUTER JOIN ...
Skary's user avatar
  • 368
3 votes
3 answers
403 views

Getting a SORT operator when I have an index

On a Azure SQL db (SQL2019 compat), I have an ETL process that populates HISTORY tables in a DeltaTrack pattern. In the Proc, there's an UPDATE to the HISTORY table which the query engine is using a ...
Geezer's user avatar
  • 431
1 vote
0 answers
43 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
0 votes
0 answers
36 views

Faster running for this query sql server

i used database engine tunning advisor for creating some index and statistics but still run slow SELECT C.code_commande AS 'Order', C.code_client AS 'Cust&#46', ...
octavian darie's user avatar
3 votes
1 answer
408 views

PostgreSQL Query Planner overestimating Row Count leads to slow Hash Join

I have a table with two columns and about 10M rows. About half the records are NULL in the first field, and about half the records are NULL in the second field, although there are a few hundred ...
LennartF22's user avatar
-4 votes
2 answers
288 views

Calculate total_worker_time in minutes [closed]

I have an easy query which can show me the top 10 elapsed time by query_plan_hash and query_hash: PRINT '-- top 10 elapsed time by query_plan_hash and query_hash --' SELECT TOP 10 query_plan_hash ...
Francesco Mantovani's user avatar
5 votes
1 answer
183 views

Partitioned view aggregation query not optimized

Given the following two tables: CREATE TABLE SalesLedger ( Id int PRIMARY KEY IDENTITY, Date date NOT NULL, Total decimal(38,18), INDEX IX (Date, Total) ); CREATE TABLE Purchases ( Id int ...
Charlieface's user avatar
0 votes
0 answers
65 views

How can I improve the performance of a query that relies on data from lots of tables?

I am working on a Web application that renders a food menu in one view. The menu is modelled as one big tree, which contains nodes like services (breakfast, lunch, dinner), courses (starter, main, ...
Check12's user avatar
  • 63
2 votes
2 answers
774 views

Does PERCENTILE_CONT still give terrible performance when calculating medians in 2022 versions of SQL Server?

I'm currently reading a book from SQL Server 2014. It, like every other online source that I've found, tells you that PERCENTILE_CONT is a very slow way of calculating medians and does not show the ...
J. Mini's user avatar
  • 135
0 votes
1 answer
54 views

Why is the PostgreSQL Query Planner choosing such an inefficient solution?

I'm working with postgres 13.9 on Amazon Aurora. In our production environment, we're running a query that is taking >15 seconds to run when the query is using a small LIMIT. For example when the ...
kylejw2's user avatar
  • 103
1 vote
1 answer
216 views

Is it a problem if BIGINT data type parameter gets passed into a table for join with INT data type?

We have an application that has been developed in Hibernate and the SQL’s are all inline from the app. There are no stored procedures. The problem I see is the way parameters are being passed in the ...
Texan DBA's user avatar

1
2 3 4 5
24