explain.depesz.com

PostgreSQL's explain analyze made readable

Result: GHZm

Settings
# exclusive inclusive rows x rows loops node
1. 0.074 631.441 ↓ 5.0 5 1

Limit (cost=61,351.68..61,351.76 rows=1 width=353) (actual time=631.342..631.441 rows=5 loops=1)

2.          

CTE current_card_with_address

3. 152.915 474.467 ↑ 1.0 270,587 1

Hash Semi Join (cost=22,223.75..44,596.11 rows=270,587 width=68) (actual time=157.008..474.467 rows=270,587 loops=1)

  • Hash Cond: (cards.id = customers_to_current_card.current_card_id)
4. 134.346 261.169 ↑ 1.0 272,333 1

Hash Join (cost=13,143.51..24,349.73 rows=272,333 width=72) (actual time=96.552..261.169 rows=272,333 loops=1)

  • Hash Cond: (address.card_id = cards.id)
5. 30.437 30.437 ↑ 1.0 272,333 1

Seq Scan on address (cost=0.00..5,171.33 rows=272,333 width=16) (actual time=0.009..30.437 rows=272,333 loops=1)

6. 60.426 96.386 ↑ 1.0 272,334 1

Hash (cost=7,079.34..7,079.34 rows=272,334 width=56) (actual time=96.386..96.386 rows=272,334 loops=1)

  • Buckets: 32768 Batches: 16 Memory Usage: 1753kB
7. 35.960 35.960 ↑ 1.0 272,334 1

Seq Scan on cards (cost=0.00..7,079.34 rows=272,334 width=56) (actual time=0.004..35.960 rows=272,334 loops=1)

8. 36.111 60.383 ↑ 1.0 270,588 1

Hash (cost=4,640.88..4,640.88 rows=270,588 width=4) (actual time=60.383..60.383 rows=270,588 loops=1)

  • Buckets: 65536 Batches: 8 Memory Usage: 1704kB
9. 24.272 24.272 ↑ 1.0 270,588 1

Seq Scan on customers_to_current_card (cost=0.00..4,640.88 rows=270,588 width=4) (actual time=0.008..24.272 rows=270,588 loops=1)

10. 0.100 631.367 ↓ 8.0 8 1

WindowAgg (cost=16,755.49..16,755.57 rows=1 width=353) (actual time=631.319..631.367 rows=8 loops=1)

11. 0.669 631.267 ↓ 64.0 64 1

GroupAggregate (cost=16,755.49..16,755.55 rows=1 width=345) (actual time=630.635..631.267 rows=64 loops=1)

  • Group Key: p.customer_id, cust.hurley_account_id, cust.platform_tenant_code, cd.id, cd.card_type, cd.association, cd.external_card_id, cd.jurisdiction, cd.postal_code, cd.country
12. 0.105 630.598 ↓ 64.0 64 1

Sort (cost=16,755.49..16,755.50 rows=1 width=640) (actual time=630.591..630.598 rows=64 loops=1)

  • Sort Key: p.customer_id, cust.hurley_account_id, cust.platform_tenant_code, cd.id, cd.card_type, cd.association, cd.external_card_id, cd.jurisdiction, cd.postal_code, cd.country
  • Sort Method: quicksort Memory: 58kB
13. 1.370 630.493 ↓ 64.0 64 1

Nested Loop Left Join (cost=10,289.34..16,755.48 rows=1 width=640) (actual time=176.443..630.493 rows=64 loops=1)

  • Join Filter: (du.purchase_id = s.purchase_id)
  • Rows Removed by Join Filter: 25158
14. 0.102 625.155 ↓ 64.0 64 1

Nested Loop (cost=10,289.34..16,715.88 rows=1 width=455) (actual time=176.385..625.155 rows=64 loops=1)

15. 20.082 624.861 ↓ 64.0 64 1

Hash Right Join (cost=10,288.92..16,715.37 rows=1 width=400) (actual time=176.368..624.861 rows=64 loops=1)

  • Hash Cond: (cd.customer_id = p.customer_id)
16. 585.880 585.880 ↑ 1.0 270,587 1

CTE Scan on current_card_with_address cd (cost=0.00..5,411.74 rows=270,587 width=258) (actual time=157.012..585.880 rows=270,587 loops=1)

17. 0.028 18.899 ↓ 64.0 64 1

Hash (cost=10,288.91..10,288.91 rows=1 width=146) (actual time=18.899..18.899 rows=64 loops=1)

  • Buckets: 1024 Batches: 1 Memory Usage: 20kB
18. 0.000 18.871 ↓ 64.0 64 1

Nested Loop (cost=1,000.42..10,288.91 rows=1 width=146) (actual time=0.359..18.871 rows=64 loops=1)

19. 2.659 18.802 ↓ 64.0 64 1

Gather (cost=1,000.00..10,280.47 rows=1 width=56) (actual time=0.346..18.802 rows=64 loops=1)

  • Workers Planned: 2
  • Workers Launched: 2
20. 16.143 16.143 ↓ 21.0 21 3

Parallel Seq Scan on subscriptions s (cost=0.00..9,280.37 rows=1 width=56) (actual time=0.084..16.143 rows=21 loops=3)

  • Filter: (auto_renewal AND ((subscription_status)::text = 'ACTIVE'::text))
  • Rows Removed by Filter: 90114
21. 0.128 0.128 ↑ 1.0 1 64

Index Scan using purchase_log_pkey on purchase_log p (cost=0.42..8.44 rows=1 width=90) (actual time=0.002..0.002 rows=1 loops=64)

  • Index Cond: (id = s.purchase_id)
22. 0.192 0.192 ↑ 1.0 1 64

Index Scan using customers_pkey on customers cust (cost=0.42..0.51 rows=1 width=59) (actual time=0.003..0.003 rows=1 loops=64)

  • Index Cond: (id = p.customer_id)
23. 3.968 3.968 ↑ 1.0 394 64

Seq Scan on decorator_usage du (cost=0.00..34.67 rows=394 width=185) (actual time=0.001..0.062 rows=394 loops=64)

  • Filter: ((status)::text = 'ACTIVE'::text)
  • Rows Removed by Filter: 220
Planning time : 1.052 ms
Execution time : 634.815 ms