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
7
votes
1 answer

postgres not using index on SELECT COUNT(*) for a large table

I have four tables; two for current data, two for archive data. One of the archive tables has tens of millions of rows. All tables have a couple narrow indexes and are very similar. Given the following queries: SELECT (SELECT COUNT(*) FROM…
Alan Samet
  • 1,118
  • 2
  • 15
  • 18
7
votes
1 answer

** Restricted Text ** when Reviewing Execution Plan in SQL Server Management Studio

I'm reviewing an execution plan to see why a stored procedure is running slowly. However in the execution plan window instead of the useful Missing Indexes text I get ** Restricted Text ** instead. This has perked my curiosity as I've not seen it…
Luke Baughan
  • 4,658
  • 3
  • 31
  • 54
7
votes
2 answers

Searching for table/index scans

Does anyone have a query which searches through SQL2005/2008's plan cache identifying queries or stored procedures which have table/index scans within their execution plans?
SuperCoolMoss
  • 247
  • 1
  • 4
  • 8
7
votes
1 answer

sqlite3 select min, max together is much slower than select them separately

sqlite> explain query plan select max(utc_time) from RequestLog; 0|0|0|SEARCH TABLE RequestLog USING COVERING INDEX key (~1 rows) # very fast sqlite> explain query plan select min(utc_time) from RequestLog; 0|0|0|SEARCH TABLE RequestLog USING…
lucemia
  • 6,349
  • 5
  • 42
  • 75
7
votes
1 answer

Why is row count 0 in my PostgreSQL plan?

I have a query which is equi-joining two tables, TableA and TableB using a nested loop. Because of the "equi"-join contraint, all rows returned in the result will therefore correspond to at least one row from each of these two tables. However,…
someName
  • 1,275
  • 2
  • 14
  • 33
6
votes
2 answers

Different parameter sizes result in inefficient query plan cache

Nhibernate profiler shows lots of error messages about the query plan: Different parameter sizes result in inefficient query plan cache usage It also leads you to an explanation in http://nhprof.com/Learn/Alerts/UncachedQueryPlan and warns you about…
guillem
  • 2,768
  • 2
  • 30
  • 44
6
votes
1 answer

When does the Oracle CBO choose to execute a "merge join cartesian" operation?

From time to time, Oracle seems to prefer a MERGE JOIN CARTESIAN operation over a regular MERGE JOIN. Knowing the data and looking at concrete execution plans, I can see that this operation is usually not a problem, as one of the joined entities can…
Lukas Eder
  • 211,314
  • 129
  • 689
  • 1,509
6
votes
1 answer

MySQL Optimiser - cost planner doesn't know when DuplicateWeedout Strategy creates disk table

This is my sample query Select table1.id from table1 where table.id in (select table2.id from table2 where table2.id in (select table3.id from table3) …
vinieth
  • 1,204
  • 3
  • 16
  • 34
6
votes
4 answers

Change in query plan and execution time with TOP and ESCAPE

One of the query (given below) is taking 90+ seconds to execute. It returns ~500 rows from a rather large table LogMessage. If ESCAPE N'~' is removed from the query it executes within few seconds. Similarly if TOP (1000) is removed, it executes…
amit_g
  • 30,880
  • 8
  • 61
  • 118
6
votes
1 answer

How to understand the nested loop in PostgreSQL explain?

Please answer,thanks a lot. Q1: why is the query condition a.id = b.id but only scanned the index of a.id at the beginng? but the number of loop is so big ? Q2: What does the 'Nested Loop' node do in the explain ? happydb=# EXPLAIN (ANALYZE,VERBOSE)…
HappyGuitarBoy
  • 85
  • 1
  • 2
  • 8
6
votes
0 answers

The Query had to wait XX seconds for MemoryGrant

I got the warning message The query had to wait xx seconds for MemoryGrant during execution from the SQL execution plan. May I know what are the reasons/possibilities that would cause this warning message to appear? Is it due to system memory leak…
Jimmy
  • 73
  • 1
  • 4
6
votes
1 answer

Why is Postgres EXPLAIN ANALYZE execution_time different than when I run the actual query?

I'm using a database client to test. Using EXPLAIN ANALYZE: Hash Join (cost=5.02..287015.54 rows=3400485 width=33) (actual time=0.023..1725.842 rows=3327845 loops=1) Hash Cond: ((fact_orders.financial_status)::text =…
john
  • 33,520
  • 12
  • 45
  • 62
6
votes
2 answers

How to keep cursors in v$sql_plan alive longer

I'm trying to analyse a query execution plan in my Oracle database. I have set alter system set statistics_level = all; Such that I can compare estimated cardinalities and times with actual cardinalities and times. Now, I'm running this statement…
Lukas Eder
  • 211,314
  • 129
  • 689
  • 1,509
6
votes
3 answers

Same query runs faster when out of proc

We have a specific query that runs a lot slower when inside a proc. I have to add here that it is enclosed within a two level-cursor. However, both cursors have an iteration result-set of one line. Let me first state things that we tried and…
George Menoutis
  • 6,894
  • 3
  • 19
  • 43
6
votes
2 answers

Missing Rows when running SELECT in SQL Server

I have a simple select statement. It's basically 2 CTE's, one includes a ROW_NUMBER() OVER (PARTITION BY, then a join from these into 4 other tables. No functions or anything unusual. WITH Safety_Check_CTE AS ( SELECT …
Mike Fleming
  • 107
  • 1
  • 9
1 2 3
…
95 96