Questions tagged [execution-plan]

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

Filter by
Sorted by
Tagged with
0
votes
0answers
22 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 ...
1
vote
1answer
98 views

How can I investigate why Sql Server is chosing the “wrong” Index?

I have a Transaction table with about 200 million records, one primary key clustered on Id and 2 indexes: IX_SiloId_ChangedTime_IncludeTime IX_SiloId_Time_IncludeContent I run these 2 statements ...
1
vote
2answers
65 views

Performance issues since migrating from SQL Server 2012R2 to 2019

I've migrated my database from a SQL Server 2012R2 to a new SQL Server 2019. I've just backupped the database and restored it on new sql server. After that I've imported and updated some additional ...
1
vote
1answer
27 views

Getting the “flat” query corresponding to a view using nested views?

I'm programming an application that queries SAP Business One databases, which are incredibly complex, riddled with cryptic table and column names left over from ancient times (including many ...
1
vote
1answer
28 views

How to convince Oracle to create optimal execution plans in SQL queries with bind variables?

An attempt to parameterize a query in Oracle using bind variables and OR :B1 IS NULL or NVL(:B1,col1) in a WHERE clause leads to suboptimal execution plans. For example, I'd like to write SELECT like ...
9
votes
1answer
138 views

Why using a local temp table (instead of a global temp table or a regular table) influences the Query Optimizer to choose a poor query plan?

This Question brings a situation where the Query Optimizer chooses poorly the seek predicate among the existing predicates of a simple query. After running some tests I got to the conclusion that the ...
3
votes
2answers
161 views

Remote Scan when updating using functions

Why does using ISNULL() when updating through a linked server use Remote Scan to retrieve all rows and filter locally instead of filtering through Remote Query? UPDATE LINKEDSERVER1.database1.dbo....
2
votes
1answer
102 views

Suboptimal plan when joining on an ID column with incorrect statistics

We are having an issue with a joining query between 2 tables (showing only relevant columns from the query): CREATE TABLE IF NOT EXISTS payments ( id TEXT PRIMARY KEY, consumer_id TEXT, ...
0
votes
2answers
40 views

MariaDB 10.4 Query Optimizer Wrong Execution Plan

I have a very strange behavior with some tables and the query optimizer on a MariaDB server. First of all we have our main table let's call it huge and we have on the same database another table a ...
5
votes
2answers
176 views

SQL Server Indexed View and TOP

I'm struggling to persuade a query plan to behave as I think it should. The addition of a TOP clause when querying an indexed view is causing a sub-optimal plan, and I'm hoping for some help in ...
2
votes
2answers
139 views

WHERE Clause Compare VarChar(n) Column = VarChar(MAX) Variable Results in Inequality Comparison and Poor Query Performance

Earlier today I was working on a Reporting Stored Procedure which had poor performance. I was able to fix the issue by changing a static variable which held a string of ten characters or less from ...
0
votes
3answers
79 views

Why are those estimates way off?

I have problems understanding the following query plan: https://www.brentozar.com/pastetheplan/?id=SyoD_PosP First there is the bottom right index seek. The estimation says over 22 million rows but ...
0
votes
2answers
74 views

SQL Server - Can I surgically remove a bad cached query plan or am I chasing the wrong idea?

Let me set the stage: SQL Server > 2016 Several databases with the same schemas Data is similar but not identical The same indexes exist etc., all systems but one work as expected Examining the ...
1
vote
2answers
80 views

SQL Server estimating badly

I have a heap with over a million inserts daily. This table is a "staging table" where messages are received and sent to differens queues that are processed by the application. A stored proc ...
0
votes
1answer
19 views

Can Postgresql exploit “accidental” clustering in plans?

Suppose I have an append-only table with columns customer_id (randomly generated string) and x, and lookups are always done on customer_id. Say the data look like below, as if we get a batch of rows ...
0
votes
1answer
53 views

How to get a parallel nested loop in postgres 12?

I have a lateral join, and I would like each subselect to execute in parallel. According to the docs (https://www.postgresql.org/docs/12/parallel-plans.html), nested loops can be parallelized: Just ...
1
vote
2answers
73 views

Optimizing postgres sorting by word_similarity

I have a table with 2M rows of hotel names in Postgresql 12. I am building a typeahead so users can quickly find the hotel by name. I have played around with different Postgres options including FTS, ...
1
vote
1answer
31 views

Do databases optimize repeated similar query params on the same column?

I have the following query: SELECT * FROM table t WHERE t.id IN :ids AND t.id IN :allowedIds (where the query params are later replaced) Are these query params optimized by the database itself? I ...
9
votes
2answers
454 views

Building an execution plan takes too long on SQL Server

I have an SQL Server 2014 instance (12.0.2000.8) and a quite complex SELECT statement with about 20 joins. This query works fine with the same data set on PostgreSQL, Oracle and other databases, and ...
0
votes
0answers
9 views

Understanding rows_examined_per_scan and rows_produced_per_join

I'm not mysql guy, but I've got opportunity to work with that recently - I've got to optimize some query on Mysql5.7 (I have to cover that also on 5.6, but started with 5.7 as it has apparently more ...
0
votes
0answers
23 views

alternative IN condition on SQL

i have query like this select name, time_attandance from "VIEW_GO_EMP" "viewGoEmp" left join "VIEW_ATTENDANCE" "viewAttendance" on "viewAttendance&...
0
votes
1answer
20 views

Oracle autotrace - pstart and pstop always the same

I have been executing autotrace against a partitioned table. The table is partitioned by month range. For the data I have so far there are 4 partitions - however, when I execute autotrace against ...
0
votes
1answer
18 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
41 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
24 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
37 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
25 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
71 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
88 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
46 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
87 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
56 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
323 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
25 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
25 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
42 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
116 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
25 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
64 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
103 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
43 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
50 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
60 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
100 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
141 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
62 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
48 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
2 3 4 5
19