Database Administrators Stack Exchange
Database Administrators Stack Exchange

Questions tagged [explain]

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

With the help of EXPLAIN, you can see where you should add indexes to tables so that the statement executes faster by using indexes to find rows. You can also use EXPLAIN to check whether the optimizer joins the tables in an optimal order. To give a hint to the optimizer to use a join order corresponding to the order in which the tables are named in a SELECT statement, begin the statement with SELECT STRAIGHT_JOIN rather than just SELECT.

References

9.8.1 Optimizing Queries with EXPLAIN

247 questions
148
votes
4 answers

Optimizing queries on a range of timestamps (two columns)

I use PostgreSQL 9.1 on Ubuntu 12.04. I need to select records inside a range of time: my table time_limits has two timestamp fields and one integer property. There are additional columns in my actual table that are not involved with this…
Stephane Rolland
  • 8,911
  • 11
  • 33
  • 40
36
votes
1 answer

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 SELECT * FROM tablename WHERE condition LOOP …
skumar
  • 371
  • 1
  • 3
  • 7
32
votes
3 answers

Understanding time format of the EXPLAIN command - Postgres

When I run the EXPLAIN ANALYZE command on a given query, I'm having a difficult time interpreting the outputted time value. For example (actual time=8163.890..8163.893). Do the internal decimals represent repeating characters?? Sorry, this may be a…
Jmoney38
  • 1,175
  • 5
  • 13
  • 22
31
votes
2 answers

Why is count(*) slow, when explain knows the answer?

This query: select count(*) from planner_event takes a very long time to run - so long, I gave up and killed it before it finished. However, when I run explain select count(*) from planner_event, I can see a column in the output with the number of…
Benubird
  • 495
  • 1
  • 5
  • 8
29
votes
4 answers

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: explain SELECT rid FROM relationship WHERE…
Alireza
  • 3,676
  • 10
  • 38
  • 44
26
votes
2 answers

Get query plan for SQL statement nested in a PL/pgSQL function

Is it possible to get the query plan for an SQL statement executed inside a user defined function (UDF)? Like with using EXPLAIN in pgAdmin or psql as client. I see the UDF abstracted away into a single operation F() in pgAdmin. Currently, I pull…
Hassan Syed
  • 437
  • 1
  • 4
  • 9
20
votes
1 answer

what is "planSummary: IDHACK"?

This Query scans only one document and returns only one document. But this is very slow: 2017-05-22T07:13:24.548+0000 I COMMAND [conn40] query databasename.collectionname query: { _id: ObjectId('576d4ce3f2d62a001e84a9b8') } planSummary: IDHACK…
Sybil
  • 2,578
  • 6
  • 34
  • 61
16
votes
3 answers

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; +----+-------------+-----------+------+---------------+------+---------+------+-------+----------------+ | id | select_type | table | type | possible_keys |…
Cratylus
  • 1,013
  • 3
  • 12
  • 18
12
votes
1 answer

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" indicates: The number of blocks dirtied indicates the…
D-Rock
  • 275
  • 2
  • 7
11
votes
1 answer

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: Bitmap Heap Scan on test (cost=16.74..20.75 rows=1…
Jesús López
  • 213
  • 2
  • 5
10
votes
1 answer

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,1,1) insert into t values(4,1,1) insert into t…
Diogo
  • 103
  • 1
  • 4
8
votes
3 answers

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 Rows_examined: 65207 Rows_affected: 0 Rows_read: 65207 #…
codefreak
  • 183
  • 1
  • 7
8
votes
1 answer

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 used so I felt better about adding this indices in…
Rapida
  • 181
  • 1
8
votes
2 answers

Selecting top 10 from indexed field of a big table takes too long

I have a table with 165M records like this: Performance id integer installs integer hour timestamp without time zone I also have an index on hour: CREATE INDEX hour_idx ON performance USING btree (hour DESC NULLS…
Dejell
  • 185
  • 8
7
votes
2 answers

Is there a good "rule of thumb" for translating EXPLAIN cost to (wall-clock) runtime?

From time to time, consumers of my database processes will ask for an estimate of when a given task will be done. While I feel like I know how to read an EXPLAIN in most database engines, I have trouble trying to translate this to "ask me again in…
TML
  • 1,374
  • 13
  • 21
1
2 3
…
16 17