Databases
StackOverflow2013
Postsdbo
POOptimizing my postgres query
 

Note that there are some explanatory texts on larger screens.

plurals
  1. POOptimizing my postgres query
    primarykey
    data
    text
    <p>Can I optimize this query, or modify the table structure in order to shorten the execution time? I don't really understand the output of <code>EXPLAIN</code>. Am I missing some index?</p> <pre><code>EXPLAIN SELECT COUNT(*) AS count, q.query_str FROM click_fact cf, query q, date_dim dd, queries_p_day_mv qpd WHERE dd.date_dim_id = qpd.date_dim_id AND qpd.query_id = q.query_id AND type = 'S' AND cf.query_id = q.query_id *emphasized text* AND dd.pg_date BETWEEN '2010-12-29' AND '2011-01-28' AND qpd.interface_id IN (SELECT DISTINCT interface_id from interface WHERE lang = 'sv') GROUP BY q.query_str ORDER BY count DESC; QUERY PLAN ------------------------------------------------------------------------------------------------------------------------------------- Sort (cost=19170.15..19188.80 rows=7460 width=12) Sort Key: (count(*)) -&gt; HashAggregate (cost=18597.03..18690.28 rows=7460 width=12) -&gt; Nested Loop (cost=10.20..18559.73 rows=7460 width=12) -&gt; Nested Loop (cost=10.20..14975.36 rows=2452 width=20) Join Filter: (qpd.interface_id = interface.interface_id) -&gt; Unique (cost=1.03..1.04 rows=1 width=4) -&gt; Sort (cost=1.03..1.04 rows=1 width=4) Sort Key: interface.interface_id -&gt; Seq Scan on interface (cost=0.00..1.02 rows=1 width=4) Filter: (lang = 'sv'::text) -&gt; Nested Loop (cost=9.16..14943.65 rows=2452 width=24) -&gt; Hash Join (cost=9.16..14133.58 rows=2452 width=8) Hash Cond: (qpd.date_dim_id = dd.date_dim_id) -&gt; Seq Scan on queries_p_day_mv qpd (cost=0.00..11471.93 rows=700793 width=12) -&gt; Hash (cost=8.81..8.81 rows=28 width=4) -&gt; Index Scan using date_dim_pg_date_index on date_dim dd (cost=0.00..8.81 rows=28 width=4) Index Cond: ((pg_date &gt;= '2010-12-29'::date) AND (pg_date &lt;= '2011-01-28'::date)) -&gt; Index Scan using query_pkey on query q (cost=0.00..0.32 rows=1 width=16) Index Cond: (q.query_id = qpd.query_id) -&gt; Index Scan using click_fact_query_id_index on click_fact cf (cost=0.00..1.01 rows=36 width=4) Index Cond: (cf.query_id = qpd.query_id) Filter: (cf.type = 'S'::bpchar) </code></pre> <p><strong>Updated with EXPLAIN ANALYZE:</strong></p> <pre><code>EXPLAIN ANALYZE SELECT COUNT(*) AS count, q.query_str FROM click_fact cf, query q, date_dim dd, queries_p_day_mv qpd WHERE dd.date_dim_id = qpd.date_dim_id AND qpd.query_id = q.query_id AND type = 'S' AND cf.query_id = q.query_id AND dd.pg_date BETWEEN '2010-12-29' AND '2011-01-28' AND qpd.interface_id IN (SELECT DISTINCT interface_id from interface WHERE lang = 'sv') GROUP BY q.query_str ORDER BY count DESC; QUERY PLAN ---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------- Sort (cost=19201.06..19220.52 rows=7784 width=12) (actual time=51017.162..51046.102 rows=17586 loops=1) Sort Key: (count(*)) Sort Method: external merge Disk: 632kB -&gt; HashAggregate (cost=18600.67..18697.97 rows=7784 width=12) (actual time=50935.411..50968.678 rows=17586 loops=1) -&gt; Nested Loop (cost=10.20..18561.75 rows=7784 width=12) (actual time=42.079..43666.404 rows=3868592 loops=1) -&gt; Nested Loop (cost=10.20..14975.91 rows=2453 width=20) (actual time=23.678..14609.282 rows=700803 loops=1) Join Filter: (qpd.interface_id = interface.interface_id) -&gt; Unique (cost=1.03..1.04 rows=1 width=4) (actual time=0.104..0.110 rows=1 loops=1) -&gt; Sort (cost=1.03..1.04 rows=1 width=4) (actual time=0.100..0.102 rows=1 loops=1) Sort Key: interface.interface_id Sort Method: quicksort Memory: 25kB -&gt; Seq Scan on interface (cost=0.00..1.02 rows=1 width=4) (actual time=0.038..0.041 rows=1 loops=1) Filter: (lang = 'sv'::text) -&gt; Nested Loop (cost=9.16..14944.20 rows=2453 width=24) (actual time=23.550..12553.786 rows=700808 loops=1) -&gt; Hash Join (cost=9.16..14133.80 rows=2453 width=8) (actual time=18.283..3885.700 rows=700808 loops=1) Hash Cond: (qpd.date_dim_id = dd.date_dim_id) -&gt; Seq Scan on queries_p_day_mv qpd (cost=0.00..11472.08 rows=700808 width=12) (actual time=0.014..1587.106 rows=700808 loops=1) -&gt; Hash (cost=8.81..8.81 rows=28 width=4) (actual time=18.221..18.221 rows=31 loops=1) -&gt; Index Scan using date_dim_pg_date_index on date_dim dd (cost=0.00..8.81 rows=28 width=4) (actual time=14.388..18.152 rows=31 loops=1) Index Cond: ((pg_date &gt;= '2010-12-29'::date) AND (pg_date &lt;= '2011-01-28'::date)) -&gt; Index Scan using query_pkey on query q (cost=0.00..0.32 rows=1 width=16) (actual time=0.005..0.006 rows=1 loops=700808) Index Cond: (q.query_id = qpd.query_id) -&gt; Index Scan using click_fact_query_id_index on click_fact cf (cost=0.00..1.01 rows=36 width=4) (actual time=0.005..0.022 rows=6 loops=700803) Index Cond: (cf.query_id = qpd.query_id) Filter: (cf.type = 'S'::bpchar) </code></pre>
    singulars
    1. This table or related slice is empty.
    1. This table or related slice is empty.
    1. PTQuestion
    1. USJohan
    1. USJohan
    plurals
    1. This table or related slice is empty.
    1. This table or related slice is empty.
    1. This table or related slice is empty.
    1. PO
      singulars
      1. PTAnswer
    2. PO
      singulars
      1. PTAnswer
    1. VO
      singulars
      1. POOptimizing my postgres query
      1. This table or related slice is empty.
      1. VTUpMod
    1. COI suggest using JOIN syntax instead of the sql92 style, and then see if you get the same plan. I know it shouldn't happen, but I've seen big changes in speed between the two styles at times - perhaps because being clearer in your intention helps the query optimizer too?
      singulars
      1. POOptimizing my postgres query
      1. USiain
    2. CO@iain: could you please post a sample query which changes its plan in `PostgreSQL` when rewritten `ANSI` style?
      singulars
      1. POOptimizing my postgres query
      1. USQuassnoi
    3. CO@Quassinoi - no, because I don't and won't have access to any of the queries that I've seen it happen with. I've seen it most on mysql to be fair. You'll just have to live with the anecdote :)
      singulars
      1. POOptimizing my postgres query
      1. USiain
 

Querying!

 
Guidance
A row detail

Detail views are divided into sections. All the information in the data section comes from columns in the selected row. The other sections display data from other, related rows.

Related data can be related in a to-one or a to-many fashion. Captions of data related in a to-many fashion link to a list view showing a filtered view of the table.

Try moving around until you find a non-empty to-many entry and click on the label to get to one. You can move back to the root by clicking on the database name in the header.

SQuiL has stopped working due to an internal error.

If you are curious you may find further information in the browser console, which is accessible through the devtools (F12).

Reload