The tag has no usage guidance.

learn more… | top users | synonyms (1)

0
votes
1answer
20 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 ...
5
votes
1answer
108 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 ...
4
votes
1answer
38 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: ...
3
votes
1answer
181 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) ...
1
vote
0answers
17 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
31 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: ...
8
votes
3answers
11k views

Why does MySQL ignore the index even on force for this order by?

I run an EXPLAIN: mysql> explain select last_name from employees order by last_name; +----+-------------+-----------+------+---------------+------+---------+------+-------+----------------+ | ...
0
votes
3answers
71 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 ...
2
votes
1answer
17 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
41 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 ...
0
votes
1answer
170 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 ...
1
vote
1answer
171 views

Help interpreting a MySQL execution plan

I'm trying to understand a slow query. The SELECT has been running (at this moment) for almost 18000 seconds (output from a SELECT on PROCESSLIST): id: 22 time: 17870 ...
8
votes
1answer
308 views

Why would function based indices I’ve created lower the cost but not show up in the explain plan breakdown?

Sorry for the terrible column/table names but since this is for a work project I wanted to insure it was OK to ask. I was just hoping to at least learn why I'm not seeing my functional indices being ...
5
votes
1answer
79 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 ...
2
votes
1answer
56 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 ...
3
votes
1answer
377 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
vote
2answers
133 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
59 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 ...
1
vote
1answer
113 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_, ...
2
votes
1answer
72 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" ...
14
votes
3answers
15k views

Why am I getting `Impossible WHERE noticed after reading const tables` in explain query?

I have a unique compound key like fr(fromid,toid) in the table, when I run the query with explain I get the following result: Impossible WHERE noticed after reading const tables` The query I ran: ...
2
votes
1answer
176 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
2answers
107 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 ...
5
votes
1answer
293 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" ...
2
votes
0answers
231 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 ...
1
vote
0answers
214 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 ...
-1
votes
2answers
675 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
120 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 ...
19
votes
3answers
26k views

Optimizing queries on a range of timestamps (two columns)

I use postgresql-9.1 with ubuntu 12.04. I need to select records inside a range of time: my table time_limits has two timestamp fields and one property integer. Indeed there are other info columns ...
2
votes
1answer
511 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
51 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
25 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 ...
1
vote
0answers
185 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 ( ...
2
votes
1answer
442 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 ...
0
votes
1answer
123 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". ...
1
vote
2answers
337 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 ...
0
votes
0answers
66 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
1answer
276 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 ...
1
vote
1answer
692 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
90 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
143 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 ...
3
votes
3answers
2k 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 ...
0
votes
0answers
556 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
20 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
644 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 ...
2
votes
1answer
86 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 = ...
0
votes
1answer
142 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 ...
1
vote
2answers
172 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 ...
1
vote
2answers
727 views

Is there any manual for the visual explain in mysql? The output plan is unreadable

Just like this example, I cannot even tell the join ordering from it. And what does "ALL" and "ref" in the table mean? Is there any manual about the visual explain?
0
votes
2answers
69 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 ...