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
375
votes
13 answers

How do I obtain a Query Execution Plan in SQL Server?

In Microsoft SQL Server how can I get a query execution plan for a query / stored procedure?
Justin
  • 84,773
  • 49
  • 224
  • 367
180
votes
1 answer

What is a "Bitmap heap scan" in a query plan?

I want to know the principle of "Bitmap heap scan", I know this often happens when I execute a query with OR in the condition. Who can explain the principle behind a "Bitmap heap scan"?
francs
  • 8,511
  • 7
  • 39
  • 43
92
votes
11 answers

How do you interpret a query's explain plan?

When attempting to understand how a SQL statement is executing, it is sometimes recommended to look at the explain plan. What is the process one should go through in interpreting (making sense) of an explain plan? What should stand out as, "Oh, this…
user290
92
votes
6 answers

SQL Server Plans : difference between Index Scan / Index Seek

In a SQL Server Execution plan what is the difference between an Index Scan and an Index Seek I'm on SQL Server 2005.
cindi
  • 4,571
  • 8
  • 31
  • 38
74
votes
5 answers

Understanding the results of Execute Explain Plan in Oracle SQL Developer

I'm trying to optimize a query but don't quite understand some of the information returned from Explain Plan. Can anyone tell me the significance of the OPTIONS and COST columns? In the OPTIONS column, I only see the word FULL. In the COST column, I…
Kevin Babcock
  • 10,187
  • 19
  • 69
  • 89
62
votes
1 answer

What is the difference between Seq Scan and Bitmap heap scan in postgres?

In output of explain command I found two terms 'Seq Scan' and 'Bitmap heap Scan'. Can somebody tell me what is the difference between these two types of scan? (I am using PostgreSql)
Sunil
  • 789
  • 2
  • 6
  • 10
61
votes
14 answers

Faster way to delete matching rows?

I'm a relative novice when it comes to databases. We are using MySQL and I'm currently trying to speed up a SQL statement that seems to take a while to run. I looked around on SO for a similar question but didn't find one. The goal is to remove…
itsmatt
  • 31,265
  • 10
  • 100
  • 164
61
votes
3 answers

How do I view the Explain Plan in Oracle Sql developer?

I have few SQL queries which has very low query running performance and I want to check the query execution plan for this query. I am trying to execute the below query but its not showing any query execution plan. Its only display message plan FOR…
Andrew
  • 3,632
  • 24
  • 64
  • 113
58
votes
5 answers

SQL explain plan: what is Materialize?

I asked PostgreSQL to explain my query. Part of the explanation was: table_name --> Materialize What does materialize do? I'm joining two tables, not views or anything like that.
Claudiu
  • 224,032
  • 165
  • 485
  • 680
56
votes
3 answers

How do I use EXPLAIN to *predict* performance of a MySQL query?

I'm helping maintain a program that's essentially a friendly read-only front-end for a big and complicated MySQL database -- the program builds ad-hoc SELECT queries from users' input, sends the queries to the DB, gets the results, post-processes…
Alex Martelli
  • 854,459
  • 170
  • 1,222
  • 1,395
54
votes
6 answers

Measuring Query Performance : "Execution Plan Query Cost" vs "Time Taken"

I'm trying to determine the relative performance of two different queries and have two ways of measuring this available to me: 1. Run both and time each query 2. Run both and get "Query Cost" from the actual execution plan Here is the code I run to…
MatBailie
  • 83,401
  • 18
  • 103
  • 137
53
votes
1 answer

Nested Join vs Merge Join vs Hash Join in PostgreSQL

I know how the Nested Join Merge Join Hash Join works and its functionality. I wanted to know in which situation these joins are used in Postgres
vinieth
  • 1,204
  • 3
  • 16
  • 34
51
votes
7 answers

Meaning of "Select tables optimized away" in MySQL Explain plan

What is the meaning of Select tables optimized away in MySQL Explain plan? explain select count(comment_count) from wp_posts; +----+-------------+---------------------------+-----------------------------+ | id | select_type |…
Chandra Patni
  • 17,347
  • 10
  • 55
  • 65
44
votes
5 answers

What "Clustered Index Scan (Clustered)" means on SQL Server execution plan?

I have a query that fails to execute with "Could not allocate a new page for database 'TEMPDB' because of insufficient disk space in filegroup 'DEFAULT'". On the way of trouble shooting I am examining the execution plan. There are two costly steps…
Bin
  • 3,645
  • 10
  • 33
  • 57
43
votes
3 answers

Easy way to run "explain" on query sets in django

It seems like it should be easy to run "explain" directly off of a queryset in Django, but I don't see anything obvious for how to do it, and "explain" is a difficult thing to search for in the docs.
guidoism
  • 7,820
  • 8
  • 41
  • 59
1
2 3
…
95 96