Stack Overflow
Stack Overflow

Questions tagged [explain]

Explain is a SQL command that shows the execution plan of a query.

Explain is an useful command used in various Database servers(like Oracle, MySQL, PostgreSQL, etc). It shows the execution plan of a query.

Oracle: 

EXPLAIN PLAN FOR SELECT …

PostgreSQL, MySQL:

EXPLAIN SELECT …

MSSQL doesn't support explain, but uses

"SET SHOWPLAN_TEXT ON".

instead.

599 questions
4
votes
4 answers

mySQL - How can I interpret my EXPLAIN results and optimize this query?

Looking to understand what my EXPLAIN results mean here, and to optimize this query and my tables as best as I can. The query: SELECT i.pending, i.itemid, i.message, i.cid, i.dateadded, i.entrypoint, …
barfoon
  • 27,481
  • 26
  • 92
  • 138
4
votes
4 answers

Improve MySQL Query with IN Subquery

I hava a table items and a table item_attributes. For simplicity let's say my table item has a column id and a column name. Of cource there is a index on the id column. the item_attributes table has the columns id, item_id, attribute_name and…
Jürgen Steinblock
  • 30,746
  • 24
  • 119
  • 189
4
votes
3 answers

Can I run an "explain analyze" on a query using JOOQ?

Can I run explain analyze on a query in JOOQ? like: explain analyse select some, columns from some_table but do it using JOOQ on PostgreSQL database? I have found an interface org.jooq.Explain, with a method DSLContext.explain​(Query query) - but…
hc0re
  • 1,806
  • 2
  • 26
  • 61
4
votes
2 answers

How to use mysql's EXPLAIN to look for possible problems

Stress testing a site & everything is breaking, obviously. Today's problem: WSOD on several pages. After a few hours I have narrowed the problem on one page down to this query (I hope): It used to run in a second; now it takes > 300. SELECT …
jisaacstone
  • 4,234
  • 2
  • 25
  • 39
4
votes
1 answer

Mongodb query explain opterationTime meaning?

We want to find the performance difference between the regular string comparison vs regex comparison. need the further understanding/explanation about the 'operationTime'. any one can explain the meaning of 'operationTime'? "operationTime" :…
Patrick Li
  • 51
  • 2
4
votes
1 answer

SEARCH TABLE vs SCAN TABLE

sqlite> .schema actor CREATE TABLE actor ( id INTEGER PRIMARY KEY, name TEXT, gender TEXT ); sqlite> explain query plan ...> select * from actor where id = '305453'; 0|0|0|SEARCH TABLE actor USING INTEGER PRIMARY KEY (rowid=?) sqlite> explain…
overexchange
  • 15,768
  • 30
  • 152
  • 347
4
votes
2 answers

How does postgres decide whether to use index scan or seq scan?

explain analyze shows that postgres will use index scanning for my query that fetches rows and performs filtering by date (i.e., 2017-04-14 05:27:51.039): explain analyze select * from tbl t where updated > '2017-04-14 05:27:51.039'; …
lolski
  • 16,231
  • 7
  • 34
  • 49
4
votes
1 answer

In a PostgreSQL query plan, what is the difference between Materialize and Hash?

Here's an example of a query plan with Materialize: Nested Loop (cost=4.65..49.46 rows=33 width=488) Join Filter: (t1.hundred < t2.hundred) -> ... (outer) -> Materialize (cost=0.29..8.51 rows=10 width=244) -> ... (inner) And…
AndreKR
  • 32,613
  • 18
  • 106
  • 168
4
votes
2 answers

mysql sql plan ,what type=range meaning in this sql

explain SELECT * FROM pop_order_info poi WHERE poi.is_delete = 0 and poi.vendor_id =7879 group by poi.order_sn limit 10; …
Yafang Gao
  • 41
  • 1
  • 1
  • 3
4
votes
1 answer

PostgreSQL: exists vs left join

I heard many times that postgres handles exists queries even faster then left join. http://archives.postgresql.org/pgsql-performance/2002-12/msg00185.php That's definitely true for one table aggregation. But in our case their is more then one and…
Bogdan Gusiev
  • 8,027
  • 16
  • 61
  • 81
4
votes
1 answer

How to prevent changing of execution plan for certain values

I have a table in PosgreSQL 9.1.9. There's a schema: CREATE TABLE chpl_text ( id integer NOT NULL DEFAULT nextval('chpl_text_id_seq1'::regclass), page_id bigint NOT NULL, page_idx integer NOT NULL, ... ); I have around 40000000 (40M) rows…
Crazy Yoghurt
  • 2,365
  • 2
  • 26
  • 37
4
votes
2 answers

postgres group by integer type columns faster than character type columns?

I have 4 tables which are create table web_content_3 ( content integer, hits bigint, bytes bigint, appid varchar(32) ); create table web_content_4 ( content character varying (128 ), hits bigint, bytes bigint, appid varchar(32) ); create table…
mayurpatel
  • 53
  • 6
4
votes
3 answers

EXPLAIN ANALYZE within PL/pgSQL gives error: "query has no destination for result data"

I am trying to understand the query plan for a select statement within a PL/pgSQL function, but I keep getting errors. My question: how do I get the query plan? Following is a simple case that reproduces the problem. The table in question is named…
ARV
  • 6,287
  • 11
  • 31
  • 41
4
votes
1 answer

Reuse computed select value

I'm trying to use ST_SnapToGrid and then GROUP BY the grid cells (x, y). Here is what I did first: SELECT COUNT(*) AS n, ST_X(ST_SnapToGrid(geom, 50)) AS x, ST_Y(ST_SnapToGrid(geom, 50)) AS y FROM points GROUP BY x, y I…
Ilia Choly
  • 18,070
  • 14
  • 92
  • 160
4
votes
2 answers

How to read PostgreSQL EXPLAIN, order: top down or bottom up?

Here's an example plan on explain.depesz.com: Limit (cost=65301.950..65301.950 rows=1 width=219) (actual time=886.074..886.074 rows=0 loops=1) -> Sort (cost=65258.840..65301.950 rows=17243 width=219) (actual time=879.683..885.211 rows=17589…
TheFooProgrammer
  • 2,439
  • 5
  • 28
  • 43
1 2 3
…
39 40