#	exclusive	inclusive	rows x	rows	loops	node
1.	160.832	986.172	↓ 1.4	1099	1	→ Nested Loop Left Join (cost=22675.24..25587.16 rows=787 width=12) (actual time=711.249..986.172 rows=1099 loops=1) Join Filter: (e.id = e.id)
2.	0.723	372.552	↓ 1.4	1099	1	→ Hash Left Join (cost=22675.24..25036.76 rows=787 width=8) (actual time=358.332..372.552 rows=1099 loops=1) Hash Cond: (e.id = e.id)
3.	13.705	13.705	↓ 1.4	1099	1	→ Seq Scan on events e (cost=0.00..2281.75 rows=787 width=4) (actual time=0.191..13.705 rows=1099 loops=1) Filter: display
4.	0.334	358.124	↓ 1.3	1021	1	→ Hash (cost=22665.40..22665.40 rows=787 width=4) (actual time=358.124..358.124 rows=1021 loops=1) Buckets: 1024 Batches: 1 Memory Usage: 36kB
5.	16.075	357.790	↓ 1.3	1021	1	→ HashAggregate (cost=22649.66..22657.53 rows=787 width=4) (actual time=357.539..357.790 rows=1021 loops=1)
6.	37.096	341.715	↓ 1.8	44764	1	→ Nested Loop (cost=0.00..22586.86 rows=25121 width=4) (actual time=0.192..341.715 rows=44764 loops=1)
7.	18.954	120.887	↓ 2.0	61244	1	→ Nested Loop (cost=0.00..11754.43 rows=30778 width=8) (actual time=0.185..120.887 rows=61244 loops=1)
8.	4.399	31.373	↓ 1.6	14112	1	→ Nested Loop (cost=0.00..4926.29 rows=8830 width=8) (actual time=0.177..31.373 rows=14112 loops=1)
9.	15.984	15.984	↓ 1.4	1099	1	→ Seq Scan on events e (cost=0.00..2281.75 rows=787 width=4) (actual time=0.165..15.984 rows=1099 loops=1) Filter: display
10.	10.990	10.990	↑ 1.7	13	1099	→ Index Scan using markets_event_id_index on markets m (cost=0.00..3.09 rows=22 width=8) (actual time=0.004..0.010 rows=13 loops=1099) Index Cond: (event_id = e.id)
11.	70.560	70.560	↑ 3.2	4	14112	→ Index Scan using outcomes_market_id_index on outcomes o (cost=0.00..0.61 rows=13 width=8) (actual time=0.003..0.005 rows=4 loops=14112) Index Cond: (market_id = m.id)
12.	183.732	183.732	↑ 1.0	1	61244	→ Index Scan using prices_outcome_id_key on prices p (cost=0.00..0.34 rows=1 width=4) (actual time=0.003..0.003 rows=1 loops=61244) Index Cond: (outcome_id = o.id)
13.	100.603	452.788	↓ 1012.0	1012	1099	→ Materialize (cost=0.00..538.60 rows=1 width=4) (actual time=0.000..0.412 rows=1012 loops=1099)
14.	6.738	352.185	↓ 1012.0	1012	1	→ Group (cost=0.00..538.59 rows=1 width=4) (actual time=0.083..352.185 rows=1012 loops=1)
15.	24.182	345.447	↓ 44042.0	44042	1	→ Nested Loop (cost=0.00..538.58 rows=1 width=4) (actual time=0.081..345.447 rows=44042 loops=1)
16.	28.851	141.706	↓ 59853.0	59853	1	→ Nested Loop (cost=0.00..536.30 rows=1 width=8) (actual time=0.073..141.706 rows=59853 loops=1)
17.	6.654	57.839	↓ 6877.0	13754	1	→ Nested Loop (cost=0.00..531.76 rows=2 width=8) (actual time=0.065..57.839 rows=13754 loops=1)
18.	7.109	7.109	↓ 91.8	14692	1	→ Index Scan using markets_event_id_idx on markets m (cost=0.00..220.66 rows=160 width=8) (actual time=0.014..7.109 rows=14692 loops=1)
19.	44.076	44.076	↑ 1.0	1	14692	→ Index Scan using events_pkey on events e (cost=0.00..1.93 rows=1 width=4) (actual time=0.002..0.003 rows=1 loops=14692) Index Cond: (id = m.event_id) Filter: display
20.	55.016	55.016	↓ 4.0	4	13754	→ Index Scan using outcomes_market_id_idx on outcomes o (cost=0.00..2.26 rows=1 width=8) (actual time=0.003..0.004 rows=4 loops=13754) Index Cond: (market_id = m.id)
21.	179.559	179.559	↑ 1.0	1	59853	→ Index Scan using prices_outcome_id_key on prices p (cost=0.00..2.27 rows=1 width=4) (actual time=0.003..0.003 rows=1 loops=59853) Index Cond: (outcome_id = o.id)

 Nested Loop Left Join  (cost=22675.24..25587.16 rows=787 width=12) (actual time=711.249..986.172 rows=1099 loops=1)
   Join Filter: (e.id = e.id)
   ->  Hash Left Join  (cost=22675.24..25036.76 rows=787 width=8) (actual time=358.332..372.552 rows=1099 loops=1)
         Hash Cond: (e.id = e.id)
         ->  Seq Scan on events e  (cost=0.00..2281.75 rows=787 width=4) (actual time=0.191..13.705 rows=1099 loops=1)
               Filter: display
         ->  Hash  (cost=22665.40..22665.40 rows=787 width=4) (actual time=358.124..358.124 rows=1021 loops=1)
               Buckets: 1024  Batches: 1  Memory Usage: 36kB
               ->  HashAggregate  (cost=22649.66..22657.53 rows=787 width=4) (actual time=357.539..357.790 rows=1021 loops=1)
                     ->  Nested Loop  (cost=0.00..22586.86 rows=25121 width=4) (actual time=0.192..341.715 rows=44764 loops=1)
                           ->  Nested Loop  (cost=0.00..11754.43 rows=30778 width=8) (actual time=0.185..120.887 rows=61244 loops=1)
                                 ->  Nested Loop  (cost=0.00..4926.29 rows=8830 width=8) (actual time=0.177..31.373 rows=14112 loops=1)
                                       ->  Seq Scan on events e  (cost=0.00..2281.75 rows=787 width=4) (actual time=0.165..15.984 rows=1099 loops=1)
                                             Filter: display
                                       ->  Index Scan using markets_event_id_index on markets m  (cost=0.00..3.09 rows=22 width=8) (actual time=0.004..0.010 rows=13 loops=1099)
                                             Index Cond: (event_id = e.id)
                                 ->  Index Scan using outcomes_market_id_index on outcomes o  (cost=0.00..0.61 rows=13 width=8) (actual time=0.003..0.005 rows=4 loops=14112)
                                       Index Cond: (market_id = m.id)
                           ->  Index Scan using prices_outcome_id_key on prices p  (cost=0.00..0.34 rows=1 width=4) (actual time=0.003..0.003 rows=1 loops=61244)
                                 Index Cond: (outcome_id = o.id)
   ->  Materialize  (cost=0.00..538.60 rows=1 width=4) (actual time=0.000..0.412 rows=1012 loops=1099)
         ->  Group  (cost=0.00..538.59 rows=1 width=4) (actual time=0.083..352.185 rows=1012 loops=1)
               ->  Nested Loop  (cost=0.00..538.58 rows=1 width=4) (actual time=0.081..345.447 rows=44042 loops=1)
                     ->  Nested Loop  (cost=0.00..536.30 rows=1 width=8) (actual time=0.073..141.706 rows=59853 loops=1)
                           ->  Nested Loop  (cost=0.00..531.76 rows=2 width=8) (actual time=0.065..57.839 rows=13754 loops=1)
                                 ->  Index Scan using markets_event_id_idx on markets m  (cost=0.00..220.66 rows=160 width=8) (actual time=0.014..7.109 rows=14692 loops=1)
                                 ->  Index Scan using events_pkey on events e  (cost=0.00..1.93 rows=1 width=4) (actual time=0.002..0.003 rows=1 loops=14692)
                                       Index Cond: (id = m.event_id)
                                       Filter: display
                           ->  Index Scan using outcomes_market_id_idx on outcomes o  (cost=0.00..2.26 rows=1 width=8) (actual time=0.003..0.004 rows=4 loops=13754)
                                 Index Cond: (market_id = m.id)
                     ->  Index Scan using prices_outcome_id_key on prices p  (cost=0.00..2.27 rows=1 width=4) (actual time=0.003..0.003 rows=1 loops=59853)
                           Index Cond: (outcome_id = o.id)
 Total runtime: 986.460 ms

Per node type stats

node type	count	sum of times	% of query
Group	1	6.738 ms	0.7 %
Hash	1	0.334 ms	0.0 %
Hash Left Join	1	0.723 ms	0.1 %
HashAggregate	1	16.075 ms	1.6 %
Index Scan	7	551.042 ms	55.9 %
Materialize	1	100.603 ms	10.2 %
Nested Loop	6	120.136 ms	12.2 %
Nested Loop Left Join	1	160.832 ms	16.3 %
Seq Scan	2	29.689 ms	3.0 %

Per table stats

Table name	Scan count	Total time	% of query
scan type	count	sum of times	% of table
events	3	73.765 ms	7.5 %
Index Scan	1	44.076 ms	59.8 %
Seq Scan	2	29.689 ms	40.2 %
markets	2	18.099 ms	1.8 %
Index Scan	2	18.099 ms	100.0 %
outcomes	2	125.576 ms	12.7 %
Index Scan	2	125.576 ms	100.0 %
prices	2	363.291 ms	36.8 %
Index Scan	2	363.291 ms	100.0 %

explain.depesz.com

A tool for finding a real cause for slow queries.

Result: R29

Colorize:

Columns visibility:

Per node type stats

Per table stats