explain.depesz.com

A tool for finding a real cause for slow queries.

Result: ht9

options
# exclusive inclusive rows x rows loops node
1. 0.003 72.797 ↑ 1.0 1 1
→

Limit (cost=1706.79..1706.80 rows=1 width=770) (actual time=72.796..72.797 rows=1 loops=1)

2. 0.039 72.794 ↑ 1.0 1 1
→

Sort (cost=1706.79..1706.80 rows=1 width=770) (actual time=72.794..72.794 rows=1 loops=1)

  • Sort Key: p.start_sale_date, p.priority_number, (random())
  • Sort Method: top-N heapsort Memory: 27kB
3. 4.510 72.755 ↓ 6.0 6 1
→

Nested Loop (cost=353.88..1706.78 rows=1 width=770) (actual time=64.911..72.755 rows=6 loops=1)

  • Join Filter: (p.id = channels_products.product_id)
4. 0.031 0.031 ↑ 1.3 6 1
→

Index Scan using channels_products_channel_id_idx on channels_products (cost=0.00..31.00 rows=8 width=4) (actual time=0.012..0.031 rows=6 loops=1)

  • Index Cond: (channel_id = 307)
5. 5.205 68.214 ↓ 49.5 841 6
→

Materialize (cost=353.88..1673.78 rows=17 width=770) (actual time=4.635..11.369 rows=841 loops=6)

6. 4.345 63.009 ↓ 49.5 841 1
→

Nested Loop (cost=353.88..1673.52 rows=17 width=758) (actual time=27.801..63.009 rows=841 loops=1)

7.          

CTE possible_product_ids
8. 2.919 12.115 ↓ 1.4 1669 1
→

HashAggregate (cost=160.56..172.11 rows=1155 width=4) (actual time=10.905..12.115 rows=1669 loops=1)

9. 5.203 9.196 ↓ 1.8 2120 1
→

Hash Join (cost=38.33..157.67 rows=1155 width=4) (actual time=0.358..9.196 rows=2120 loops=1)

  • Hash Cond: (cp.channel_id = public.channels.id)
10. 3.663 3.663 ↓ 1.0 5018 1
→

Seq Scan on channels_products cp (cost=0.00..89.03 rows=5003 width=8) (actual time=0.013..3.663 rows=5018 loops=1)

11. 0.051 0.330 ↑ 1.6 52 1
→

Hash (cost=37.28..37.28 rows=84 width=4) (actual time=0.330..0.330 rows=52 loops=1)

  • Buckets: 1024 Batches: 1 Memory Usage: 2kB
12. 0.279 0.279 ↑ 1.6 52 1
→

Seq Scan on channels (cost=0.00..37.28 rows=84 width=4) (actual time=0.010..0.279 rows=52 loops=1)

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

CTE future_product_ids
14. 0.121 7.205 ↑ 2.1 77 1
→

HashAggregate (cost=137.22..138.87 rows=165 width=4) (actual time=7.150..7.205 rows=77 loops=1)

15. 3.526 7.084 ↑ 2.1 77 1
→

Hash Join (cost=27.37..136.81 rows=165 width=4) (actual time=1.045..7.084 rows=77 loops=1)

  • Hash Cond: (cp.channel_id = public.channels.id)
16. 3.426 3.426 ↓ 1.0 5018 1
→

Seq Scan on channels_products cp (cost=0.00..89.03 rows=5003 width=8) (actual time=0.008..3.426 rows=5018 loops=1)

17. 0.030 0.132 ↓ 2.6 31 1
→

Hash (cost=27.22..27.22 rows=12 width=4) (actual time=0.132..0.132 rows=31 loops=1)

  • Buckets: 1024 Batches: 1 Memory Usage: 2kB
18. 0.102 0.102 ↓ 2.6 31 1
→

Index Scan using channels_front_page_idx on channels (cost=0.00..27.22 rows=12 width=4) (actual time=0.015..0.102 rows=31 loops=1)

  • Index Cond: (front_page = true)
  • Filter: ((front_page IS TRUE) AND (start_at > now()))
19.          

CTE product_ids
20. 2.710 28.983 ↓ 8.0 1592 1
→

HashSetOp Except (cost=0.00..42.90 rows=200 width=4) (actual time=27.740..28.983 rows=1592 loops=1)

21. 2.142 26.273 ↓ 1.3 1746 1
→

Append (cost=0.00..39.60 rows=1320 width=4) (actual time=10.912..26.273 rows=1746 loops=1)

22. 2.169 16.710 ↓ 1.4 1669 1
→

Subquery Scan on "*SELECT* 1" (cost=0.00..34.65 rows=1155 width=4) (actual time=10.910..16.710 rows=1669 loops=1)

23. 14.541 14.541 ↓ 1.4 1669 1
→

CTE Scan on possible_product_ids (cost=0.00..23.10 rows=1155 width=4) (actual time=10.908..14.541 rows=1669 loops=1)

24. 0.105 7.421 ↑ 2.1 77 1
→

Subquery Scan on "*SELECT* 2" (cost=0.00..4.95 rows=165 width=4) (actual time=7.155..7.421 rows=77 loops=1)

25. 7.316 7.316 ↑ 2.1 77 1
→

CTE Scan on future_product_ids (cost=0.00..3.30 rows=165 width=4) (actual time=7.153..7.316 rows=77 loops=1)

26. 31.451 31.451 ↓ 8.0 1592 1
→

CTE Scan on product_ids (cost=0.00..4.00 rows=200 width=4) (actual time=27.742..31.451 rows=1592 loops=1)

27. 9.552 9.552 ↑ 1.0 1 1592
→

Index Scan using products_pkey on products p (cost=0.00..5.16 rows=1 width=758) (actual time=0.005..0.006 rows=1 loops=1592)

  • Index Cond: (id = product_ids.product_id)
  • Filter: ((fake_sold_out IS FALSE) AND (quantity_in_stock > 0) AND ((start_sale_date IS NULL) OR (start_sale_date < now())))
28.          

SubPlan (forNested Loop)
29. 3.364 17.661 ↑ 1.0 1 841
→

Aggregate (cost=16.56..16.57 rows=1 width=8) (actual time=0.020..0.021 rows=1 loops=841)

30. 4.232 14.297 ↑ 1.0 1 841
→

Nested Loop (cost=0.00..16.56 rows=1 width=8) (actual time=0.010..0.017 rows=1 loops=841)

31. 4.205 4.205 ↓ 2.0 2 841
→

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.005 rows=2 loops=841)

  • Index Cond: (product_id = p.id)
32. 5.860 5.860 ↑ 1.0 1 1465
→

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

  • Index Cond: (id = cp.channel_id)
  • Filter: ((start_at < now()) AND ((end_at IS NULL) OR (end_at > now())))