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
5
votes
2 answers

How do I know what's behind the `auto_key0` index in MySQL?

Saying I have this query: EXPLAIN SELECT * FROM ( SELECT "A" as a, i.n FROM (SELECT 1 AS n) AS i UNION ALL SELECT "B" as a, i.n FROM (SELECT 1 AS n) AS i) AS t WHERE a = "B"; MySQL says id select_type table partitions type …
Xenos
  • 3,351
  • 2
  • 27
  • 50
5
votes
1 answer

Why does postgres do a table scan instead of using my index?

I'm working with the HackerNews dataset in Postgres. There are about 17M rows about 14.5M of them are comments and about 2.5M are stories. There is a very active user named "rbanffy" who has 25k submissions, about equal split stories/comments. …
Yehosef
  • 17,987
  • 7
  • 35
  • 56
5
votes
1 answer

How to find out what is represented by the intermediate value used in SQL Server execution plan

I'm learning how to read the actual execution plan in SQL Server. I have noticed that SQL Server tends to represent intermediate value used in the physical query plan as e.g. expr1006, expr1007 etc. (i.e. expr followed by a number). Here is a…
Thor
  • 9,638
  • 15
  • 62
  • 137
5
votes
6 answers

Are SQL Execution Plans based on Schema or Data or both?

I hope this question is not too obvious...I have already found lots of good information on interpreting execution plans but there is one question I haven't found the answer to. Is the plan (and more specifically the relative CPU cost) based on the…
Rich
  • 1,165
  • 1
  • 15
  • 29
5
votes
1 answer

Postgres hash join vs nested loop decision making

I have this very long query which I'll summarize here and paste in total on the bottom: select * from a left join b t1 on a.x = b.x left join b t2 on a.y = b.x left join b t3 on a.z = b.x left join c on a.1 = c.1 and a.2 = c.2 and a.3 = c.3 --call…
Henrietta Martingale
  • 771
  • 8
  • 27
5
votes
0 answers

Why does DBD::SQLite have a different query plan for an SQL statement

I have an INSERT INTO... SELECT ... FROM SQL statement that runs acceptably fast when executed from SQLite's command line shell. However, if I execute the same statement (copy/pasted) with Perl's DBI::SQLite, the statement becomes slow. The reason…
René Nyffenegger
  • 39,402
  • 33
  • 158
  • 293
5
votes
2 answers

How can I work around SQL Server - Inline Table Value Function execution plan variation based on parameters?

Here is the situation: I have a table value function with a datetime parameter ,lest's say tdf(p_date) , that filters about two million rows selecting those with column date smaller than p_date and computes some aggregate values on other columns. It…
Ovidiu Pacurar
  • 8,173
  • 2
  • 30
  • 36
5
votes
1 answer

Why actual time in EXPLAIN result is different with every execution in PostgreSQL?

I am trying to test efficiency of different partitioning schemas in PostgreSQL. I created several databases with one table and different partition sizes and then populated hundreds of million records in each. Now I would like to test some queries on…
dmkov
  • 334
  • 2
  • 11
5
votes
3 answers

mysql multi column index not working (as expected)?

I have a table like this CREATE TABLE IF NOT EXISTS `tbl_folder` ( `id` int(11) NOT NULL AUTO_INCREMENT, `owner_userid` int(11) NOT NULL, `name` varchar(63) NOT NULL, `description` text NOT NULL, `visibility` tinyint(4) NOT NULL DEFAULT…
alp_chen
  • 53
  • 2
5
votes
0 answers

Row Level Security Query Plan is 45x slower than identical non-RLS query

I'm having some trouble getting the query planner to write good plans for row level security (RLS) enabled tables. It seems all it takes is a join from a row level security enabled table to a non-row level security enabled table to force a bad plan…
Justin
  • 500
  • 8
  • 18
5
votes
2 answers

Optimize a query that is using multiple left joins on the same tables

I've come across a query that is taking "too long". The query has 50+ left joins between 10 or so tables. To give a brief overview of the database model, the tables joined are tables that store data for a particular data type (ex: date_fields,…
Dan G
  • 65
  • 1
  • 3
5
votes
3 answers

MySQL slow query with join even though EXPLAIN shows good plan

I have the following scenario: In a MySQL database, I have 2 MyISAM tables, one with 4.2 million rows, and another with 320 million rows. The following is the schema for the tables: Table1 (4.2M rows) F1 INTEGER UNSIGNED NOT NULL PRIMARY KEY f2…
Tim
  • 51
  • 2
5
votes
3 answers

Oracle explain plan estimates incorrect cardinality for an index range scan

I have an Oracle 10.2.0.3 database, and a query like this: select count(a.id) from LARGE_PARTITIONED_TABLE a join SMALL_NONPARTITIONED_TABLE b on a.key1 = b.key1 and a.key2 = b.key2 where b.id = 1000 Table LARGE_PARTITIONED_TABLE (a) has about 5…
Tommi
  • 8,550
  • 5
  • 32
  • 51
5
votes
1 answer

Understanding Number of Executions in Execution plan

Below is the simple execution plan for below query. Query: SELECT TOP (25) orderid, custid, empid, shipperid, orderdate, filler FROM dbo.Orders ORDER BY orderid; Execution plan: My question is how to make sense of number of executions Below is the…
TheGameiswar
  • 27,855
  • 8
  • 56
  • 94
5
votes
2 answers

How can a stored proc have multiple execution plans?

I am working with MS SQL Server 2008 R2. I have a stored procedure named rpt_getWeeklyScheduleData. This is the query I used to look up its execution plan in a specific database: select * from sys.dm_exec_cached_plans cp CROSS APPLY…
srh
  • 1,661
  • 4
  • 30
  • 57
1 2 3
…
95 96