Stack Overflow
Stack Overflow

Questions tagged [sql-execution-plan]

A execution, or query plan, is the breakdown of steps the database engine uses to get a result.

A execution, or query plan, is the breakdown of steps the database engine uses to get a result. Specifically it is the plan that the optimizer determines is the best, most efficient, way to get the data.

The execution plan can be analyzed to determine what is happening and how to improve efficiency. Including finding if an index is being scanned or missing an index altogether. There is also many points of analytic data that can be acquired via the plan including things such as estimated and actual number of rows.

1429 questions
4
votes
6 answers

Different Execution Plan for the same Stored Procedure

We have a query that is taking around 5 sec on our production system, but on our mirror system (as identical as possible to production) and dev systems it takes under 1 second. We have checked out the query plans and we can see that they differ.…
Robert Wagner
  • 17,515
  • 9
  • 56
  • 72
4
votes
1 answer

get existing SQL plan for Postgresql

I would like to view my old existing SQL plan at my PostgreSQL I am aware Oracle have a view called DBA_HIST_SQL_PLAN tried to google 'existing or old SQL plan PostgreSQL but could not find something related. I don't want to use PREPARE STATMENT -…
2Big2BeSmall
  • 1,348
  • 3
  • 20
  • 40
4
votes
4 answers

Two radically different queries against 4 mil records execute in the same time - one uses brute force

I'm using SQL Server 2008. I have a table with over 3 million records, which is related to another table with a million records. I have spent a few days experimenting with different ways of querying these tables. I have it down to two radically…
IamIC
  • 17,747
  • 20
  • 91
  • 154
4
votes
2 answers

How does postgres decide whether to use index scan or seq scan?

explain analyze shows that postgres will use index scanning for my query that fetches rows and performs filtering by date (i.e., 2017-04-14 05:27:51.039): explain analyze select * from tbl t where updated > '2017-04-14 05:27:51.039'; …
lolski
  • 16,231
  • 7
  • 34
  • 49
4
votes
1 answer

Recursive CTE causes slowness and index scan

I have a table (Location_Tree) containing location information, arranged in a Id/ParentId structure on several levels, from level 0 of "World" right down to individual cities, via worldregions, countries, states, counties, cantons and cities. We…
High Plains Grifter
  • 1,357
  • 1
  • 12
  • 36
4
votes
1 answer

oracle filter on explain plan partitions

I'm making a proof of concept and I'm experimenting a strange behaviour. I have a table partitioned by range by a date field and the cost of a query changes a lot if I set a fixed date or one created by SYSDATE. These are the explain plans: SQL>…
RufusSC2
  • 253
  • 2
  • 9
4
votes
0 answers

SQL Server sp_create_plan_guide and LIKE clause parameter

I am having trouble dealing with LIKE clause when i am creating plan guides. Real query i had trouble with was enormous so i made up tiny one just to describe my issue. Here is summary of my trouble: Query i am trying to create plan for is: SELECT…
Adrah
  • 41
  • 2
4
votes
1 answer

What does filtered mean in the context of MySQL EXPLAIN EXTENDED?

What does filtered mean in the context of MySQL EXPLAIN EXTENDED? MySQL Docs states: filtered (JSON name: filtered) The filtered column indicates an estimated percentage of table rows that will be filtered by the table condition. That is, rows…
Dennis
  • 7,907
  • 11
  • 65
  • 115
4
votes
1 answer

"Estimated Number of Executions" is inflated

I have a situation where a slower query plan is being chosen instead of a faster one due to, I believe, some incorrect estimates. However, I can't figure out where the incorrect estimates are coming from. Shown below is the faster plan which is not…
James Brantly
  • 561
  • 2
  • 9
4
votes
1 answer

Why is Drill join query not fully optimized for Mongo DB?

I am working on proof of concept to optimize the performance of the join queries executed through drill. The underlying storage is a NO-SQL based database - Mongo DB. The time it takes to return the result of the join query is 46 seconds. Upon…
Mridul Chopra
  • 91
  • 1
  • 4
4
votes
2 answers

Shredding XML From Execution Plans

I'll preface this by saying that I hate XML, horrible stuff to work with, but necessary sometimes. My current issue is that I'm trying to take the XML from an execution plan (supplied by a user, so could be any size) and shred this into a table for…
Rich Benner
  • 7,873
  • 9
  • 33
  • 39
4
votes
1 answer

In a PostgreSQL query plan, what is the difference between Materialize and Hash?

Here's an example of a query plan with Materialize: Nested Loop (cost=4.65..49.46 rows=33 width=488) Join Filter: (t1.hundred < t2.hundred) -> ... (outer) -> Materialize (cost=0.29..8.51 rows=10 width=244) -> ... (inner) And…
AndreKR
  • 32,613
  • 18
  • 106
  • 168
4
votes
1 answer

Data mismatch when querying with different indexes

I stumbled upon with a very curious case. We have a SQL Server 2012 database and such a table CREATE TABLE [dbo].[ActiveTransactions] ( [Id] [BIGINT] IDENTITY(1,1) NOT NULL, [Amount] [DECIMAL](12, 4) NOT NULL, [TypeId] [SMALLINT] NOT…
Dimitri
  • 2,798
  • 7
  • 39
  • 59
4
votes
3 answers

How to store query execution plan so that they can be used later

My applications runs queries against a sql server database. In many cases I can see the benefit of an execution plan: for example I click for the first time on a button to SELECT * from Tasks WHERE IdUser = 24 AND DATE < '12/12/2010' and DATE >…
UnDiUdin
  • 14,924
  • 39
  • 151
  • 249
4
votes
0 answers

Redshift: Query cost from query plan to actual query execution time

The query plan in Redshift gives us cost estimation of a query. My question is is there any way to map this cost into time? For example, if the query plan says that the cost is 100, is there anyway to say that the query execution time will be XX…
Luniam
  • 463
  • 7
  • 21
1 2 3
…
95 96