explain.depesz.com

PostgreSQL's explain analyze made readable

Result: 9p6e

Settings
# exclusive inclusive rows x rows loops node
1. 51.934 209.778 ↑ 3.3 972 1

HashAggregate (cost=83,812.67..83,901.69 rows=3,237 width=108) (actual time=209.053..209.778 rows=972 loops=1)

  • Group Key: o.customer_id, c.name, c.email
2.          

CTE active_subs

3. 1.220 1.353 ↑ 2.0 97 1

Bitmap Heap Scan on subscription s (cost=74.10..2,855.29 rows=190 width=193) (actual time=0.264..1.353 rows=97 loops=1)

  • Recheck Cond: (store_id = 45979592)
  • Filter: (status = 2)
  • Rows Removed by Filter: 927
  • Heap Blocks: exact=916
4. 0.133 0.133 ↓ 1.4 1,029 1

Bitmap Index Scan on subscription_store_idx (cost=0.00..74.06 rows=733 width=0) (actual time=0.133..0.133 rows=1,029 loops=1)

  • Index Cond: (store_id = 45979592)
5.          

CTE order_details

6. 4.417 92.969 ↑ 1.0 1,480 1

GroupAggregate (cost=43,014.17..43,102.43 rows=1,543 width=41) (actual time=87.884..92.969 rows=1,480 loops=1)

  • Group Key: o_1.customer_id, o_1.is_renewal, ((som.subscription_id IS NULL)), ((g.id IS NOT NULL))
7. 2.701 88.552 ↑ 1.0 3,120 1

Sort (cost=43,014.17..43,022.26 rows=3,237 width=41) (actual time=87.855..88.552 rows=3,120 loops=1)

  • Sort Key: o_1.customer_id, o_1.is_renewal, ((som.subscription_id IS NULL)), ((g.id IS NOT NULL))
  • Sort Method: quicksort Memory: 340kB
8. 4.571 85.851 ↑ 1.0 3,120 1

Nested Loop Left Join (cost=11,457.73..42,825.45 rows=3,237 width=41) (actual time=11.756..85.851 rows=3,120 loops=1)

9. 39.242 71.932 ↑ 1.0 3,116 1

Hash Right Join (cost=11,457.29..15,952.24 rows=3,237 width=33) (actual time=11.735..71.932 rows=3,116 loops=1)

  • Hash Cond: (g.sending_order_id = o_1.id)
10. 27.437 27.437 ↑ 1.0 102,627 1

Seq Scan on gift g (cost=0.00..3,850.27 rows=103,127 width=16) (actual time=0.005..27.437 rows=102,627 loops=1)

11. 1.237 5.253 ↑ 1.0 3,116 1

Hash (cost=11,416.83..11,416.83 rows=3,237 width=25) (actual time=5.253..5.253 rows=3,116 loops=1)

  • Buckets: 1024 Batches: 1 Memory Usage: 195kB
12. 4.016 4.016 ↑ 1.0 3,116 1

Index Scan using ix_order_store_id on "order" o_1 (cost=0.56..11,416.83 rows=3,237 width=25) (actual time=0.018..4.016 rows=3,116 loops=1)

  • Index Cond: (store_id = 45979592)
13. 9.348 9.348 ↑ 1.0 1 3,116

Index Scan using subscription_order_order_idx on subscription_order_map som (cost=0.44..8.29 rows=1 width=16) (actual time=0.003..0.003 rows=1 loops=3,116)

  • Index Cond: (order_id = o_1.id)
14. 13.425 157.844 ↓ 10.3 33,320 1

Hash Right Join (cost=37,713.09..37,749.75 rows=3,237 width=108) (actual time=144.023..157.844 rows=33,320 loops=1)

  • Hash Cond: (order_details.customer_id = c.id)
15. 0.418 0.418 ↑ 1.0 1,480 1

CTE Scan on order_details (cost=0.00..30.86 rows=1,543 width=24) (actual time=0.003..0.418 rows=1,480 loops=1)

16. 4.589 144.001 ↓ 3.2 10,504 1

Hash (cost=37,672.63..37,672.63 rows=3,237 width=100) (actual time=144.001..144.001 rows=10,504 loops=1)

  • Buckets: 1024 Batches: 1 Memory Usage: 1325kB
17. 5.979 139.412 ↓ 3.2 10,504 1

Merge Left Join (cost=37,628.65..37,672.63 rows=3,237 width=100) (actual time=117.802..139.412 rows=10,504 loops=1)

  • Merge Cond: (c.id = order_details_gifts_sent.customer_id)
18. 6.091 132.151 ↓ 3.2 10,504 1

Merge Left Join (cost=37,560.76..37,592.78 rows=3,237 width=84) (actual time=117.677..132.151 rows=10,504 loops=1)

  • Merge Cond: (c.id = order_details_ecom.customer_id)
19. 4.116 123.244 ↓ 1.7 5,564 1

Merge Left Join (cost=37,492.87..37,512.93 rows=3,237 width=76) (actual time=116.906..123.244 rows=5,564 loops=1)

  • Merge Cond: (c.id = order_details_renewals.customer_id)
20. 4.056 24.183 ↓ 1.7 5,564 1

Sort (cost=37,424.99..37,433.08 rows=3,237 width=60) (actual time=22.902..24.183 rows=5,564 loops=1)

  • Sort Key: o.customer_id
  • Sort Method: quicksort Memory: 954kB
21. 2.407 20.127 ↓ 1.7 5,564 1

Hash Left Join (cost=7.17..37,236.26 rows=3,237 width=60) (actual time=1.494..20.127 rows=5,564 loops=1)

  • Hash Cond: (c.id = active_subs.customer_id)
22. 3.148 16.264 ↑ 1.0 3,116 1

Nested Loop (cost=0.99..37,217.94 rows=3,237 width=52) (actual time=0.031..16.264 rows=3,116 loops=1)

23. 3.768 3.768 ↑ 1.0 3,116 1

Index Scan using ix_order_store_id on "order" o (cost=0.56..11,416.83 rows=3,237 width=8) (actual time=0.020..3.768 rows=3,116 loops=1)

  • Index Cond: (store_id = 45979592)
24. 9.348 9.348 ↑ 1.0 1 3,116

Index Scan using customer_pkey on customer c (cost=0.43..7.96 rows=1 width=44) (actual time=0.003..0.003 rows=1 loops=3,116)

  • Index Cond: (id = o.customer_id)
25. 0.042 1.456 ↑ 2.0 97 1

Hash (cost=3.80..3.80 rows=190 width=16) (actual time=1.456..1.456 rows=97 loops=1)

  • Buckets: 1024 Batches: 1 Memory Usage: 5kB
26. 1.414 1.414 ↑ 2.0 97 1

CTE Scan on active_subs (cost=0.00..3.80 rows=190 width=16) (actual time=0.267..1.414 rows=97 loops=1)

27. 1.111 94.945 ↓ 6.4 4,939 1

Sort (cost=67.89..69.82 rows=772 width=24) (actual time=94.000..94.945 rows=4,939 loops=1)

  • Sort Key: order_details_renewals.customer_id
  • Sort Method: quicksort Memory: 58kB
28. 93.834 93.834 ↑ 1.8 435 1

CTE Scan on order_details order_details_renewals (cost=0.00..30.86 rows=772 width=24) (actual time=87.919..93.834 rows=435 loops=1)

  • Filter: is_renewal
  • Rows Removed by Filter: 1045
29. 2.410 2.816 ↓ 13.4 10,342 1

Sort (cost=67.89..69.82 rows=772 width=16) (actual time=0.769..2.816 rows=10,342 loops=1)

  • Sort Key: order_details_ecom.customer_id
  • Sort Method: quicksort Memory: 108kB
30. 0.406 0.406 ↓ 1.6 1,263 1

CTE Scan on order_details order_details_ecom (cost=0.00..30.86 rows=772 width=16) (actual time=0.002..0.406 rows=1,263 loops=1)

  • Filter: (NOT is_subscription_order)
  • Rows Removed by Filter: 217
31. 1.165 1.282 ↓ 8.0 6,140 1

Sort (cost=67.89..69.82 rows=772 width=24) (actual time=0.122..1.282 rows=6,140 loops=1)

  • Sort Key: order_details_gifts_sent.customer_id
  • Sort Method: quicksort Memory: 25kB
32. 0.117 0.117 ↑ 85.8 9 1

CTE Scan on order_details order_details_gifts_sent (cost=0.00..30.86 rows=772 width=24) (actual time=0.002..0.117 rows=9 loops=1)

  • Filter: is_gift_sent
  • Rows Removed by Filter: 1471