explain.depesz.com

PostgreSQL's explain analyze made readable

Result: NEpN

Settings
# exclusive inclusive rows x rows loops node
1. 0.001 13,295.068 ↓ 0.0 0 1

Limit (cost=230,030.41..230,838.51 rows=36 width=97) (actual time=13,295.068..13,295.068 rows=0 loops=1)

2. 1.197 13,295.067 ↓ 0.0 0 1

Hash Anti Join (cost=230,030.41..230,838.51 rows=36 width=97) (actual time=13,295.067..13,295.067 rows=0 loops=1)

  • Hash Cond: (donors.id = query_98974.id)
3. 3,138.959 12,845.761 ↓ 7.8 556 1

Nested Loop Left Join (cost=56,249.14..57,055.72 rows=71 width=89) (actual time=1,207.741..12,845.761 rows=556 loops=1)

  • Join Filter: (donors.id = donation_person_amounts_1.donor_id)
  • Rows Removed by Join Filter: 42006295
4. 1.712 2,171.334 ↓ 556.0 556 1

Nested Loop (cost=677.27..1,021.41 rows=1 width=81) (actual time=1,096.343..2,171.334 rows=556 loops=1)

  • Join Filter: (donors.id = donation_person_amount_election_cycles.donor_id)
5. 3.202 2,153.922 ↓ 3,140.0 3,140 1

Nested Loop (cost=676.71..1,018.85 rows=1 width=73) (actual time=1,095.432..2,153.922 rows=3,140 loops=1)

6. 26.528 2,135.020 ↓ 3,140.0 3,140 1

Nested Loop (cost=676.71..1,017.13 rows=1 width=74) (actual time=1,095.420..2,135.020 rows=3,140 loops=1)

7. 256.903 1,400.516 ↓ 2,510.6 235,992 1

GroupAggregate (cost=676.14..678.26 rows=94 width=8) (actual time=1,095.388..1,400.516 rows=235,992 loops=1)

  • Group Key: donors.id
  • Filter: (count(DISTINCT recipients.donation_super_committee_id) >= 1)
8. 147.433 1,143.613 ↓ 3,068.3 288,419 1

Sort (cost=676.14..676.38 rows=94 width=8) (actual time=1,095.363..1,143.613 rows=288,419 loops=1)

  • Sort Key: donors.id
  • Sort Method: quicksort Memory: 21675kB
9. 210.563 996.180 ↓ 3,068.3 288,419 1

Nested Loop (cost=1.56..673.06 rows=94 width=8) (actual time=0.038..996.180 rows=288,419 loops=1)

10. 38.534 208.779 ↓ 3,068.3 288,419 1

Nested Loop (cost=0.99..595.30 rows=94 width=8) (actual time=0.027..208.779 rows=288,419 loops=1)

11. 0.040 0.040 ↑ 1.0 3 1

Index Scan using index_donation_committees_on_donation_super_committee_id on donation_committees recipients (cost=0.43..18.66 rows=3 width=8) (actual time=0.013..0.040 rows=3 loops=1)

  • Index Cond: (donation_super_committee_id = 69062806)
  • Filter: (election_cycle = ANY ('{2020,2018,2016}'::integer[]))
  • Rows Removed by Filter: 8
12. 170.205 170.205 ↓ 374.1 96,140 3

Index Scan using index_donation_person_amounts_on_recipient_id on donation_person_amounts (cost=0.56..189.64 rows=257 width=8) (actual time=0.015..56.735 rows=96,140 loops=3)

  • Index Cond: (recipient_id = recipients.id)
13. 576.838 576.838 ↑ 1.0 1 288,419

Index Only Scan using donation_people_pkey on donation_people donors (cost=0.56..0.82 rows=1 width=4) (actual time=0.002..0.002 rows=1 loops=288,419)

  • Index Cond: (id = donation_person_amounts.donor_id)
  • Heap Fetches: 79349
14. 707.976 707.976 ↓ 0.0 0 235,992

Index Scan using donation_people_pkey on donation_people (cost=0.56..3.58 rows=1 width=62) (actual time=0.003..0.003 rows=0 loops=235,992)

  • Index Cond: (id = donors.id)
  • Filter: ((deleted_at IS NULL) AND (mappy_state_id = 9))
  • Rows Removed by Filter: 1
15. 15.700 15.700 ↑ 1.0 1 3,140

Seq Scan on mappy_states (cost=0.00..1.71 rows=1 width=7) (actual time=0.001..0.005 rows=1 loops=3,140)

  • Filter: (id = 9)
  • Rows Removed by Filter: 56
16. 15.700 15.700 ↓ 0.0 0 3,140

Index Scan using index_donation_person_amount_election_cycles_donor_and_election on donation_person_amount_election_cycles (cost=0.56..2.55 rows=1 width=12) (actual time=0.005..0.005 rows=0 loops=3,140)

  • Index Cond: ((donor_id = donation_people.id) AND (election_cycle = 2020))
  • Filter: (transaction_amount >= '100'::double precision)
  • Rows Removed by Filter: 0
17. 7,479.254 7,535.468 ↓ 5.3 75,551 556

HashAggregate (cost=55,571.87..55,714.16 rows=14,229 width=12) (actual time=0.175..13.553 rows=75,551 loops=556)

  • Group Key: donation_person_amounts_1.donor_id
18. 56.214 56.214 ↑ 1.0 80,217 1

Index Scan using index_donation_person_amounts_on_recipient_id on donation_person_amounts donation_person_amounts_1 (cost=0.56..55,170.57 rows=80,260 width=12) (actual time=0.014..56.214 rows=80,217 loops=1)

  • Index Cond: (recipient_id = ANY ('{145744,12050289,192052}'::integer[]))
19. 41.669 448.109 ↓ 4.7 235,992 1

Hash (cost=173,153.54..173,153.54 rows=50,219 width=12) (actual time=448.109..448.109 rows=235,992 loops=1)

  • Buckets: 262144 (originally 65536) Batches: 1 (originally 1) Memory Usage: 12189kB
20. 25.405 406.440 ↓ 4.7 235,992 1

Subquery Scan on query_98974 (cost=172,149.16..173,153.54 rows=50,219 width=12) (actual time=299.133..406.440 rows=235,992 loops=1)

21. 224.455 381.035 ↓ 4.7 235,992 1

HashAggregate (cost=172,149.16..172,651.35 rows=50,219 width=12) (actual time=299.131..381.035 rows=235,992 loops=1)

  • Group Key: donation_person_amounts_2.donor_id
22. 156.580 156.580 ↓ 1.0 288,419 1

Index Scan using index_donation_person_amounts_on_recipient_id on donation_person_amounts donation_person_amounts_2 (cost=0.56..170,732.77 rows=283,278 width=12) (actual time=0.022..156.580 rows=288,419 loops=1)

  • Index Cond: (recipient_id = ANY ('{189150,142605,12047671}'::integer[]))
Planning time : 4.359 ms
Execution time : 13,297.700 ms