Questions tagged [explain]

The EXPLAIN statement can be used to obtain information about the database executes a statement.

Filter by
Sorted by
Tagged with
0 votes
1 answer
182 views

Optimize and understand this Query Explain - POSTGRESQL

I have this query which is working perfectly fine. But This takes atleast 10 minutes to finish the query. And I have approximately 12m to 19million records in each table. Need Help to optimize it ...
Mani Deep's user avatar
  • 101
9 votes
1 answer
9k views

Clustered Index Scan (Clustered) vs. Index Seek (NonClustered) + Key Lookup (Clustered)

I have the following tables and content create table t(i int primary key, j int, k char(6000)) create index ix on t(j) insert into t values(1,1,1) insert into t values(2,1,1) insert into t values(3,...
Diogo's user avatar
  • 93
0 votes
3 answers
6k views

Why can I execute this query but not see its execution plan?

Consider the following query: SELECT table_name, to_number(extractvalue(xmltype(dbms_xmlgen.getxml('select count(*) c from ' ||owner ||'.' ||table_name)),'/ROWSET/ROW/C')) AS COUNT FROM ...
Kshitiz Sharma's user avatar
7 votes
1 answer
2k views

Width in Postgres EXPLAIN?

Explain plan in Postgres has width, which (per docs) is: Estimated average width of rows output by this plan node (in bytes). My question: when will width be 0? Because in examples from docs, it ...
LAFK says Reinstate Monica's user avatar
3 votes
2 answers
3k views

Index not used on partitioned table

We have a table similar to this one mysql> SHOW CREATE TABLE my_requests\G *************************** 1. row *************************** Table: my_requests Create Table: CREATE TABLE `...
pconcepcion's user avatar
1 vote
1 answer
3k views

Explain plan gives different results depending on schema

I have a query that joins a multi million row table to one containing hundreds of thousands of rows. Schema owner 'ABC' owns the smaller table and schema owner 'DEF' owns the larger table. They both ...
BriteSponge's user avatar
  • 1,510
4 votes
1 answer
10k views

Visual Explain Not working in MySQL Workbench

Is there some trick to getting the Visual Explain feature to work in MySQL Workbench? I am running version 6.3.6 on Windows 7. I've tried simple queries with just one join. I've tried it with complex ...
IGGt's user avatar
  • 2,126
0 votes
1 answer
3k views

Why can't I SPOOL an EXPLAIN PLAN? [closed]

Why can't I spool the explain plan to a txt file? SPOOL EXPLAIN_PLAN_1.txt set heading off set echo off set pages 999 set long 90000 select plan_table_output from table(dbms_xplan.display('...
Some Java Guy's user avatar
2 votes
1 answer
37 views

How do I find what keys mysql will use for a query?

I just had a query encounter a deadlock, where the lock it was trying to acquire was for an index. But when I used explain to visualize the query, I saw that a different index was listed under key and ...
Benubird's user avatar
  • 475
3 votes
2 answers
2k views

How to prevent PostgreSQL from rewriting OUTER JOIN queries?

My query is: SELECT Acol1, Acol2, Bcol1, Bcol2, Ccol1, Ccol2 FROM tableA LEFT JOIN (tableB FULL JOIN tableC ON (Bcol1 = Ccol1)) ON (Acol1 = Bcol1) EXPLAIN ANALYZE gives ...
Doris W.'s user avatar
0 votes
1 answer
413 views

Is it possible to set order in which inherited tables a scanned in postgresql?

I have a postgresql measurement table which is partitioned based on logdate column by month. Suppose I have city_id integer column and want to select latest row with city_id = 125. When I run explain ...
lompy's user avatar
  • 1
10 votes
1 answer
8k views

What does "Heap Block" in "Bitmap Heap Scan" mean?

I have this query: EXPLAIN (ANALYZE, BUFFERS) SELECT * FROM test WHERE timestamp_range @> '2015-01-22 23:00:00'::timestamp AND data_int_array @> '{49, 61}'::integer[]; Which outputs: ...
Jesús López's user avatar
2 votes
1 answer
2k views

How to enable missing Tempspc column in Oracle explain plan

I am trying to debug "ORA-01652: unable to extend temp segment" from a data warehouse ETL batch job, but my plan_table_output view from explain plan does not show the "Tempspc" column. But I do not ...
aptcat's user avatar
  • 21
0 votes
1 answer
168 views

Combining Explain and Union

Morning all, I'm trying to quickly compare multiple queries that do similar tasks and I'm wondering if there's a quicker way of lining up the EXPLAIN outputs. To me, this example query should work: ...
Samuel Barnett's user avatar
2 votes
1 answer
33 views

Why does PSQL not allow hash indexes with Bitmapscan?

For doing a simple equality query such as EXPLAIN SELECT * FROM table where x = 1; I see that a Bitmap Index scan is done using the BTree indexes. Apparently, Bitmap Index scans disable Hash ...
Abundance's user avatar
  • 123
1 vote
1 answer
6k views

How to optimize MySQL SUM query even after adding index

The below query is taking 11 min to return the result set even after adding indexes and changing the table row format to Compressed. The 2 tables in the below query each have Table 1:...
aalney's user avatar
  • 21
0 votes
4 answers
146 views

MySQL optimizer won't use my index

With the following query: SELECT * FROM geoname LEFT JOIN alternatename ON geoname.geonameid = alternatename.geonameid AND alternatename.isPreferredName = 1 AND alternatename....
marcv's user avatar
  • 163
0 votes
1 answer
5k views

What privileges are needed to run EXPLAIN PLAN FOR CREATE INDEX?

My user can EXPLAIN PLAN FOR any query I like, but not for CREATE INDEX. I need to do that in order to estimate a suggested index's size, and using sysdba is out of question. What privileges should I ...
JCCyC's user avatar
  • 133
3 votes
1 answer
437 views

MySQL stuck in EXPLAIN statement

I am trying to debug the following query. # Projects with at least 50 members with at least 240 yearly commits each select project_id, count(*) as active_members from ( select project_members....
Diomidis Spinellis's user avatar
7 votes
1 answer
792 views

What do the shared buffers metrics for various types of index scans represent?

TL;DR: When an "EXPLAIN (BUFFERS)" shows an "Index Scan", it does not show the number of pages needed to be read from the index. Is it just omitting that number, or does it actually not read pages (...
Mark Hildreth's user avatar
1 vote
2 answers
1k views

Should I stop an awfully inefficient MySQL DELETE statement?

I'm running the following statement on MySQL 5.5 in a 1GB 1CPU SSD virtual machine from Linode: DELETE table FROM ( SELECT MAX(id) id, field FROM table GROUP BY field ) temp_table INNER ...
e18r's user avatar
  • 123
1 vote
0 answers
101 views

Explain partitions

What does the possible keys, reference and rows indicate in explain partitions? I have used partitions on the table and I executing the explain partitions with a query: select count(...
reshu's user avatar
  • 21
1 vote
1 answer
827 views

mysql query choosing an incorrect execution plan

I have a primary with an dependant subquery as a where condition that is choosing a wrong execution plan, and therefore is running for ever. Main query: SELECT rtr.id AS id156_, rtr....
nerlijma's user avatar
  • 113
6 votes
2 answers
2k views

How can I copy the MySQL EXPLAIN result into a table?

I'd like to archive the results of a series of query EXPLAIN plans into a table for later analysis. I created a table that contains all the fields from an EXPLAIN result, but I can't figure out how ...
kcd's user avatar
  • 61
1 vote
1 answer
315 views

How to interpret this XML plan fragment?

I am looking at the query plan produced by SQL Server 2008 R2 and I'm not sure I'm interpreting it properly. Examples: <RelOp NodeId="2" PhysicalOp="Sort" LogicalOp="Sort" EstimateRows="1" ...
mustaccio's user avatar
  • 26k
5 votes
2 answers
4k views

Mysql Index for concat select

For Below Query SELECT MAX(CONCAT(date, ' ', last_entry)) AS LAST_LOG FROM entry_log WHERE TRIM(LEADING 0 FROM card_no)='2948' I've index on date card_no date,last_entry date,last_entry,...
farness's user avatar
  • 153
2 votes
0 answers
907 views

Why is this MySQL query "using where" instead of "using index"?

Could you help me with this query, please? SELECT p.patid, MAX(c1.eventdate) as eventdate from patient as p left join op_adv_effects._clinical as c1 on p.patid = c1.patid left join op_adv_effects....
Antonella's user avatar
4 votes
0 answers
3k views

Amazon Redshift: "*SELECT* 1" vs. volt_dt_0 vs. volt_tt_51343b6aa3bd4

I've noticed subqueries in Amazon Redshift can be represented in the explain plan in 3 separate ways: -> XN Subquery Scan "*SELECT* 1" -> XN Subquery Scan volt_dt_0 -> XN Seq Scan on ...
foobar0100's user avatar
0 votes
2 answers
4k views

Why is "select join insert" so much faster than "loop insert"?

MySQL docs state that the time required for inserts has these approximate proportions: Connecting: (3) Sending query to server: (2) Parsing query: (2) Inserting row: (1 × size of row) ...
Pacerier's user avatar
  • 471
0 votes
0 answers
637 views

Slow query over 10mil rows with group by on indexed column

I've got a slow query which I want to optimize. SELECT pand.bouwjaar AS construction_date, count(*) FROM pand AS pand WHERE pand.begindatumtijdvakgeldigheid <= 'now'::text::timestamp ...
stUrb's user avatar
  • 747
2 votes
1 answer
3k views

Poor query performance due to recheck condition on index scan

We are testing the performance of metadata when stored in JSONB compared to a traditional EAV catalogue (commonly used in biomedical scenarios), using a PostgreSQL 9.4 server. I get a problem when ...
user3111884's user avatar
0 votes
1 answer
94 views

MySQL - How can I improve this query? EXPLAIN attached

I have a query that I feel should go much faster then it does. Right now it'll execute around 1,5xx secs each time. I don't have any advanced knowledge of looking at the EXPLAIN and draw conclusions. ...
naboovalley's user avatar
1 vote
1 answer
109 views

How to optimize search restricted by boolean column?

I want to optimize a query1 of the form SELECT yid, xid FROM x WHERE is_principal; Here, is_principal is a non-null boolean column of x. If I run EXPLAIN QUERY PLAN on this query, the output I get ...
kjo's user avatar
  • 341
6 votes
1 answer
3k views

Monitoring the Postgres query parser

As I understand it, when you send a query to Postgres like: SELECT id FROM table1 WHERE name = $1; Postgres will create a query plan. The plan will be cached for the same query in the same session. ...
Andomar's user avatar
  • 3,495
4 votes
1 answer
2k views

Is there a parser for Postgres' auto_explain to make it easier to read and understand?

We have a number of Postgres functions that each call a number of other Postgres functions. If you simply EXPLAIN ANALYSE a call like SELECT parent_function(), the output is minimal, since it doesn't ...
Taytay's user avatar
  • 325
35 votes
1 answer
33k views

EXPLAIN ANALYZE shows no details for queries inside a plpgsql function

I am using a PL/pgSQL function in PostgreSQL 9.3 with several complex queries inside: create function f1() returns integer as $$ declare event tablename%ROWTYPE; .... .... begin FOR event IN ...
skumar's user avatar
  • 361
-1 votes
1 answer
643 views

Statements in State "Copying to tmp table" disrupt performance [closed]

On a MySQL database (version 5.1.73) a Magento-Shop executes a statement (A) that uses distinct, see http://pastebin.com/A2sy45nF and therefore is "Using temporary" according to "explain". ...
paderEpiktet's user avatar
10 votes
1 answer
3k views

Why does a SELECT statement dirty cache buffers in Postgres?

I am running EXPLAIN (ANALYZE, BUFFERS) SELECT ... in my Postgres 9.3 server. I end up seeing something like Buffers: shared hit=166416 dirtied=2 in the output. From the documentation, "dirtied" ...
D-Rock's user avatar
  • 255
1 vote
1 answer
3k views

mariadb - EXPLAIN works but ANALYZE throws ERROR 1064 (42000) error in SQL syntax

I want to see the execution plan of a query to find out why it is slow, but ANALYZE does not seem to work, it throws an error on the same query that EXPLAIN accepts. MariaDB [dbx]> explain select `...
haheute's user avatar
  • 161
3 votes
2 answers
3k views

Unit of COST in an Oracle execution plan

Is there a unit for cost in an Oracle execution plan? I mean if the cost of an operation is 50 then can I map this number to CPU cycles or utilisation percentage? What does this number stand for? Is ...
user50579's user avatar
1 vote
1 answer
5k views

Slow postgresql query with pagination and filtering

For the DB in question, I have followed the advice of Pagination Done the PostgreSQL Way That is, results are being paginated on a composite index with a definite sort order. Now, my problem is ...
JonoB's user avatar
  • 233
2 votes
0 answers
159 views

Almost identical queries but large time difference in execution

Exhibit A SELECT SUM(value) FROM Credits RIGHT JOIN calendar ON datefield = DATE(FROM_UNIXTIME(created_at)) WHERE confirm = 1 && reason = 1 && country = 'it' && created_at >...
XCore's user avatar
  • 121
0 votes
0 answers
2k views

Oracle 11.2 Explain Plan takes too long

I'm using Oracle 11.2 SE on a 16GB Xeon E3 Linux machine as well as 11.2 XE on a 8GB i7 Win8 machine: SELECT banner FROM v$version: Oracle Database 11g Release 11.2.0.4.0 - 64bit Production PL/SQL ...
Blama's user avatar
  • 151
0 votes
1 answer
30 views

Disc IO for query in MongoDB

is there a way to tell how much data MongoDB is reading from disc and how much from cache for a query ? I looked at the explain()-Function but did not find this kind of informations. Thanks, Andre
user avatar
8 votes
3 answers
9k views

MySQL Explain has different row count than slow query log

I have this entry in slow query log: # User@Host: user[host] @ [ip] # Thread_id: 1514428 Schema: db Last_errno: 0 Killed: 0 # Query_time: 2.795454 Lock_time: 0.000116 Rows_sent: 15 ...
codefreak's user avatar
  • 183
2 votes
1 answer
597 views

What is this view being used in my query?

I'm running EXPLAIN PLAN on this query: SELECT DISTINCT l.item_num, l.repl_code FROM item_w l JOIN item i ON l.item_num = i.item_num LEFT JOIN item_w r ON l.item_num = r.item_num AND l.repl_code = r....
Michael McGriff's user avatar
3 votes
1 answer
882 views

Mysql join table order by index

First Master Table CREATE TABLE `edipostingmaster` ( `ID` bigint(20) NOT NULL AUTO_INCREMENT, `applied` int(11) DEFAULT '0', `fileName` varchar(255) DEFAULT NULL, `checkNo` varchar(100) ...
Senthil Muthiah's user avatar
1 vote
1 answer
1k views

postgres explain plan with giant gaps between operations

I have a postgresql explain plan that seems to have large gaps between the join, sort & filter processes and can't explain (sorry) it. The query is taking an inordinate amount of time and the ...
JaeRae's user avatar
  • 13
1 vote
2 answers
285 views

Can I get some rationale from the Optimzer why it chose a certain explain plan?

This is not about reading an explain plan. This is not about adding any hints or measuring performance of a specific query. I have always been wondering whether it's possible for a given explain ...
Martin's user avatar
  • 2,420
0 votes
2 answers
93 views

Why search by date field requires engine to touch all records?

Why search by date field requires engine to touch all records? Is there any way to avoid it? Following query EXPLAIN select * FROM playdays where realdate>=Date('2010,01,01') ; shows that amount ...
Budda's user avatar
  • 101