explain.depesz.com

PostgreSQL's explain analyze made readable

Result: DGvE

Settings
# exclusive inclusive rows x rows loops node
1. 33.932 270.555 ↑ 2.2 191 1

GroupAggregate (cost=12,308.36..25,648.16 rows=418 width=4) (actual time=129.697..270.555 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. 35.378 236.623 ↓ 1.4 185,732 1

Merge Left Join (cost=12,308.36..25,257.38 rows=132,137 width=16) (actual time=129.415..236.623 rows=185,732 loops=1)

  • Merge Cond: (a.id = ac.account_id)
3. 8.200 167.487 ↑ 4.5 9,161 1

Merge Join (cost=8,170.18..14,359.50 rows=41,406 width=16) (actual time=110.219..167.487 rows=9,161 loops=1)

  • Merge Cond: (provider_client.account_id = a.id)
4. 4.535 45.166 ↓ 1.4 10,329 1

Sort (cost=2,828.23..2,831.89 rows=7,311 width=12) (actual time=43.046..45.166 rows=10,329 loops=1)

  • Sort Key: provider_client.account_id
  • Sort Method: quicksort Memory: 1014kB
5. 3.946 40.631 ↓ 1.4 10,329 1

Merge Left Join (cost=2,572.94..2,734.39 rows=7,311 width=12) (actual time=34.737..40.631 rows=10,329 loops=1)

  • Merge Cond: (campaign.id = lc.campaign_id)
6. 4.448 24.529 ↓ 1.4 10,134 1

Sort (cost=1,578.46..1,582.12 rows=7,311 width=8) (actual time=23.338..24.529 rows=10,134 loops=1)

  • Sort Key: campaign.id
  • Sort Method: quicksort Memory: 860kB
7. 1.581 20.081 ↓ 1.4 10,134 1

Nested Loop Left Join (cost=0.12..1,484.62 rows=7,311 width=8) (actual time=0.033..20.081 rows=10,134 loops=1)

8. 3.300 3.300 ↑ 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.022..3.300 rows=1,520 loops=1)

  • Index Cond: (source = 'vk'::dream_source)
  • Filter: (NOT archived)
  • Rows Removed by Filter: 2025
9. 15.200 15.200 ↓ 2.3 7 1,520

Index Scan using idx_campaign on campaign (cost=0.06..0.31 rows=3 width=8) (actual time=0.003..0.010 rows=7 loops=1,520)

  • Index Cond: (provider_client.account_id = account_id)
10. 3.911 12.156 ↓ 1.0 11,517 1

Sort (cost=994.47..1,000.19 rows=11,441 width=12) (actual time=11.393..12.156 rows=11,517 loops=1)

  • Sort Key: lc.campaign_id
  • Sort Method: quicksort Memory: 1207kB
11. 4.485 8.245 ↓ 1.0 11,517 1

Gather (cost=170.95..840.23 rows=11,441 width=12) (actual time=1.776..8.245 rows=11,517 loops=1)

  • Workers Planned: 1
  • Workers Launched: 1
12. 2.596 3.760 ↑ 1.2 5,758 2

Parallel Bitmap Heap Scan on local_campaign lc (cost=70.95..625.82 rows=6,730 width=12) (actual time=0.711..3.760 rows=5,758 loops=2)

  • Recheck Cond: (source = 'vk'::dream_source)
  • Heap Blocks: exact=657
13. 1.164 1.164 ↓ 1.0 11,538 1

Bitmap Index Scan on idx_local_c_row (cost=0.00..70.38 rows=11,441 width=0) (actual time=1.164..1.164 rows=11,538 loops=1)

  • Index Cond: (source = 'vk'::dream_source)
14. 21.743 114.121 ↓ 1.3 86,695 1

Group (cost=5,341.94..6,094.90 rows=66,511 width=1,107) (actual time=67.000..114.121 rows=86,695 loops=1)

  • Group Key: a.id
15. 0.000 92.378 ↓ 1.6 86,695 1

Gather Merge (cost=5,341.94..6,067.19 rows=55,426 width=4) (actual time=66.997..92.378 rows=86,695 loops=1)

  • Workers Planned: 2
  • Workers Launched: 2
16. 17.109 213.816 ↓ 1.0 28,906 3

Group (cost=5,241.94..5,269.65 rows=27,713 width=4) (actual time=63.255..71.272 rows=28,906 loops=3)

  • Group Key: a.id
17. 31.104 196.707 ↓ 1.1 30,738 3

Sort (cost=5,241.94..5,255.80 rows=27,713 width=4) (actual time=63.254..65.569 rows=30,738 loops=3)

  • Sort Key: a.id
  • Sort Method: quicksort Memory: 2273kB
  • Worker 0: Sort Method: quicksort Memory: 2157kB
  • Worker 1: Sort Method: quicksort Memory: 2199kB
18. 14.814 165.603 ↓ 1.1 30,738 3

Merge Anti Join (cost=4,329.76..4,832.94 rows=27,713 width=4) (actual time=47.867..55.201 rows=30,738 loops=3)

  • Merge Cond: (i.id = p.invoice_id)
19. 27.870 138.387 ↑ 1.2 31,847 3

Sort (cost=4,090.75..4,109.23 rows=36,971 width=8) (actual time=43.917..46.129 rows=31,847 loops=3)

  • Sort Key: i.id
  • Sort Method: quicksort Memory: 3096kB
  • Worker 0: Sort Method: quicksort Memory: 2207kB
  • Worker 1: Sort Method: quicksort Memory: 2249kB
20. 32.790 110.517 ↑ 1.2 31,847 3

Merge Left Join (cost=0.12..3,529.75 rows=36,971 width=8) (actual time=0.409..36.839 rows=31,847 loops=3)

  • Merge Cond: (a.id = i.account_id)
21. 55.266 55.266 ↑ 1.2 29,577 3

Parallel Index Only Scan using account_pkey on account a (cost=0.06..2,451.07 rows=36,971 width=4) (actual time=0.024..18.422 rows=29,577 loops=3)

  • Heap Fetches: 70472
22. 22.461 22.461 ↑ 1.0 13,245 3

Index Scan using idx_invoice_account_id on invoice i (cost=0.06..777.16 rows=13,266 width=8) (actual time=0.012..7.487 rows=13,245 loops=3)

23. 3.468 12.402 ↓ 1.0 3,326 3

Sort (cost=239.01..240.67 rows=3,322 width=4) (actual time=3.947..4.134 rows=3,326 loops=3)

  • Sort Key: p.invoice_id
  • Sort Method: quicksort Memory: 253kB
  • Worker 0: Sort Method: quicksort Memory: 253kB
  • Worker 1: Sort Method: quicksort Memory: 253kB
24. 8.934 8.934 ↓ 1.0 3,328 3

Index Scan using dream_idx_for_charge_at on payment p (cost=0.06..200.15 rows=3,322 width=4) (actual time=0.022..2.978 rows=3,328 loops=3)

  • Index Cond: (for_charge > '0'::numeric)
25. 18.608 33.758 ↓ 12.0 196,881 1

Sort (cost=4,138.19..4,146.40 rows=16,419 width=8) (actual time=19.186..33.758 rows=196,881 loops=1)

  • Sort Key: ac.account_id
  • Sort Method: quicksort Memory: 1546kB
26. 15.150 15.150 ↓ 1.0 16,593 1

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

  • Filter: (status <> 'empty'::dream_act_processed)
  • Rows Removed by Filter: 60573
Planning time : 1.349 ms
Execution time : 271.541 ms