Questions tagged [execution-plan]

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

Filter by
Sorted by
Tagged with
1 vote
1 answer
65 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 ...
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 ...
0 votes
2 answers
57 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 ...
0 votes
2 answers
27 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 ...
0 votes
1 answer
2k views

Forcing a sequential scan over the index scan

I have this query select s_acctbal,s_name,n_name,p_partkey,p_mfgr,s_address,s_phone,s_comment from part,supplier,partsupp,nation,region where p_partkey=ps_partkey and s_suppkey=...
1 vote
1 answer
72 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 ...
0 votes
2 answers
66 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: "...
1 vote
0 answers
69 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 ...
0 votes
1 answer
935 views

Subquery not using Index, even if forcing

Using MariaDB 10.5.12 on CentOS 7 If it makes any difference, my "optimizer_use_condition_selectivity" = 1 If I run: EXPLAIN SELECT COUNT(*) FROM ips WHERE ip="1.2.3.4" This query ...
0 votes
1 answer
24 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 ...
0 votes
2 answers
66 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 ...
0 votes
1 answer
29 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 ...
14 votes
1 answer
10k views

Why does this LEFT JOIN perform so much worse than LEFT JOIN LATERAL?

I have the following tables (taken from the Sakila database): film: film_id is pkey actor: actor_id is pkey film_actor: film_id and actor_id are fkeys to film/actor I am selecting a ...
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,...
3 votes
1 answer
435 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 ...
0 votes
1 answer
296 views

How to enhance performance when compare two table feature value to run faster?

I work on sql server 2019 i face issue when compare feature values between two tables Based on RecomendationId . it take too long time to display result time that take it 5 minute . total rows on ...
0 votes
1 answer
4k views

Costly HASH MATCH Aggregate

I have a view,which is slow when i query a simple select statement with a where clause.The where clause column is indexed(Non-clustered index) Here is the view and the plan. execution plan CREATE ...
0 votes
0 answers
28 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 ...
0 votes
1 answer
58 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" ...
1 vote
2 answers
177 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....
3 votes
2 answers
551 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,...
0 votes
1 answer
577 views

What exactly happens when optimizer_prune_level = 1?

This documentation link states that optimizer_prune_level Controls the heuristics applied during query optimization to prune less-promising partial plans from the optimizer search space. Can ...
1 vote
0 answers
39 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 ...
0 votes
1 answer
773 views

Performance issues after upgrading to Mysql 8.0.19 version

Few queries are performing very poorly after the upgrade. Here are the examples. ****Example 1**:** SELECT x.CI_TITLE 'ciTitle', x.DEPARTMENT_MEANING 'departmentMeaning', x....
1 vote
1 answer
98 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 ...
0 votes
2 answers
182 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 ...
1 vote
1 answer
547 views

How to get an actual execution plan from the linked server?

There is a "main" SQL Server to which I have full access and I can connect to it via SSMS. Its version is: Microsoft SQL Server 2016 (SP1-CU15-GDR) (KB4505221) - 13.0.4604.0 (X64) Jun ...
0 votes
1 answer
998 views

Query using VIEW very slow when the same query is fast using WITH

Given this view CREATE OR REPLACE VIEW public."SellMeta" AS WITH wh AS ( SELECT "WWHH".id, "WWHH".url, "WWHH".name, ...
2 votes
1 answer
96 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 ...
6 votes
1 answer
406 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 ...
4 votes
0 answers
66 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 ...
0 votes
1 answer
52 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 ...
1 vote
1 answer
127 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....
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 ...
1 vote
1 answer
454 views

MariaDB uses "wrong"/irrelevant Keys on large table

We have migrated our database from a local hosting company to AWS. Now when we run a particular query on AWS it finishes in about a minute (processlist says "sending data") where on the old server the ...
0 votes
1 answer
289 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 ...
0 votes
3 answers
145 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) ...
1 vote
2 answers
56 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 ...
0 votes
1 answer
48 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 ...
0 votes
1 answer
1k views

Query with left join not using index

The below query is taking about 1 second to run SELECT `taxi_driver_mapping`.*, concat("dpk00", taxi_driver_mapping.mapping_payment_plan_id) AS package_text, `people`.*, ...
21 votes
1 answer
6k views

Hash keys probe and residual

Say, we have a query like this: select a.*,b.* from a join b on a.col1=b.col1 and len(a.col1)=10 Assuming the above query uses a Hash Join and has a residual, the probe key will be col1 and the ...
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,...
0 votes
1 answer
96 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 ...
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 ...
-2 votes
1 answer
45 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 , '...
0 votes
1 answer
118 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 ...
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 ...
8 votes
1 answer
1k views

Inserting with implicit type conversion causes warning for cardinality estimates

I noticed this while doing some performance testing recently. When I insert a value into a column that will require an implicit conversion (e.g. bigint into nvarchar), I get a warning: Type ...
0 votes
2 answers
39 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, ...
0 votes
1 answer
551 views

How can I prompt SQL server to use more suitable index in query?

I have a query that somtimes runs longer than the application calling it allows. I've traced it down a clustered index seek on rather larger table. I've created a new index, that better supports this ...

1
2 3 4 5
24