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.

1250 questions
350
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
  • 80,106
  • 47
  • 208
  • 350
136
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
  • 7,069
  • 6
  • 33
  • 42
89
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
89
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,181
  • 8
  • 28
  • 35
71
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
  • 9,828
  • 18
  • 66
  • 88
59
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
  • 30,403
  • 10
  • 97
  • 160
59
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
  • 759
  • 2
  • 6
  • 10
55
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
  • 762,786
  • 156
  • 1,160
  • 1,345
52
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
  • 70,516
  • 16
  • 91
  • 126
50
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
  • 2,942
  • 17
  • 49
  • 91
48
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
  • 206,738
  • 150
  • 445
  • 651
46
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
  • 16,529
  • 10
  • 50
  • 64
36
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
  • 968
  • 1
  • 10
  • 28
36
votes
9 answers

How to improve performance on a clustered index seek

I'm trying to improve the performance on a query that is running very slowly. After going through the Actual Execution Plan; I found that a Clustered Index Seek was taking up 82%. Is there any way for me to improve the performance on an Index…
Abe Miessler
  • 75,910
  • 89
  • 276
  • 451
34
votes
2 answers

SQL Server: Table-valued Functions vs. Stored Procedures

I have been doing a lot of reading up on execution plans and the problems of dynamic parameters in stored procedures. I know the suggested solutions for this. My question, though, is everything I have read indicated that SQL Server caches the…
IamIC
  • 16,207
  • 18
  • 81
  • 142
1
2 3
…
83 84