explain.depesz.com

PostgreSQL's explain analyze made readable

Result: hrhd

Settings
# exclusive inclusive rows x rows loops node
1. 0.075 627.624 ↓ 5.0 5 1

Limit (cost=55,905.68..55,905.76 rows=1 width=353) (actual time=627.524..627.624 rows=5 loops=1)

2.          

CTE current_card_with_address

3. 151.724 472.276 ↑ 1.0 270,587 1

Hash Semi Join (cost=21,499.75..43,159.11 rows=270,587 width=68) (actual time=154.763..472.276 rows=270,587 loops=1)

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

Hash Join (cost=12,419.51..22,912.73 rows=272,333 width=72) (actual time=94.525..260.385 rows=272,333 loops=1)

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

Seq Scan on address (cost=0.00..4,458.33 rows=272,333 width=16) (actual time=0.010..32.088 rows=272,333 loops=1)

6. 57.095 94.340 ↑ 1.0 272,334 1

Hash (cost=6,355.34..6,355.34 rows=272,334 width=56) (actual time=94.340..94.340 rows=272,334 loops=1)

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

Seq Scan on cards (cost=0.00..6,355.34 rows=272,334 width=56) (actual time=0.005..37.245 rows=272,334 loops=1)

8. 35.955 60.167 ↑ 1.0 270,588 1

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

  • Buckets: 65536 Batches: 8 Memory Usage: 1704kB
9. 24.212 24.212 ↑ 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.011..24.212 rows=270,588 loops=1)

10. 0.099 627.549 ↓ 8.0 8 1

WindowAgg (cost=12,746.49..12,746.57 rows=1 width=353) (actual time=627.501..627.549 rows=8 loops=1)

11. 0.662 627.450 ↓ 64.0 64 1

GroupAggregate (cost=12,746.49..12,746.55 rows=1 width=345) (actual time=626.823..627.450 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.096 626.788 ↓ 64.0 64 1

Sort (cost=12,746.49..12,746.50 rows=1 width=640) (actual time=626.781..626.788 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.332 626.692 ↓ 64.0 64 1

Nested Loop Left Join (cost=6,280.34..12,746.48 rows=1 width=640) (actual time=172.354..626.692 rows=64 loops=1)

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

Nested Loop (cost=6,280.34..12,706.88 rows=1 width=455) (actual time=172.294..621.328 rows=64 loops=1)

15. 20.276 621.041 ↓ 64.0 64 1

Hash Right Join (cost=6,279.92..12,706.37 rows=1 width=400) (actual time=172.276..621.041 rows=64 loops=1)

  • Hash Cond: (cd.customer_id = p.customer_id)
16. 583.714 583.714 ↑ 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=154.766..583.714 rows=270,587 loops=1)

17. 0.047 17.051 ↓ 64.0 64 1

Hash (cost=6,279.91..6,279.91 rows=1 width=146) (actual time=17.051..17.051 rows=64 loops=1)

  • Buckets: 1024 Batches: 1 Memory Usage: 20kB
18. 0.001 17.004 ↓ 64.0 64 1

Nested Loop (cost=1,000.42..6,279.91 rows=1 width=146) (actual time=0.350..17.004 rows=64 loops=1)

19. 2.314 16.875 ↓ 64.0 64 1

Gather (cost=1,000.00..6,271.47 rows=1 width=56) (actual time=0.339..16.875 rows=64 loops=1)

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

Parallel Seq Scan on subscriptions s (cost=0.00..5,271.37 rows=1 width=56) (actual time=0.424..14.561 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. 4.032 4.032 ↑ 1.0 394 64

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

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