explain.depesz.com

PostgreSQL's explain analyze made readable

Result: Dycd

Settings
# exclusive inclusive rows x rows loops node
1. 4,264.711 70,590.398 ↑ 2.2 191 1

GroupAggregate (cost=8,491.97..8,632.18 rows=418 width=4) (actual time=62,257.138..70,590.398 rows=191 loops=1)

  • Group Key: provider_client.account_id
  • Filter: (bool_and(((lc.status)::text = ANY ('{deleted,autoarchived}'::text[]))) AND (max(ac.period) < '2019-01-01'::date))
  • Rows Removed by Filter: 890
2. 10,099.085 66,325.687 ↓ 4,090.2 22,414,393 1

Sort (cost=8,491.97..8,494.71 rows=5,480 width=16) (actual time=62,256.993..66,325.687 rows=22,414,393 loops=1)

  • Sort Key: provider_client.account_id
  • Sort Method: external merge Disk: 466888kB
3. 4,447.241 56,226.602 ↓ 4,090.2 22,414,393 1

Merge Anti Join (cost=8,321.74..8,423.91 rows=5,480 width=16) (actual time=47,744.129..56,226.602 rows=22,414,393 loops=1)

  • Merge Cond: (i.id = p.invoice_id)
4. 15,722.248 51,775.109 ↓ 3,091.0 22,598,323 1

Sort (cost=8,082.73..8,086.38 rows=7,311 width=20) (actual time=47,740.608..51,775.109 rows=22,598,323 loops=1)

  • Sort Key: i.id
  • Sort Method: external merge Disk: 559752kB
5. 5,087.547 36,052.861 ↓ 3,091.0 22,598,323 1

Merge Left Join (cost=7,828.94..7,988.88 rows=7,311 width=20) (actual time=27,255.702..36,052.861 rows=22,598,323 loops=1)

  • Merge Cond: (campaign.id = lc.campaign_id)
6. 13,760.246 30,608.844 ↓ 3,075.6 22,485,815 1

Sort (cost=6,841.26..6,844.91 rows=7,311 width=16) (actual time=27,244.438..30,608.844 rows=22,485,815 loops=1)

  • Sort Key: campaign.id
  • Sort Method: external merge Disk: 572184kB
7. 3,496.381 16,848.598 ↓ 3,075.6 22,485,815 1

Nested Loop Left Join (cost=839.78..6,747.42 rows=7,311 width=16) (actual time=8.878..16,848.598 rows=22,485,815 loops=1)

8. 12.127 113.241 ↓ 23.2 62,448 1

Nested Loop Left Join (cost=839.72..5,506.39 rows=2,686 width=12) (actual time=8.869..113.241 rows=62,448 loops=1)

9. 7.117 33.431 ↓ 2.3 6,153 1

Hash Right Join (cost=839.67..4,785.10 rows=2,686 width=12) (actual time=8.862..33.431 rows=6,153 loops=1)

  • Hash Cond: (ac.account_id = account.id)
10. 17.474 17.474 ↓ 1.0 16,593 1

Seq Scan on acts ac (cost=0.00..3,908.27 rows=16,419 width=8) (actual time=0.009..17.474 rows=16,593 loops=1)

  • Filter: (status <> 'empty'::dream_act_processed)
  • Rows Removed by Filter: 60573
11. 0.471 8.840 ↑ 1.8 1,520 1

Hash (cost=704.02..704.02 rows=2,686 width=8) (actual time=8.839..8.840 rows=1,520 loops=1)

  • Buckets: 4096 Batches: 1 Memory Usage: 92kB
12. 0.357 8.369 ↑ 1.8 1,520 1

Nested Loop (cost=0.12..704.02 rows=2,686 width=8) (actual time=0.039..8.369 rows=1,520 loops=1)

13. 3.452 3.452 ↑ 1.8 1,520 1

Index Scan using idx_provider_client_id on provider_client (cost=0.06..243.60 rows=2,686 width=4) (actual time=0.020..3.452 rows=1,520 loops=1)

  • Index Cond: (source = 'vk'::dream_source)
  • Filter: (NOT archived)
  • Rows Removed by Filter: 2025
14. 4.560 4.560 ↑ 1.0 1 1,520

Index Only Scan using account_pkey on account (cost=0.06..0.17 rows=1 width=4) (actual time=0.003..0.003 rows=1 loops=1,520)

  • Index Cond: (id = provider_client.account_id)
  • Heap Fetches: 1520
15. 67.683 67.683 ↓ 5.0 10 6,153

Index Scan using idx_invoice_account_id on invoice i (cost=0.06..0.17 rows=2 width=8) (actual time=0.003..0.011 rows=10 loops=6,153)

  • Index Cond: (account.id = account_id)
16. 13,238.976 13,238.976 ↓ 120.0 360 62,448

Index Scan using idx_campaign on campaign (cost=0.06..0.31 rows=3 width=8) (actual time=0.004..0.212 rows=360 loops=62,448)

  • Index Cond: (provider_client.account_id = account_id)
17. 348.238 356.470 ↓ 476.6 5,399,630 1

Sort (cost=987.68..993.35 rows=11,330 width=12) (actual time=11.255..356.470 rows=5,399,630 loops=1)

  • Sort Key: lc.campaign_id
  • Sort Method: quicksort Memory: 1207kB
18. 4.497 8.232 ↓ 1.0 11,517 1

Gather (cost=170.23..835.09 rows=11,330 width=12) (actual time=1.802..8.232 rows=11,517 loops=1)

  • Workers Planned: 1
  • Workers Launched: 1
19. 2.573 3.735 ↑ 1.2 5,758 2

Parallel Bitmap Heap Scan on local_campaign lc (cost=70.23..621.79 rows=6,665 width=12) (actual time=0.703..3.735 rows=5,758 loops=2)

  • Recheck Cond: (source = 'vk'::dream_source)
  • Heap Blocks: exact=611
20. 1.162 1.162 ↓ 1.0 11,536 1

Bitmap Index Scan on idx_local_c_row (cost=0.00..69.66 rows=11,330 width=0) (actual time=1.162..1.162 rows=11,536 loops=1)

  • Index Cond: (source = 'vk'::dream_source)
21. 1.626 4.252 ↓ 1.0 3,326 1

Sort (cost=239.01..240.67 rows=3,322 width=4) (actual time=3.510..4.252 rows=3,326 loops=1)

  • Sort Key: p.invoice_id
  • Sort Method: quicksort Memory: 252kB
22. 2.626 2.626 ↓ 1.0 3,326 1

Index Scan using dream_idx_for_charge_at on payment p (cost=0.06..200.15 rows=3,322 width=4) (actual time=0.041..2.626 rows=3,326 loops=1)

  • Index Cond: (for_charge > '0'::numeric)
Planning time : 3.185 ms
Execution time : 70,861.191 ms