The tag has no usage guidance.

learn more… | top users | synonyms (1)

0
votes
2answers
49 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 ...
2
votes
1answer
39 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 ...
1
vote
2answers
88 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 `...
1
vote
1answer
62 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 ...
0
votes
0answers
110 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 ...
1
vote
1answer
71 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('...
2
votes
1answer
26 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 ...
0
votes
0answers
56 views

EXPLAIN SELECT query is writing to tmp table

I am getting a "copying to tmp table" when running an EXPLAIN SELECT on a query. This is a pseudo-query showing the structure of the query (I can't post the actual query): SELECT colA , colB , ...
2
votes
2answers
69 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 ...
0
votes
1answer
28 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 ...
4
votes
1answer
148 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: ...
1
vote
0answers
82 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 ...
0
votes
1answer
34 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: ...
2
votes
1answer
19 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 ...
0
votes
1answer
145 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:...
0
votes
3answers
78 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....
0
votes
1answer
387 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 ...
2
votes
1answer
74 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....
5
votes
1answer
90 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 (...
1
vote
2answers
183 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 ...
1
vote
0answers
67 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(...
1
vote
1answer
145 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....
6
votes
2answers
211 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 ...
2
votes
1answer
97 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" ...
3
votes
2answers
115 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,...
2
votes
0answers
336 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....
1
vote
0answers
372 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 ...
0
votes
2answers
1k 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) ...
0
votes
0answers
139 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 ...
2
votes
1answer
735 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 ...
0
votes
1answer
53 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. ...
1
vote
1answer
26 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 ...
0
votes
0answers
253 views

MySQL explain query: Using temporary table vs more rows examined

EDITED: Can you tell me which explain plan is better?! First: explain SELECT SQL_NO_CACHE count(*) FROM a force index(uni) WHERE a.date BETWEEN 20141001 AND 20141031 AND ((a.cid = 157 AND a.tid IN ( ...
3
votes
1answer
568 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....
2
votes
1answer
273 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 ...
3
votes
1answer
775 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 ...
-1
votes
1answer
139 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". ...
6
votes
1answer
367 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" ...
1
vote
1answer
336 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 `...
0
votes
0answers
68 views

Optimizing query - understanding explain

I've got a query (partly generated by django orm, but can be modified). For some reason, It's very slow (between 1.5 -3 seconds, without having lots of data on the system) . I've got an "explain" ...
1
vote
2answers
460 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 ...
1
vote
1answer
908 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 ...
0
votes
0answers
98 views

Explain plan shows a table not in the query

In trying to optimize a 2 minute query against an SAP R3 Oracle database, I examined the Explain Plan in Toad and found that most of the query's time is spent on a table that isn't even in the query. ...
2
votes
0answers
148 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 >...
0
votes
0answers
707 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 ...
0
votes
1answer
22 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
1
vote
0answers
822 views

sql query takes too long to execute [closed]

I am having trouble with this query. It takes 10 hours to execute. WITH CONSUM AS ( SELECT C.ID_MEASURER,C.ID_MEASURING_POINT,C.CONSUM_TYPE FROM GCGT_RE_CONSUM_H_E C WHERE C.DAY ...
3
votes
3answers
3k 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 ...
2
votes
1answer
111 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....
3
votes
1answer
203 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) ...