explain.depesz.com

PostgreSQL's explain analyze made readable

Result: KmPW

Settings
# exclusive inclusive rows x rows loops node
1. 100.230 8,684.808 ↑ 2.1 11,534 1

GroupAggregate (cost=527,500.93..529,000.25 rows=23,989 width=108) (actual time=8,568.983..8,684.808 rows=11,534 loops=1)

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

CTE active_subs

3. 133.940 135.726 ↑ 1.3 1,759 1

Bitmap Heap Scan on subscription s (cost=875.28..28,919.51 rows=2,289 width=193) (actual time=3.004..135.726 rows=1,759 loops=1)

  • Recheck Cond: (store_id = 63474248)
  • Filter: (status = 2)
  • Rows Removed by Filter: 7083
  • Heap Blocks: exact=7923
4. 1.786 1.786 ↓ 1.0 8,913 1

Bitmap Index Scan on subscription_store_idx (cost=0.00..874.71 rows=8,820 width=0) (actual time=1.786..1.786 rows=8,913 loops=1)

  • Index Cond: (store_id = 63474248)
5.          

CTE order_details

6. 44.123 410.299 ↓ 1.4 15,556 1

GroupAggregate (cost=264,011.60..264,665.65 rows=11,430 width=41) (actual time=359.648..410.299 rows=15,556 loops=1)

  • Group Key: o_1.customer_id, o_1.is_renewal, ((som.subscription_id IS NULL)), ((g.id IS NOT NULL))
7. 23.375 366.176 ↑ 1.1 22,322 1

Sort (cost=264,011.60..264,071.57 rows=23,989 width=41) (actual time=359.615..366.176 rows=22,322 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: 2512kB
8. 30.459 342.801 ↑ 1.1 22,322 1

Hash Left Join (cost=5,798.27..262,266.39 rows=23,989 width=41) (actual time=78.798..342.801 rows=22,322 loops=1)

  • Hash Cond: (o_1.id = g.sending_order_id)
9. 25.193 239.325 ↑ 1.1 22,322 1

Nested Loop Left Join (cost=658.91..256,886.10 rows=23,989 width=33) (actual time=5.733..239.325 rows=22,322 loops=1)

10. 33.347 36.252 ↑ 1.1 22,235 1

Bitmap Heap Scan on "order" o_1 (cost=658.48..79,175.22 rows=23,989 width=25) (actual time=5.704..36.252 rows=22,235 loops=1)

  • Recheck Cond: (store_id = 63474248)
  • Heap Blocks: exact=17408
11. 2.905 2.905 ↑ 1.1 22,727 1

Bitmap Index Scan on ix_order_store_id (cost=0.00..652.48 rows=23,989 width=0) (actual time=2.905..2.905 rows=22,727 loops=1)

  • Index Cond: (store_id = 63474248)
12. 177.880 177.880 ↑ 1.0 1 22,235

Index Scan using subscription_order_order_idx on subscription_order_map som (cost=0.44..7.40 rows=1 width=16) (actual time=0.008..0.008 rows=1 loops=22,235)

  • Index Cond: (order_id = o_1.id)
13. 31.405 73.017 ↑ 1.1 94,012 1

Hash (cost=3,850.27..3,850.27 rows=103,127 width=16) (actual time=73.017..73.017 rows=94,012 loops=1)

  • Buckets: 16384 Batches: 1 Memory Usage: 4407kB
14. 41.612 41.612 ↑ 1.0 102,627 1

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

15. 315.026 8,584.578 ↓ 2.8 66,753 1

Sort (cost=233,915.78..233,975.75 rows=23,989 width=108) (actual time=8,568.920..8,584.578 rows=66,753 loops=1)

  • Sort Key: o.customer_id, c.name, c.email
  • Sort Method: quicksort Memory: 12785kB
16. 32.505 8,269.552 ↓ 2.8 66,753 1

Hash Right Join (cost=231,920.24..232,170.57 rows=23,989 width=108) (actual time=8,234.550..8,269.552 rows=66,753 loops=1)

  • Hash Cond: (order_details_gifts_sent.customer_id = c.id)
17. 2.534 2.534 ↑ 1.4 4,045 1

CTE Scan on order_details order_details_gifts_sent (cost=0.00..228.60 rows=5,715 width=24) (actual time=0.005..2.534 rows=4,045 loops=1)

  • Filter: is_gift_sent
  • Rows Removed by Filter: 11511
18. 33.494 8,234.513 ↓ 2.8 66,729 1

Hash (cost=231,620.38..231,620.38 rows=23,989 width=100) (actual time=8,234.513..8,234.513 rows=66,729 loops=1)

  • Buckets: 4096 Batches: 1 Memory Usage: 8634kB
19. 40.782 8,201.019 ↓ 2.8 66,729 1

Merge Left Join (cost=231,311.21..231,620.38 rows=23,989 width=100) (actual time=8,084.115..8,201.019 rows=66,729 loops=1)

  • Merge Cond: (c.id = order_details_ecom.customer_id)
20. 24.748 8,140.808 ↓ 1.7 40,196 1

Merge Left Join (cost=230,725.98..230,946.31 rows=23,989 width=92) (actual time=8,077.306..8,140.808 rows=40,196 loops=1)

  • Merge Cond: (c.id = order_details_renewals.customer_id)
21. 23.377 8,108.345 ↓ 1.7 40,196 1

Merge Left Join (cost=230,140.74..230,272.24 rows=23,989 width=76) (actual time=8,074.724..8,108.345 rows=40,196 loops=1)

  • Merge Cond: (c.id = active_subs.customer_id)
22. 25.753 7,944.366 ↓ 1.7 40,096 1

Sort (cost=229,967.23..230,027.21 rows=23,989 width=68) (actual time=7,936.451..7,944.366 rows=40,096 loops=1)

  • Sort Key: o.customer_id
  • Sort Method: quicksort Memory: 7175kB
23. 27.359 7,918.613 ↓ 1.7 40,096 1

Hash Right Join (cost=227,949.99..228,222.02 rows=23,989 width=68) (actual time=7,829.277..7,918.613 rows=40,096 loops=1)

  • Hash Cond: (order_details.customer_id = c.id)
24. 421.646 421.646 ↓ 1.4 15,556 1

CTE Scan on order_details (cost=0.00..228.60 rows=11,430 width=24) (actual time=359.653..421.646 rows=15,556 loops=1)

25. 21.993 7,469.608 ↑ 1.1 22,235 1

Hash (cost=227,650.13..227,650.13 rows=23,989 width=52) (actual time=7,469.608..7,469.608 rows=22,235 loops=1)

  • Buckets: 4096 Batches: 1 Memory Usage: 1913kB
26. 44.745 7,447.615 ↑ 1.1 22,235 1

Nested Loop (cost=658.91..227,650.13 rows=23,989 width=52) (actual time=7.800..7,447.615 rows=22,235 loops=1)

27. 83.242 87.555 ↑ 1.1 22,235 1

Bitmap Heap Scan on "order" o (cost=658.48..79,175.22 rows=23,989 width=8) (actual time=7.143..87.555 rows=22,235 loops=1)

  • Recheck Cond: (store_id = 63474248)
  • Heap Blocks: exact=17408
28. 4.313 4.313 ↑ 1.1 22,727 1

Bitmap Index Scan on ix_order_store_id (cost=0.00..652.48 rows=23,989 width=0) (actual time=4.313..4.313 rows=22,727 loops=1)

  • Index Cond: (store_id = 63474248)
29. 7,315.315 7,315.315 ↑ 1.0 1 22,235

Index Scan using customer_pkey on customer c (cost=0.43..6.18 rows=1 width=44) (actual time=0.327..0.329 rows=1 loops=22,235)

  • Index Cond: (id = o.customer_id)
30. 3.270 140.602 ↓ 5.0 11,424 1

Sort (cost=173.51..179.23 rows=2,289 width=16) (actual time=138.267..140.602 rows=11,424 loops=1)

  • Sort Key: active_subs.customer_id
  • Sort Method: quicksort Memory: 131kB
31. 137.332 137.332 ↑ 1.3 1,759 1

CTE Scan on active_subs (cost=0.00..45.78 rows=2,289 width=16) (actual time=3.008..137.332 rows=1,759 loops=1)

32. 5.915 7.715 ↓ 4.6 26,530 1

Sort (cost=585.23..599.52 rows=5,715 width=24) (actual time=2.579..7.715 rows=26,530 loops=1)

  • Sort Key: order_details_renewals.customer_id
  • Sort Method: quicksort Memory: 292kB
33. 1.800 1.800 ↑ 2.3 2,506 1

CTE Scan on order_details order_details_renewals (cost=0.00..228.60 rows=5,715 width=24) (actual time=0.005..1.800 rows=2,506 loops=1)

  • Filter: is_renewal
  • Rows Removed by Filter: 13050
34. 15.777 19.429 ↓ 10.9 62,526 1

Sort (cost=585.23..599.52 rows=5,715 width=16) (actual time=6.805..19.429 rows=62,526 loops=1)

  • Sort Key: order_details_ecom.customer_id
  • Sort Method: quicksort Memory: 883kB
35. 3.652 3.652 ↓ 1.9 10,626 1

CTE Scan on order_details order_details_ecom (cost=0.00..228.60 rows=5,715 width=16) (actual time=0.001..3.652 rows=10,626 loops=1)

  • Filter: (NOT is_subscription_order)
  • Rows Removed by Filter: 4930