Questions tagged [execution-plan]

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

Filter by
Sorted by
Tagged with
0
votes
1answer
16 views

Why does the query plan start with a not-filtered-for table when the filtered-for table's entries exceed a certain threshold?

We process data for our customers. Each customerrequest and is associated with one or more items (less than two on average) and falls into one of <100 categories, foreign keys to which are also ...
0
votes
1answer
37 views

PostgreSQL parallel scan with “greater” or “less” than

I have two queries. One has "=" other one has ">" in as filter condition. My question is why planner plans only query with equals in condition to run in parallel. If I mess with ...
0
votes
0answers
22 views

PostgreSQL JSONB Execution Plan with high Offset

How does PostgreSQL work i.e. the execution plan on JSONB query with high Offset number and how to improve the performance? Considering the below "Test Query", help me to clarify the ...
0
votes
1answer
30 views

How to capture execution plan from sql profiler for filtered queries only

Using sql profiler, we trace all slow queries (filter on duration/reads) to see where we can optimise. Events used: RPC:Completed SQL:BatchCompleted Filter on Duration. If I add the event ShowPlan ...
0
votes
0answers
23 views

in SSMS, different result sets for a same query in Azure

I have a complex stored procedure that calls different other stored procedures. I've never seen this behaviour in over 20 years of dba work (since Sql 7). When I call the stored procedure with about ...
1
vote
1answer
67 views

PostgreSQL: query slow, planner estimates 0.01-0.1 of actual results

I will preface this by saying I'm not well-versed in SQL. I mainly work with ORMs, and this recent headache has brought me to dive into the world of queries, planners, etc.. A very common query is ...
0
votes
2answers
84 views

Why Whole Table Scan (Clustered Index)

I have a big table with 315M rows. with these columns & Index : [key_] (int & IDENTITY(1,1)), [time_] (datetime), [value_] (float), [quality_] (int) PK & Clustered-Index: key_ , Non-...
0
votes
1answer
44 views

What is the best way to add optional update in procedure in transaction?

Currently I have a procedure that performs 4 operations in the Transaction: Update record in table A Update record in table B Insert record into table C Delete record from table D The first ...
0
votes
1answer
17 views

Query dramatically slower with less rows from WHERE to evaluate

What is the query SELECT SQL_NO_CACHE id, laptime, driver_id, driverName FROM ( SELECT race_result_details.id id, driver_id, driver.name as driverName, laptime FROM race_result_details LEFT ...
3
votes
1answer
41 views

How does Postgres compute the row count estimate during query planning

I have a simple query such as: SELECT * FROM example WHERE filter_1 = ? AND filter_2 = ? LIMIT 10 The table is quite large (about 100 millions row) and it has an index ...
2
votes
1answer
53 views

MariaDB wants to use bizarre and extremely inefficient query plan on Aria, but not on InnoDB (5 seconds vs 1+ hour query)

I'm having some troubles getting a query to preform as expected on my MariaDB 10.4.14 system. We recently migrated the database from InnoDB to Aria so we could store the INDEX separately on a fast PCI-...
3
votes
1answer
193 views

Select with CROSS APPLY runs slow

I am trying to optimize the query to run faster. The query is the following: SELECT grp_fk_obj_id, grp_name FROM tbl_groups as g1 CROSS APPLY (SELECT TOP 1 grp_id as gid ...
0
votes
1answer
23 views

Readable join for execution plan in Oracle

I want to understand what join operations were done on my database by different users for future analysis and improvements. but I have issues with understanding the execution plan. Example: Consider ...
0
votes
1answer
24 views

Statistics Update Time is missing in Actual execution plan

I remember that I have seen somewhere in the past that when I saw the XML of the actual execution plan, it had the details about when the stats were updated on the indexes on a table and what was the ...
0
votes
1answer
38 views

Actual Execution plan does not have memory grant info

I manually ran query in SSMS and captured actual execution plan of the query by enabling its icon in SSMS. I am not getting "Memory Grant" when I keep my mouse on SELECT OPERATOR. I didn't ...
0
votes
1answer
112 views

Query tuning update with hash flow distinct operator

Need some help in understanding the slowness in one of the UPDATE statements like below:- UPDATE TOP (100) xyz SET xyz.flag = 1 OUTPUT inserted.Rcode, inserted.EDR, inserted.id, abc.EID,abc.CID,abc....
1
vote
0answers
22 views

Why does adding an index increase the execution time in SQLite?

I'll just show you an example. Here candidates is a table of 1000000 candidates from 1000 teams and their individual scores. We want a list of all teams and whether the total score of all candidates ...
1
vote
0answers
42 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 ...
0
votes
1answer
65 views

Can OFFSET 0 ROWS FETCH NEXT 25 ROWS ONLY cause to create totally different execution plan and also cause query to run slow?

We have a SELECT query which is taking time (around 90 secs) to execute. It has OFFSET 0 ROWS FETCH NEXT 25 ROWS ONLY in it. When we remove OFFSET 0 ROWS FETCH NEXT 25 ROWS ONLY, it completes in 4-5 ...
0
votes
0answers
32 views

How can I capture wait stats for a previous Actual Plan in SQL Server?

I have some SQL Server stored procedures that sometimes take too much time to run. If I run them manually, I'm able to catch the Wait Stats. However, I would like to automatically capture all plans ...
1
vote
0answers
30 views

Unexpected result by MySQL Optimizer for identical queries?

Following query is working like expected and uses index Query takes 0,0481 sec SELECT geodb_locations.name, geodb_locations.name_url, COUNT(user.uid) AS useranzahl FROM user LEFT ...
-1
votes
1answer
33 views

How are database views executed?

Lets assume there is a design where every table is represented by a view, that is simply selecting all of the columns of the table and all the rows. --vw Select * From sys_table And I then run a ...
0
votes
1answer
39 views

Postgres not using index in citext column

The following query is returning a SEQ Scan instead a Index. select file_name from myschemadb.my_files where file_name = 'djsaidjasdjoasdjoaidad' Engine Postgres 11.5 My table: CREATE TABLE ...
0
votes
1answer
49 views

Postgres sometimes uses sequential scan instead of an index only scan for data that doesn't exist

We are using Postgres 12 and We have a simple table like this: > \d live_event_segmentation Table "public.live_event_segmentation" Column | Type ...
1
vote
2answers
98 views

Why is index not being used? Can I replicate plan?

I have been tuning a query/ indexes in a dev environment, but cannot replicate the new query plan when applying the same changes to a UAT environment. Specifically, in the UAT environment the ...
4
votes
0answers
133 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 ...
0
votes
2answers
55 views

Why 2 Table Scans of Same Table Have Huge Differences in Elapsed Time SQL Server

This is driving me bananas. According to both the Execution Plan AND Statistics IO, both queries are doing a full table scan and nothing more, but in one case the query takes LESS THAN ONE SECOND and ...
0
votes
0answers
41 views

Clearing plan cache improves performance - bad plans in cache?

I have run into this situation a number of times on a VM with SQL Server Express 12.0.6205.1 (SQL Server 2014 SP3 CU1) Using SSMS, I'll run a stored procedure a few times. It consistently takes, say ...
1
vote
1answer
38 views

PostgreSQL incorrectly using primary key index for min/max query

I have a table with the structure similar to this: CREATE TABLE employees ( id bigserial NOT NULL, name_id uuid NOT NULL, department uuid NOT NULL, details text NULL, deleted bool ...
1
vote
1answer
26 views

Wrong estimated row count

PostgreSQL 11 Running EXPLAIN returns a row estimate that is way too low compared to the actual row count. Given the query SELECT s.offer_id, category_id, category_name_de, category_name_fr, ...
0
votes
1answer
33 views

SQL extended event captured only the estimated execution plan but no actual execution plan

My SQL instance version is Microsoft SQL Server 2014 (SP3-CU-GDR) (KB4535288) I am troubleshooting the application time-out and the time-out SELECT query is quite simple. It only takes less than 1 ...
0
votes
1answer
25 views

Bad estimates when sorting, good estimates without sort

I'm struggling with the tuning of one of the queries on our production SQL Server 2016 SP2. I needed to anonymize the query due to security policies. The query is like this: SELECT TOP 1000 Object1....
2
votes
0answers
54 views

Does SQL Server really require fully qualified object references to match a statement to a cached execution plan [duplicate]

From SQL Server Docs, section Execution Plan Caching and Reuse: The algorithms to match new SQL statements to existing, unused execution plans in the cache require that all object references be fully ...
2
votes
1answer
81 views

How to optimize Merge Anti Join?

While trying to find the fastest way to do a bulk update of a large table, I came up with the following plan: having main_table (potentially above 1000 mln rows) and update table up to a few million ...
2
votes
1answer
178 views

SQL Server - Prevent Clustered Index Scan on a specific table

My database contains a specific table that is quite large (250+M rows, 100+GB data space). Generating a clustered index scan on this table is always a bad idea. This table has multiple indexes and ...
1
vote
1answer
50 views

Is there anything wrong with splitting a complicated view into many small ones?

I have a view where each row is associated with a customer, and the columns are various computed values such as life_time_value and purchases_per_week, as well as more complicated statistical values ...
0
votes
1answer
25 views

can't find column stats for newly created table

I am trying to simulate a plan recompilation due to auto update stats which is triggered when threshold(500 + 20 percent modifications) reached. database current settings are: I started with ...
0
votes
1answer
46 views

Does plan recompilation happens to plan guide?

Does plan recompilation happens for plan guide due to the, for example, auto update stats? P.S Do not take into account adding a hint OPTION (RECOMPILE) in the plan guide
1
vote
1answer
46 views

How to prevent seek pagination from doing sequential scan over entire table

I have 3 tables and a materialized view: resource_categories contains all category names and metadata create table if not exists resource_categories ( category_id INT, title VARCHAR(255), ...
0
votes
1answer
85 views

Query processor ran out of internal resources and could not produce a query plan” - MariaDB

i have some query SELECT COUNT(loan_payment) as setcolumn FROM table1 WHERE paydate between {d '2020-05-01'} AND {d '2020-05-31'} and period_code = 'MONTHLY' AND company_id =...
0
votes
1answer
24 views

Postgres foreign data wrappers weird join behavior

So I have tables in two different databases that I need to analyze together. I am using fdw to do so. However I am running in to weird behavior with my joins. Keep in mind the following snippets are ...
1
vote
2answers
67 views

SQL Server order of execution and query plan

I am aware that the order of execution of a query in SQL Server is (excluding TOP, etc): FROM, JOIN WHERE GROUP BY HAVING SELECT ORDER BY However, I cannot understand how in the query plan the sort (...
7
votes
1answer
217 views

GROUPING SETS returning unexpected results with calculated expression

Here we have two similar queries using grouping sets where the SELECT clause contains some expressions calculated in aggregation: SELECT RN10, RN10 / 10, COUNT(*) FROM ( SELECT RN, RN/10 AS ...
13
votes
1answer
1k views

SQL Server Spill level 0 - what is it?

I was asked to look into performance of a query in SQL Server 2016 SP2, and I found something that I have not seen before: <Warnings> <SpillToTempDb SpillLevel="0" SpilledThreadCount="1" /&...
-2
votes
1answer
31 views

Clustered index scan in Delete query

I am trying to improve the performance of the delete query below. #ROW_IDS.Column_ID and Table1.ID are big integer values.Table1.ID and #ROW_IDS.Column_ID are clustered indexes. Table1 has 131 million ...
5
votes
2answers
94 views

More than 64 parameters to WHERE IN sentence makes MSSQL reading far too many rows

I have some queries where I need to get more than 64 specific rows, like this example with 65 IDs. TableID is primary key, type BigInt. SELECT * FROM TableA WHERE TableID IN (260905384, ...
1
vote
1answer
24 views

Query performance improvement

Can I improve performance of this query? SELECT transact.id FROM transaction0 transact JOIN journ_notes journ ON transact.journal_jdoid = journ.jdoid JOIN product ...
1
vote
1answer
66 views

Weird planning time data dependency in Postgres

We got a fairly large table holding session data in Postgres. Recently, we found that the planning time for the most common query on this particular table increased over time, at one point to ~130ms ...
0
votes
1answer
46 views

Why scan clustered index instead seek nonclustered index?

There is SQL script: CREATE TABLE users ( id INT, firstname VARCHAR(50), surname VARCHAR(50) ); CREATE CLUSTERED INDEX ix_users_id ON users (id); CREATE ...
2
votes
1answer
83 views

Query planner enerates different plans in psql and application for the same query

I am optimising an application on a development server; the database is being dropped and restored from time to time (always the same database and data) and the caches flushed. I want to identify ...

1
2 3 4 5
18