explain.depesz.com

A tool for finding a real cause for slow queries.

Result: QWZ

options
# exclusive inclusive rows x rows loops node
1. 0.002 33.320 ↓ 2.0 4 1
→

Limit (cost=617.39..617.41 rows=2 width=53) (actual time=33.310..33.320 rows=4 loops=1)

2. 0.009 33.318 ↓ 2.0 4 1
→

Unique (cost=617.39..617.41 rows=2 width=53) (actual time=33.309..33.318 rows=4 loops=1)

3. 0.908 33.309 ↓ 2.0 4 1
→

Sort (cost=617.39..617.39 rows=2 width=53) (actual time=33.308..33.309 rows=4 loops=1)

  • Sort Key: buyable_products.start_sale_date, buyable_products.priority_number, buyable_products.id, buyable_products.name
  • Sort Method: quicksort Memory: 64kB
4. 0.578 32.401 ↓ 183.5 367 1
→

Nested Loop (cost=554.14..617.38 rows=2 width=53) (actual time=24.943..32.401 rows=367 loops=1)

5. 0.254 29.965 ↓ 232.2 929 1
→

Nested Loop (cost=554.14..616.05 rows=4 width=61) (actual time=24.918..29.965 rows=929 loops=1)

6. 1.189 26.531 ↓ 70.7 1060 1
→

Hash Join (cost=554.14..611.51 rows=15 width=57) (actual time=24.902..26.531 rows=1060 loops=1)

  • Hash Cond: (channels_products.product_id = buyable_products.id)
7. 0.486 0.486 ↑ 1.0 3143 1
→

Seq Scan on channels_products (cost=0.00..45.43 rows=3143 width=8) (actual time=0.023..0.486 rows=3143 loops=1)

8. 0.453 24.856 ↓ 54.7 656 1
→

Hash (cost=553.99..553.99 rows=12 width=53) (actual time=24.856..24.856 rows=656 loops=1)

  • Buckets: 1024 Batches: 1 Memory Usage: 55kB
9. 0.186 24.403 ↓ 54.7 656 1
→

Subquery Scan on buyable_products (cost=350.15..553.99 rows=12 width=53) (actual time=16.068..24.403 rows=656 loops=1)

10. 1.530 24.217 ↓ 54.7 656 1
→

Hash Join (cost=350.15..553.87 rows=12 width=745) (actual time=16.067..24.217 rows=656 loops=1)

  • Hash Cond: (product_ids.product_id = p.id)
11.          

CTE possible_product_ids
12. 0.635 2.126 ↓ 1.5 1171 1
→

HashAggregate (cost=80.99..89.04 rows=805 width=4) (actual time=1.941..2.126 rows=1171 loops=1)

13. 0.801 1.491 ↓ 1.9 1565 1
→

Hash Join (cost=13.71..78.97 rows=805 width=4) (actual time=0.267..1.491 rows=1565 loops=1)

  • Hash Cond: (cp.channel_id = public.channels.id)
14. 0.450 0.450 ↑ 1.0 3143 1
→

Seq Scan on channels_products cp (cost=0.00..45.43 rows=3143 width=8) (actual time=0.005..0.450 rows=3143 loops=1)

15. 0.018 0.240 ↑ 1.5 42 1
→

Hash (cost=12.92..12.92 rows=63 width=4) (actual time=0.240..0.240 rows=42 loops=1)

  • Buckets: 1024 Batches: 1 Memory Usage: 2kB
16. 0.222 0.222 ↑ 1.5 42 1
→

Seq Scan on channels (cost=0.00..12.92 rows=63 width=4) (actual time=0.030..0.222 rows=42 loops=1)

  • Filter: ((start_at < now()) AND ((end_at IS NULL) OR (end_at > now())))
17.          

CTE future_product_ids
18. 0.030 0.914 ↑ 2.2 41 1
→

HashAggregate (cost=70.11..71.00 rows=89 width=4) (actual time=0.910..0.914 rows=41 loops=1)

19. 0.432 0.884 ↑ 2.2 41 1
→

Hash Join (cost=11.78..69.88 rows=89 width=4) (actual time=0.135..0.884 rows=41 loops=1)

  • Hash Cond: (cp.channel_id = public.channels.id)
20. 0.349 0.349 ↑ 1.0 3143 1
→

Seq Scan on channels_products cp (cost=0.00..45.43 rows=3143 width=8) (actual time=0.006..0.349 rows=3143 loops=1)

21. 0.008 0.103 ↓ 3.7 26 1
→

Hash (cost=11.69..11.69 rows=7 width=4) (actual time=0.103..0.103 rows=26 loops=1)

  • Buckets: 1024 Batches: 1 Memory Usage: 1kB
22. 0.095 0.095 ↓ 3.7 26 1
→

Seq Scan on channels (cost=0.00..11.69 rows=7 width=4) (actual time=0.007..0.095 rows=26 loops=1)

  • Filter: ((front_page IS TRUE) AND (start_at > now()))
23.          

CTE product_ids
24. 0.676 4.529 ↓ 5.7 1131 1
→

HashSetOp Except (cost=0.00..29.05 rows=200 width=4) (actual time=4.305..4.529 rows=1131 loops=1)

25. 0.151 3.853 ↓ 1.4 1212 1
→

Append (cost=0.00..26.82 rows=894 width=4) (actual time=1.945..3.853 rows=1212 loops=1)

26. 0.203 2.761 ↓ 1.5 1171 1
→

Subquery Scan on "*SELECT* 1" (cost=0.00..24.15 rows=805 width=4) (actual time=1.944..2.761 rows=1171 loops=1)

27. 2.558 2.558 ↓ 1.5 1171 1
→

CTE Scan on possible_product_ids (cost=0.00..16.10 rows=805 width=4) (actual time=1.943..2.558 rows=1171 loops=1)

28. 0.014 0.941 ↑ 2.2 41 1
→

Subquery Scan on "*SELECT* 2" (cost=0.00..2.67 rows=89 width=4) (actual time=0.913..0.941 rows=41 loops=1)

29. 0.927 0.927 ↑ 2.2 41 1
→

CTE Scan on future_product_ids (cost=0.00..1.78 rows=89 width=4) (actual time=0.912..0.927 rows=41 loops=1)

30. 5.123 5.123 ↓ 5.7 1131 1
→

CTE Scan on product_ids (cost=0.00..4.00 rows=200 width=4) (actual time=4.308..5.123 rows=1131 loops=1)

31. 4.290 11.660 ↓ 1.9 1891 1
→

Hash (cost=148.38..148.38 rows=1015 width=745) (actual time=11.660..11.660 rows=1891 loops=1)

  • Buckets: 1024 Batches: 1 Memory Usage: 1384kB
32. 7.370 7.370 ↓ 1.9 1891 1
→

Index Scan using products_fake_sold_out_idx2 on products p (cost=0.00..148.38 rows=1015 width=745) (actual time=0.049..7.370 rows=1891 loops=1)

  • Index Cond: (fake_sold_out = false)
  • Filter: ((quantity_in_stock > 0) AND ((start_sale_date IS NULL) OR (start_sale_date < now())))
33.          

SubPlan (forHash Join)
34. 0.656 5.904 ↑ 1.0 1 656
→

Aggregate (cost=16.56..16.57 rows=1 width=8) (actual time=0.009..0.009 rows=1 loops=656)

35. 1.160 5.248 ↑ 1.0 1 656
→

Nested Loop (cost=0.00..16.56 rows=1 width=8) (actual time=0.006..0.008 rows=1 loops=656)

36. 1.968 1.968 ↓ 2.0 2 656
→

Index Scan using channels_products_product_id_idx on channels_products cp (cost=0.00..8.27 rows=1 width=4) (actual time=0.003..0.003 rows=2 loops=656)

  • Index Cond: (product_id = p.id)
37. 2.120 2.120 ↑ 1.0 1 1060
→

Index Scan using channels_pkey on channels (cost=0.00..8.28 rows=1 width=12) (actual time=0.002..0.002 rows=1 loops=1060)

  • Index Cond: (id = cp.channel_id)
  • Filter: ((start_at < now()) AND ((end_at IS NULL) OR (end_at > now())))
38. 3.180 3.180 ↑ 1.0 1 1060
→

Index Scan using channels_pkey on channels (cost=0.00..0.29 rows=1 width=4) (actual time=0.002..0.003 rows=1 loops=1060)

  • Index Cond: (id = channels_products.channel_id)
  • Filter: ((start_at < now()) AND ((end_at IS NULL) OR (end_at > now())))
39. 1.858 1.858 ↓ 0.0 0 929
→

Index Scan using channels_pkey on channels (cost=0.00..0.32 rows=1 width=4) (actual time=0.002..0.002 rows=0 loops=929)

  • Index Cond: (id = channels_products.channel_id)
  • Filter: ((hidden IS FALSE) AND (upsell IS FALSE) AND (site_id = 1))