explain.depesz.com

A tool for finding a real cause for slow queries.

Result: J0E

options
Did it help? Consider supporting us - Bitcoin address: 12v2hUztAk2LgzQ9H9LMwuU32urHMjZQnq
# exclusive inclusive rows x rows loops node
1. 13.289 9,616.103 ↑ 1.0 1 1

Nested Loop Semi Join (cost=0.01..362,054,252.71 rows=1 width=89) (actual time=9,616.099..9,616.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 9,602.718 ↓ 3.9 47,667 1

Materialize (cost=0.01..362,053,870.71 rows=12,155 width=4) (actual time=3.343..9,602.718 rows=47,667 loops=1)

6. 53.653 9,580.966 ↓ 3.9 47,667 1

Nested Loop (cost=0.01..362,053,809.93 rows=12,155 width=4) (actual time=3.338..9,580.966 rows=47,667 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. 2,013.642 9,383.754 ↓ 2.5 47,853 1

Index Scan using foo on sales_names (cost=0.01..361,985,580.26 rows=19,116 width=16) (actual time=3.328..9,383.754 rows=47,853 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 49,193

Limit (cost=8.83..12.86 rows=1 width=4) (actual time=0.009..0.009 rows=1 loops=49,193)

10. 98.386 344.351 ↑ 1.0 1 49,193

Bitmap Heap Scan on date_shipping (cost=8.83..12.86 rows=1 width=4) (actual time=0.007..0.007 rows=1 loops=49,193)

  • 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 49,193

BitmapOr (cost=8.83..8.83 rows=1 width=0) (actual time=0.005..0.005 rows=0 loops=49,193)

12. 147.579 147.579 ↑ 1.0 1 49,193

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=49,193)

  • Index Cond: ((d_id = $2) AND (shipping_option_id = 100002))
13. 98.386 98.386 ↑ 1.0 1 49,193

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=49,193)

  • 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 3,543.690 ↑ 1.0 1 37,302

Subquery Scan on sales_dates (cost=0.00..44.26 rows=1 width=8) (actual time=0.094..0.095 rows=1 loops=37,302)

17. 223.812 3,506.388 ↑ 1.0 1 37,302

GroupAggregate (cost=0.00..44.25 rows=1 width=8) (actual time=0.094..0.094 rows=1 loops=37,302)

18. 1,027.412 3,282.576 ↓ 6.0 24 37,302

Nested Loop (cost=0.00..44.22 rows=4 width=8) (actual time=0.007..0.088 rows=24 loops=37,302)

19. 447.624 447.624 ↓ 6.0 24 37,302

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=37,302)

  • Index Cond: ($2 = d_id)
20. 1,807.540 1,807.540 ↑ 1.0 1 903,770

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=903,770)

  • Index Cond: (sp.p_id = p.p_id)
21. 35.985 3,382.590 ↑ 1.0 1 35,985

Subquery Scan on sales_dates (cost=0.00..44.26 rows=1 width=8) (actual time=0.094..0.094 rows=1 loops=35,985)

22. 215.910 3,346.605 ↑ 1.0 1 35,985

GroupAggregate (cost=0.00..44.25 rows=1 width=8) (actual time=0.093..0.093 rows=1 loops=35,985)

23. 952.444 3,130.695 ↓ 6.2 25 35,985

Nested Loop (cost=0.00..44.22 rows=4 width=8) (actual time=0.006..0.087 rows=25 loops=35,985)

24. 395.835 395.835 ↓ 6.2 25 35,985

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=35,985)

  • Index Cond: ($2 = d_id)
25. 1,782.416 1,782.416 ↑ 1.0 1 891,208

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=891,208)

  • 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 47,853

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=47,853)

  • 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)