Result: kVJ

options

HTML
TEXT
STATS

exclusive	inclusive	rows x	rows	loops	node
6.016	620215.216	↑ 1.0	1	1	→ Aggregate (cost=2017589.33..2017589.34 rows=1 width=0) (actual time=620215.216..620215.216 rows=1 loops=1) Total runtime: 620215.454 ms
24.059	620209.200	↓ 2.7	11572	1	→ Nested Loop (cost=0.00..2017578.75 rows=4230 width=0) (actual time=50.985..620209.200 rows=11572 loops=1) Join Filter: ((sales_names.p_max_sales > sales_prices.p_sales) OR (sales_names.p_max_sales = (-1)))
119.897	620068.381	↓ 2.0	11676	1	→ Index Scan using sales_names_e_id_idx on sales_names (cost=0.00..1984322.18 rows=5823 width=8) (actual time=50.969..620068.381 rows=11676 loops=1) Index Cond: (e_id = 142578) Filter: (((d_max_sales > (SubPlan 2)) OR (d_max_sales = (-1))) AND ((e_max_sales > (SubPlan 1)) OR (e_max_sales = (-1))))
					SubPlan (forIndex Scan)
23.620	8196.140	↑ 2.0	1	11810	→ Subquery Scan on sales_dates (cost=0.00..44.64 rows=2 width=8) (actual time=0.694..0.694 rows=1 loops=11810)
342.490	8172.520	↑ 2.0	1	11810	→ GroupAggregate (cost=0.00..44.62 rows=2 width=8) (actual time=0.692..0.692 rows=1 loops=11810)
661.954	7830.030	↓ 31.8	127	11810	→ Nested Loop (cost=0.00..44.57 rows=4 width=8) (actual time=0.020..0.663 rows=127 loops=11810)
1157.380	1157.380	↓ 31.8	127	11810	→ Index Scan using prices_d_id on prices p (cost=0.00..10.76 rows=4 width=8) (actual time=0.011..0.098 rows=127 loops=11810) Index Cond: ($1 = d_id)
6010.696	6010.696	↑ 1.0	1	1502674	→ Index Scan using sales_prices_p_id on sales_prices sp (cost=0.00..8.44 rows=1 width=8) (actual time=0.003..0.004 rows=1 loops=1502674) Index Cond: (sp.p_id = p.p_id)
11.676	611752.344	↑ 2.0	1	11676	→ Subquery Scan on sales_events (cost=0.00..119.71 rows=2 width=8) (actual time=52.394..52.394 rows=1 loops=11676)
28629.552	611740.668	↑ 2.0	1	11676	→ GroupAggregate (cost=0.00..119.69 rows=2 width=8) (actual time=52.393..52.393 rows=1 loops=11676)
117775.812	583111.116	↓ 984.2	11810	11676	→ Nested Loop (cost=0.00..119.60 rows=12 width=8) (actual time=0.022..49.941 rows=11810 loops=11676)
51654.624	51654.624	↓ 984.2	11810	11676	→ Index Scan using prices_e_id_idx on prices p (cost=0.00..18.17 rows=12 width=8) (actual time=0.015..4.424 rows=11810 loops=11676) Index Cond: ($0 = e_id)
413680.680	413680.680	↑ 1.0	1	137893560	→ Index Scan using sales_prices_p_id on sales_prices sp (cost=0.00..8.44 rows=1 width=8) (actual time=0.003..0.003 rows=1 loops=137893560) Index Cond: (sp.p_id = p.p_id)
116.760	116.760	↑ 1.0	1	11676	→ Index Scan using sales_prices_sales_names_id_uni on sales_prices (cost=0.00..5.69 rows=1 width=8) (actual time=0.009..0.010 rows=1 loops=11676) Index Cond: (sales_prices.sales_names_id = sales_names.sales_names_id)

Aggregate  (cost=2017589.33..2017589.34 rows=1 width=0) (actual time=620215.216..620215.216 rows=1 loops=1)
   ->  Nested Loop  (cost=0.00..2017578.75 rows=4230 width=0) (actual time=50.985..620209.200 rows=11572 loops=1)
         Join Filter: ((sales_names.p_max_sales > sales_prices.p_sales) OR (sales_names.p_max_sales = (-1)))
         ->  Index Scan using sales_names_e_id_idx on sales_names  (cost=0.00..1984322.18 rows=5823 width=8) (actual time=50.969..620068.381 rows=11676 loops=1)
               Index Cond: (e_id = 142578)
               Filter: (((d_max_sales > (SubPlan 2)) OR (d_max_sales = (-1))) AND ((e_max_sales > (SubPlan 1)) OR (e_max_sales = (-1))))
               SubPlan 2
                 ->  Subquery Scan on sales_dates  (cost=0.00..44.64 rows=2 width=8) (actual time=0.694..0.694 rows=1 loops=11810)
                       ->  GroupAggregate  (cost=0.00..44.62 rows=2 width=8) (actual time=0.692..0.692 rows=1 loops=11810)
                             ->  Nested Loop  (cost=0.00..44.57 rows=4 width=8) (actual time=0.020..0.663 rows=127 loops=11810)
                                   ->  Index Scan using prices_d_id on prices p  (cost=0.00..10.76 rows=4 width=8) (actual time=0.011..0.098 rows=127 loops=11810)
                                         Index Cond: ($1 = d_id)
                                   ->  Index Scan using sales_prices_p_id on sales_prices sp  (cost=0.00..8.44 rows=1 width=8) (actual time=0.003..0.004 rows=1 loops=1502674)
                                         Index Cond: (sp.p_id = p.p_id)
               SubPlan 1
                 ->  Subquery Scan on sales_events  (cost=0.00..119.71 rows=2 width=8) (actual time=52.394..52.394 rows=1 loops=11676)
                       ->  GroupAggregate  (cost=0.00..119.69 rows=2 width=8) (actual time=52.393..52.393 rows=1 loops=11676)
                             ->  Nested Loop  (cost=0.00..119.60 rows=12 width=8) (actual time=0.022..49.941 rows=11810 loops=11676)
                                   ->  Index Scan using prices_e_id_idx on prices p  (cost=0.00..18.17 rows=12 width=8) (actual time=0.015..4.424 rows=11810 loops=11676)
                                         Index Cond: ($0 = e_id)
                                   ->  Index Scan using sales_prices_p_id on sales_prices sp  (cost=0.00..8.44 rows=1 width=8) (actual time=0.003..0.003 rows=1 loops=137893560)
                                         Index Cond: (sp.p_id = p.p_id)
         ->  Index Scan using sales_prices_sales_names_id_uni on sales_prices  (cost=0.00..5.69 rows=1 width=8) (actual time=0.009..0.010 rows=1 loops=11676)
               Index Cond: (sales_prices.sales_names_id = sales_names.sales_names_id)
 Total runtime: 620215.454 ms
(25 rows)

Per node type stats

node type	count	sum of times	% of query
Aggregate	1	6.016 ms	0.0 %
GroupAggregate	2	28972.042 ms	4.7 %
Index Scan	6	472740.037 ms	76.2 %
Nested Loop	3	118461.825 ms	19.1 %
Subquery Scan on sales_dates	1	23.620 ms	0.0 %
Subquery Scan on sales_events	1	11.676 ms	0.0 %

Per table stats

Table name	Scan count	Total time	% of query
scan type	count	sum of times	% of table
prices	2	52812.004 ms	8.5 %
Index Scan	2	52812.004 ms	100.0 %
sales_names	1	119.897 ms	0.0 %
Index Scan	1	119.897 ms	100.0 %
sales_prices	3	419808.136 ms	67.7 %
Index Scan	3	419808.136 ms	100.0 %

explain.depesz.com

A tool for finding a real cause for slow queries.

Result: kVJ

Colorize:

Columns visibility:

Per node type stats

Per table stats