#	exclusive	inclusive	rows x	rows	loops	node
1.	13.289	9616.103	↑ 1.0	1	1	→ Nested Loop Semi Join (cost=0.01..362054252.71 rows=1 width=89) (actual time=9616.099..9616.103 rows=1 loops=1) Join Filter: (public.sales_names.p_id = public.sales_names.p_id)
2.	0.004	0.040	↑ 1.0	1	1	→ Nested Loop (cost=0.00..27.64 rows=1 width=89) (actual time=0.036..0.040 rows=1 loops=1)
3.	0.025	0.025	↑ 1.0	1	1	→ Index Scan using sales_names_d_id_idx on sales_names (cost=0.00..19.13 rows=1 width=85) (actual time=0.022..0.025 rows=1 loops=1) Index Cond: (d_id = 646751) Filter: (e_id = 244671)
4.	0.011	0.011	↑ 1.0	1	1	→ Index Scan using sales_prices_sales_names_id_uni on sales_prices (cost=0.00..8.50 rows=1 width=8) (actual time=0.011..0.011 rows=1 loops=1) Index Cond: (public.sales_prices.sales_names_id = public.sales_names.sales_names_id)
5.	21.752	9602.718	↓ 3.9	47667	1	→ Materialize (cost=0.01..362053870.71 rows=12155 width=4) (actual time=3.343..9602.718 rows=47667 loops=1)
6.	53.653	9580.966	↓ 3.9	47667	1	→ Nested Loop (cost=0.01..362053809.93 rows=12155 width=4) (actual time=3.338..9580.966 rows=47667 loops=1) Join Filter: ((public.sales_names.p_max_sales = (-1)) OR ((public.sales_names.p_max_sales > public.sales_prices.p_sales) AND ((public.sales_names.p_max_sales - public.sales_prices.p_sales) >= public.sales_names.order_min)))
7.	2013.642	9383.754	↓ 2.5	47853	1	→ Index Scan using foo on sales_names (cost=0.01..361985580.26 rows=19116 width=16) (actual time=3.328..9383.754 rows=47853 loops=1) Index Cond: ((e_start_date < now()) AND (p_start_date <= (now())::timestamp without time zone) AND (p_end_date >= (now())::timestamp without time zone)) Filter: ((((d_sales_end >= (now())::timestamp without time zone) AND (SubPlan 7)) OR ((d_start_date >= ((now())::timestamp without time zone + '3 days'::interval)) AND (SubPlan 8))) AND ((d_max_sales = (-1)) OR ((d_max_sales > (Sub
8.						SubPlan (forIndex Scan)
9.	98.386	442.737	↑ 1.0	1	49193	→ Limit (cost=8.83..12.86 rows=1 width=4) (actual time=0.009..0.009 rows=1 loops=49193)
10.	98.386	344.351	↑ 1.0	1	49193	→ Bitmap Heap Scan on date_shipping (cost=8.83..12.86 rows=1 width=4) (actual time=0.007..0.007 rows=1 loops=49193) Recheck Cond: (((d_id = $2) AND (shipping_option_id = 100002)) OR ((d_id = $2) AND (shipping_option_id = 100004))) Filter: ((ds_start <= now()) AND (ds_end > now()))
11.	0.000	245.965	↓ 0.0	0	49193	→ BitmapOr (cost=8.83..8.83 rows=1 width=0) (actual time=0.005..0.005 rows=0 loops=49193)
12.	147.579	147.579	↑ 1.0	1	49193	→ Bitmap Index Scan on uidx_date_shipping (cost=0.00..4.42 rows=1 width=0) (actual time=0.003..0.003 rows=1 loops=49193) Index Cond: ((d_id = $2) AND (shipping_option_id = 100002))
13.	98.386	98.386	↑ 1.0	1	49193	→ Bitmap Index Scan on uidx_date_shipping (cost=0.00..4.42 rows=1 width=0) (actual time=0.002..0.002 rows=1 loops=49193) Index Cond: ((d_id = $2) AND (shipping_option_id = 100004))
14.	0.219	1.095	↑ 1.0	1	219	→ Limit (cost=0.00..8.44 rows=1 width=4) (actual time=0.005..0.005 rows=1 loops=219)
15.	0.876	0.876	↑ 1.0	1	219	→ Index Scan using uidx_date_shipping on date_shipping (cost=0.00..8.44 rows=1 width=4) (actual time=0.004..0.004 rows=1 loops=219) Index Cond: ((d_id = $2) AND (shipping_option_id = 100001)) Filter: ((ds_start <= now()) AND (ds_end > now()))
16.	37.302	3543.690	↑ 1.0	1	37302	→ Subquery Scan on sales_dates (cost=0.00..44.26 rows=1 width=8) (actual time=0.094..0.095 rows=1 loops=37302)
17.	223.812	3506.388	↑ 1.0	1	37302	→ GroupAggregate (cost=0.00..44.25 rows=1 width=8) (actual time=0.094..0.094 rows=1 loops=37302)
18.	1027.412	3282.576	↓ 6.0	24	37302	→ Nested Loop (cost=0.00..44.22 rows=4 width=8) (actual time=0.007..0.088 rows=24 loops=37302)
19.	447.624	447.624	↓ 6.0	24	37302	→ Index Scan using prices_d_id on prices p (cost=0.00..10.18 rows=4 width=8) (actual time=0.003..0.012 rows=24 loops=37302) Index Cond: ($2 = d_id)
20.	1807.540	1807.540	↑ 1.0	1	903770	→ Index Scan using sales_prices_p_id on sales_prices sp (cost=0.00..8.50 rows=1 width=8) (actual time=0.002..0.002 rows=1 loops=903770) Index Cond: (sp.p_id = p.p_id)
21.	35.985	3382.590	↑ 1.0	1	35985	→ Subquery Scan on sales_dates (cost=0.00..44.26 rows=1 width=8) (actual time=0.094..0.094 rows=1 loops=35985)
22.	215.910	3346.605	↑ 1.0	1	35985	→ GroupAggregate (cost=0.00..44.25 rows=1 width=8) (actual time=0.093..0.093 rows=1 loops=35985)
23.	952.444	3130.695	↓ 6.2	25	35985	→ Nested Loop (cost=0.00..44.22 rows=4 width=8) (actual time=0.006..0.087 rows=25 loops=35985)
24.	395.835	395.835	↓ 6.2	25	35985	→ Index Scan using prices_d_id on prices p (cost=0.00..10.18 rows=4 width=8) (actual time=0.003..0.011 rows=25 loops=35985) Index Cond: ($2 = d_id)
25.	1782.416	1782.416	↑ 1.0	1	891208	→ Index Scan using sales_prices_p_id on sales_prices sp (cost=0.00..8.50 rows=1 width=8) (actual time=0.002..0.002 rows=1 loops=891208) Index Cond: (sp.p_id = p.p_id)
26.	0.000	0.000	↓ 0.0		0	→ Subquery Scan on sales_events (cost=0.00..127.78 rows=2 width=8) (actual time=.. rows= loops=0)
27.	0.000	0.000	↓ 0.0		0	→ GroupAggregate (cost=0.00..127.76 rows=2 width=8) (actual time=.. rows= loops=0)
28.	0.000	0.000	↓ 0.0		0	→ Nested Loop (cost=0.00..127.67 rows=13 width=8) (actual time=.. rows= loops=0)
29.	0.000	0.000	↓ 0.0		0	→ Index Scan using prices_e_id_idx on prices p (cost=0.00..17.04 rows=13 width=8) (actual time=.. rows= loops=0) Index Cond: ($3 = e_id)
30.	0.000	0.000	↓ 0.0		0	→ Index Scan using sales_prices_p_id on sales_prices sp (cost=0.00..8.50 rows=1 width=8) (actual time=.. rows= loops=0) Index Cond: (sp.p_id = p.p_id)
31.	0.000	0.000	↓ 0.0		0	→ Subquery Scan on sales_events (cost=0.00..127.78 rows=2 width=8) (actual time=.. rows= loops=0)
32.	0.000	0.000	↓ 0.0		0	→ GroupAggregate (cost=0.00..127.76 rows=2 width=8) (actual time=.. rows= loops=0)
33.	0.000	0.000	↓ 0.0		0	→ Nested Loop (cost=0.00..127.67 rows=13 width=8) (actual time=.. rows= loops=0)
34.	0.000	0.000	↓ 0.0		0	→ Index Scan using prices_e_id_idx on prices p (cost=0.00..17.04 rows=13 width=8) (actual time=.. rows= loops=0) Index Cond: ($3 = e_id)
35.	0.000	0.000	↓ 0.0		0	→ Index Scan using sales_prices_p_id on sales_prices sp (cost=0.00..8.50 rows=1 width=8) (actual time=.. rows= loops=0) Index Cond: (sp.p_id = p.p_id)
36.	143.559	143.559	↑ 1.0	1	47853	→ Index Scan using sales_prices_sales_names_id_uni on sales_prices (cost=0.00..3.55 rows=1 width=8) (actual time=0.003..0.003 rows=1 loops=47853) Index Cond: (public.sales_prices.sales_names_id = public.sales_names.sales_names_id)
37.						SubPlan (forNested Loop Semi Join)
38.	0.007	0.035	↑ 2.0	1	1	→ Subquery Scan on sales_events (cost=0.00..127.78 rows=2 width=8) (actual time=0.030..0.035 rows=1 loops=1)
39.	0.003	0.028	↑ 2.0	1	1	→ GroupAggregate (cost=0.00..127.76 rows=2 width=8) (actual time=0.028..0.028 rows=1 loops=1)
40.	0.005	0.025	↑ 13.0	1	1	→ Nested Loop (cost=0.00..127.67 rows=13 width=8) (actual time=0.021..0.025 rows=1 loops=1)
41.	0.011	0.011	↑ 13.0	1	1	→ Index Scan using prices_e_id_idx on prices p (cost=0.00..17.04 rows=13 width=8) (actual time=0.011..0.011 rows=1 loops=1) Index Cond: ($0 = e_id)
42.	0.009	0.009	↑ 1.0	1	1	→ Index Scan using sales_prices_p_id on sales_prices sp (cost=0.00..8.50 rows=1 width=8) (actual time=0.005..0.009 rows=1 loops=1) Index Cond: (sp.p_id = p.p_id)
43.	0.003	0.021	↑ 1.0	1	1	→ Subquery Scan on sales_dates (cost=0.00..44.26 rows=1 width=8) (actual time=0.020..0.021 rows=1 loops=1)
44.	0.005	0.018	↑ 1.0	1	1	→ GroupAggregate (cost=0.00..44.25 rows=1 width=8) (actual time=0.018..0.018 rows=1 loops=1)
45.	0.004	0.013	↑ 4.0	1	1	→ Nested Loop (cost=0.00..44.22 rows=4 width=8) (actual time=0.012..0.013 rows=1 loops=1)
46.	0.004	0.004	↑ 4.0	1	1	→ Index Scan using prices_d_id on prices p (cost=0.00..10.18 rows=4 width=8) (actual time=0.004..0.004 rows=1 loops=1) Index Cond: ($1 = d_id)
47.	0.005	0.005	↑ 1.0	1	1	→ Index Scan using sales_prices_p_id on sales_prices sp (cost=0.00..8.50 rows=1 width=8) (actual time=0.004..0.005 rows=1 loops=1) Index Cond: (sp.p_id = p.p_id)

 Nested Loop Semi Join  (cost=0.01..362054252.71 rows=1 width=89) (actual time=9616.099..9616.103 rows=1 loops=1)
   Join Filter: (public.sales_names.p_id = public.sales_names.p_id)
   ->  Nested Loop  (cost=0.00..27.64 rows=1 width=89) (actual time=0.036..0.040 rows=1 loops=1)
         ->  Index Scan using sales_names_d_id_idx on sales_names  (cost=0.00..19.13 rows=1 width=85) (actual time=0.022..0.025 rows=1 loops=1)
               Index Cond: (d_id = 646751)
               Filter: (e_id = 244671)
         ->  Index Scan using sales_prices_sales_names_id_uni on sales_prices  (cost=0.00..8.50 rows=1 width=8) (actual time=0.011..0.011 rows=1 loops=1)
               Index Cond: (public.sales_prices.sales_names_id = public.sales_names.sales_names_id)
   ->  Materialize  (cost=0.01..362053870.71 rows=12155 width=4) (actual time=3.343..9602.718 rows=47667 loops=1)
         ->  Nested Loop  (cost=0.01..362053809.93 rows=12155 width=4) (actual time=3.338..9580.966 rows=47667 loops=1)
               Join Filter: ((public.sales_names.p_max_sales = (-1)) OR ((public.sales_names.p_max_sales > public.sales_prices.p_sales) AND ((public.sales_names.p_max_sales - public.sales_prices.p_sales) >= public.sales_names.order_min)))
               ->  Index Scan using foo on sales_names  (cost=0.01..361985580.26 rows=19116 width=16) (actual time=3.328..9383.754 rows=47853 loops=1)
                     Index Cond: ((e_start_date < now()) AND (p_start_date <= (now())::timestamp without time zone) AND (p_end_date >= (now())::timestamp without time zone))
                     Filter: ((((d_sales_end >= (now())::timestamp without time zone) AND (SubPlan 7)) OR ((d_start_date >= ((now())::timestamp without time zone + '3 days'::interval)) AND (SubPlan 8))) AND ((d_max_sales = (-1)) OR ((d_max_sales > (Sub
Plan 3)) AND ((d_max_sales - (SubPlan 4)) >= order_min))) AND ((e_max_sales = (-1)) OR ((e_max_sales > (SubPlan 5)) AND ((e_max_sales - (SubPlan 6)) >= order_min))))
                     SubPlan 7
                       ->  Limit  (cost=8.83..12.86 rows=1 width=4) (actual time=0.009..0.009 rows=1 loops=49193)
                             ->  Bitmap Heap Scan on date_shipping  (cost=8.83..12.86 rows=1 width=4) (actual time=0.007..0.007 rows=1 loops=49193)
                                   Recheck Cond: (((d_id = $2) AND (shipping_option_id = 100002)) OR ((d_id = $2) AND (shipping_option_id = 100004)))
                                   Filter: ((ds_start <= now()) AND (ds_end > now()))
                                   ->  BitmapOr  (cost=8.83..8.83 rows=1 width=0) (actual time=0.005..0.005 rows=0 loops=49193)
                                         ->  Bitmap Index Scan on uidx_date_shipping  (cost=0.00..4.42 rows=1 width=0) (actual time=0.003..0.003 rows=1 loops=49193)
                                               Index Cond: ((d_id = $2) AND (shipping_option_id = 100002))
                                         ->  Bitmap Index Scan on uidx_date_shipping  (cost=0.00..4.42 rows=1 width=0) (actual time=0.002..0.002 rows=1 loops=49193)
                                               Index Cond: ((d_id = $2) AND (shipping_option_id = 100004))
                     SubPlan 8
                       ->  Limit  (cost=0.00..8.44 rows=1 width=4) (actual time=0.005..0.005 rows=1 loops=219)
                             ->  Index Scan using uidx_date_shipping on date_shipping  (cost=0.00..8.44 rows=1 width=4) (actual time=0.004..0.004 rows=1 loops=219)
                                   Index Cond: ((d_id = $2) AND (shipping_option_id = 100001))
                                   Filter: ((ds_start <= now()) AND (ds_end > now()))
                     SubPlan 3
                       ->  Subquery Scan on sales_dates  (cost=0.00..44.26 rows=1 width=8) (actual time=0.094..0.095 rows=1 loops=37302)
                             ->  GroupAggregate  (cost=0.00..44.25 rows=1 width=8) (actual time=0.094..0.094 rows=1 loops=37302)
                                   ->  Nested Loop  (cost=0.00..44.22 rows=4 width=8) (actual time=0.007..0.088 rows=24 loops=37302)
                                         ->  Index Scan using prices_d_id on prices p  (cost=0.00..10.18 rows=4 width=8) (actual time=0.003..0.012 rows=24 loops=37302)
                                               Index Cond: ($2 = d_id)
                                         ->  Index Scan using sales_prices_p_id on sales_prices sp  (cost=0.00..8.50 rows=1 width=8) (actual time=0.002..0.002 rows=1 loops=903770)
                                               Index Cond: (sp.p_id = p.p_id)
                     SubPlan 4
                       ->  Subquery Scan on sales_dates  (cost=0.00..44.26 rows=1 width=8) (actual time=0.094..0.094 rows=1 loops=35985)
                             ->  GroupAggregate  (cost=0.00..44.25 rows=1 width=8) (actual time=0.093..0.093 rows=1 loops=35985)
                                   ->  Nested Loop  (cost=0.00..44.22 rows=4 width=8) (actual time=0.006..0.087 rows=25 loops=35985)
                                         ->  Index Scan using prices_d_id on prices p  (cost=0.00..10.18 rows=4 width=8) (actual time=0.003..0.011 rows=25 loops=35985)
                                               Index Cond: ($2 = d_id)
                                         ->  Index Scan using sales_prices_p_id on sales_prices sp  (cost=0.00..8.50 rows=1 width=8) (actual time=0.002..0.002 rows=1 loops=891208)
                                               Index Cond: (sp.p_id = p.p_id)
                     SubPlan 5
                       ->  Subquery Scan on sales_events  (cost=0.00..127.78 rows=2 width=8) (never executed)
                             ->  GroupAggregate  (cost=0.00..127.76 rows=2 width=8) (never executed)
                                   ->  Nested Loop  (cost=0.00..127.67 rows=13 width=8) (never executed)
                                         ->  Index Scan using prices_e_id_idx on prices p  (cost=0.00..17.04 rows=13 width=8) (never executed)
                                               Index Cond: ($3 = e_id)
                                         ->  Index Scan using sales_prices_p_id on sales_prices sp  (cost=0.00..8.50 rows=1 width=8) (never executed)
                                               Index Cond: (sp.p_id = p.p_id)
                     SubPlan 6
                       ->  Subquery Scan on sales_events  (cost=0.00..127.78 rows=2 width=8) (never executed)
                             ->  GroupAggregate  (cost=0.00..127.76 rows=2 width=8) (never executed)
                                   ->  Nested Loop  (cost=0.00..127.67 rows=13 width=8) (never executed)
                                         ->  Index Scan using prices_e_id_idx on prices p  (cost=0.00..17.04 rows=13 width=8) (never executed)
                                               Index Cond: ($3 = e_id)
                                         ->  Index Scan using sales_prices_p_id on sales_prices sp  (cost=0.00..8.50 rows=1 width=8) (never executed)
                                               Index Cond: (sp.p_id = p.p_id)
               ->  Index Scan using sales_prices_sales_names_id_uni on sales_prices  (cost=0.00..3.55 rows=1 width=8) (actual time=0.003..0.003 rows=1 loops=47853)
                     Index Cond: (public.sales_prices.sales_names_id = public.sales_names.sales_names_id)
   SubPlan 1
     ->  Subquery Scan on sales_events  (cost=0.00..127.78 rows=2 width=8) (actual time=0.030..0.035 rows=1 loops=1)
           ->  GroupAggregate  (cost=0.00..127.76 rows=2 width=8) (actual time=0.028..0.028 rows=1 loops=1)
                 ->  Nested Loop  (cost=0.00..127.67 rows=13 width=8) (actual time=0.021..0.025 rows=1 loops=1)
                       ->  Index Scan using prices_e_id_idx on prices p  (cost=0.00..17.04 rows=13 width=8) (actual time=0.011..0.011 rows=1 loops=1)
                             Index Cond: ($0 = e_id)
                       ->  Index Scan using sales_prices_p_id on sales_prices sp  (cost=0.00..8.50 rows=1 width=8) (actual time=0.005..0.009 rows=1 loops=1)
                             Index Cond: (sp.p_id = p.p_id)
   SubPlan 2
     ->  Subquery Scan on sales_dates  (cost=0.00..44.26 rows=1 width=8) (actual time=0.020..0.021 rows=1 loops=1)
           ->  GroupAggregate  (cost=0.00..44.25 rows=1 width=8) (actual time=0.018..0.018 rows=1 loops=1)
                 ->  Nested Loop  (cost=0.00..44.22 rows=4 width=8) (actual time=0.012..0.013 rows=1 loops=1)
                       ->  Index Scan using prices_d_id on prices p  (cost=0.00..10.18 rows=4 width=8) (actual time=0.004..0.004 rows=1 loops=1)
                             Index Cond: ($1 = d_id)
                       ->  Index Scan using sales_prices_p_id on sales_prices sp  (cost=0.00..8.50 rows=1 width=8) (actual time=0.004..0.005 rows=1 loops=1)
                             Index Cond: (sp.p_id = p.p_id)
 Total runtime: 9617.516 ms

Per node type stats

node type	count	sum of times	% of query
Bitmap Heap Scan	1	98.386 ms	1.0 %
Bitmap Index Scan	2	245.965 ms	2.6 %
BitmapOr	1	0.000 ms	0.0 %
GroupAggregate	6	439.730 ms	4.6 %
Index Scan	17	6591.557 ms	68.5 %
Limit	2	98.605 ms	1.0 %
Materialize	1	21.752 ms	0.2 %
Nested Loop	8	2033.522 ms	21.1 %
Nested Loop Semi Join	1	13.289 ms	0.1 %
Subquery Scan on sales_dates	3	73.290 ms	0.8 %
Subquery Scan on sales_events	3	0.007 ms	0.0 %

Per table stats

Table name	Scan count	Total time	% of query
scan type	count	sum of times	% of table
date_shipping	2	99.262 ms	1.0 %
Bitmap Heap Scan	1	98.386 ms	99.1 %
Index Scan	1	0.876 ms	0.9 %
prices	6	843.474 ms	8.8 %
Index Scan	6	843.474 ms	100.0 %
sales_names	2	2013.667 ms	20.9 %
Index Scan	2	2013.667 ms	100.0 %
sales_prices	8	3733.540 ms	38.8 %
Index Scan	8	3733.540 ms	100.0 %

explain.depesz.com

A tool for finding a real cause for slow queries.

Result: J0E

Colorize:

Columns visibility:

Per node type stats

Per table stats