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

Postgresql huge performance difference when using IN vs NOT IN

I have 2 tables, "transaksi" and "buku". "transaksi" has around ~250k rows, and buku has around ~170k rows. Both tables have column called "k999a", and both tables use no indexes. Now I check these 2 statements. Statement 1: explain select k999a…
aaron
  • 257
  • 6
  • 15
6
votes
2 answers

Why does this SQL result in Index Scan instead of an Index Seek?

Can someone please help me tune this SQL query? SELECT a.BuildingID, a.ApplicantID, a.ACH, a.Address, a.Age, a.AgentID, a.AmenityFee, a.ApartmentID, a.Applied, a.AptStatus, a.BikeLocation, a.BikeRent, a.Children, a.CurrentResidence,…
Axeva
  • 4,697
  • 6
  • 40
  • 64
6
votes
1 answer

Postgres: why LEFT JOIN affects to query plan?

I have PostgreSQL 9.5.9 and two tables: table1 and table2 Column | Type | Modifiers ------------+--------------------------------+------------------------------------------- id |…
ox160d05d
  • 600
  • 4
  • 8
6
votes
2 answers

At what cardinality does SQL Server switch to an index scan (vs. seek)

Assuming that a table contains sufficient information to warrant an index seek, at what cardinality will SQL Server (or PostgreSQL) opt for an index scan? The reason I ask this is I previously posted a question (link) in which two queries performed…
IamIC
  • 17,747
  • 20
  • 91
  • 154
6
votes
2 answers

how to generate explain plan for entire stored procedure

I usually generate explain plans using the following in sqlplus: SET AUTOTRACE ON SET TIMING ON SET TRIMSPOOL ON SET LINES 200 SPOOL filename.txt SET AUTOTRACE TRACEONLY; {query goes here} SPOOL OFF SET AUTOTRACE OFF But what If I want to…
learn_plsql
  • 1,681
  • 10
  • 28
  • 34
6
votes
2 answers

MySQL explain filtered column jumping 4,100 with index

My Query: EXPLAIN EXTENDED SELECT `artwork`.`id` , `artwork`.`added` FROM `artwork` ORDER BY `artwork`.`added` DESC LIMIT 0 , 6 When I added an index on "added" to avoid using filesort and use index instead the output of explained went…
Ben
  • 60,438
  • 111
  • 314
  • 488
6
votes
1 answer

What is a pseudo-merge join?

This is the description from Microsoft TechNet explaining Trace Flag 342 (emphasis added): Disables the costing of pseudo-merge joins, thus significantly reducing time spent on the parse for certain types of large, multi-table joins. One can…
DVT
  • 3,014
  • 1
  • 13
  • 19
6
votes
3 answers

query plan shows cost of 54% for an insert when no rows actually involved

In one of my queries there's an insert of data into a temp table. Looking at the query plan, it shows the the actual insert into temp table took 54% (just inserting data into temp table). However, no rows are being inserted into the temp table. Why…
user55474
  • 537
  • 1
  • 8
  • 25
6
votes
1 answer

Why is VALUES(CONVERT(XML,'...')) much slower than VALUES(@xml)?

I would like to create a subquery that produces a list of numbers as a single-column result, something like MindLoggedOut did here but without the @x xml variable, so that it can be appended to a WHERE expression as a pure string (subquery) without…
robert4
  • 1,072
  • 15
  • 20
6
votes
1 answer

Apache Cassandra CQL queries explain plan

How can I get an execution plan (or smth like that) for a CQL query? I failed to find any consolidated document about CQL query optimization/execution. For instance I want to find out, is there any difference in execution of queries like: select…
nukie
  • 691
  • 7
  • 14
6
votes
7 answers

T/SQL Efficiency and Order of Execution

In regards to the order of execution of statements in SQL, is there any difference between the following performance wise? SELECT * FROM Persons WHERE UserType = 'Manager' AND LastName IN ('Hansen','Pettersen') And: SELECT * FROM Persons WHERE…
Kyle Rosendo
  • 25,001
  • 7
  • 80
  • 118
6
votes
3 answers

SQL optimization - execution plan changes based on constraint value - Why?

I've got a table ItemValue full of data on a SQL 2005 Server running in 2000 compatibility mode that looks something like (it's a User-Defined values table): ID ItemCode FieldID Value -- ---------- ------- ------ 1 abc123 …
Cᴏʀʏ
  • 105,112
  • 20
  • 162
  • 194
6
votes
4 answers

Question on how to read a SQL Execution plan

I have executed a query and included the Actual Execution Plan. There is one Hash Match that is of interest to me because it's subtree uses a Index Scan instead of an index seek. When I mouse over this Hash Match there is a section called "Probe…
Abe Miessler
  • 82,532
  • 99
  • 305
  • 486
6
votes
1 answer

Couchbase Query Execution time?

How I can calculate Query time and Query Execution plan in Couchbase.Is there any Utilities like Oracle Explain plan and tkprof in Couchbase db? edit: I am trying to see which database performs best for my data. So i am trying to experiment with…
Vijay
  • 924
  • 1
  • 12
  • 27
6
votes
1 answer

Oracle equivalent of Postgres EXPLAIN ANALYZE

Similar to this question. I'd like to get a detailed query plan and actual execution in Oracle (10g) similar to EXPLAIN ANALYZE in PostgreSQL. Is there an equivalent?
case nelson
  • 3,537
  • 3
  • 30
  • 37
1 2 3
…
95 96