explain.depesz.com

PostgreSQL's explain analyze made readable

Result: RwjJ

Settings
# exclusive inclusive rows x rows loops node
1. 4.574 4,998.749 ↓ 12.0 2,470 1

Nested Loop Left Join (cost=451,114.54..478,661.89 rows=206 width=93) (actual time=2,591.229..4,998.749 rows=2,470 loops=1)

2. 1.176 4,828.685 ↓ 12.0 2,470 1

Hash Left Join (cost=451,113.97..468,071.14 rows=206 width=85) (actual time=2,591.074..4,828.685 rows=2,470 loops=1)

  • Hash Cond: (donation_people.mappy_state_id = mappy_states.id)
3. 1.486 4,827.488 ↓ 12.0 2,470 1

Nested Loop (cost=451,111.68..468,066.12 rows=206 width=86) (actual time=2,591.041..4,827.488 rows=2,470 loops=1)

4. 92.561 3,411.589 ↓ 3.0 5,181 1

Merge Join (cost=451,111.12..464,481.89 rows=1,714 width=86) (actual time=2,590.701..3,411.589 rows=5,181 loops=1)

  • Merge Cond: (donation_person_recipient_parties.donor_id = donation_people.id)
5. 245.822 2,571.941 ↓ 2.5 1,375,699 1

Group (cost=304,537.12..310,387.38 rows=557,806 width=4) (actual time=1,858.312..2,571.941 rows=1,375,699 loops=1)

  • Group Key: donation_person_recipient_parties.donor_id
6. 1,372.423 2,326.119 ↓ 1.4 1,589,631 1

Sort (cost=304,537.12..307,462.25 rows=1,170,052 width=4) (actual time=1,858.310..2,326.119 rows=1,589,631 loops=1)

  • Sort Key: donation_person_recipient_parties.donor_id
  • Sort Method: external merge Disk: 21752kB
7. 757.723 953.696 ↓ 1.4 1,589,631 1

Bitmap Heap Scan on donation_person_recipient_parties (cost=45,409.49..176,322.00 rows=1,170,052 width=4) (actual time=217.073..953.696 rows=1,589,631 loops=1)

  • Recheck Cond: ((recipient_party_code)::text = 'rep'::text)
  • Filter: (election_cycle = ANY ('{2020,2018,2016}'::integer[]))
  • Rows Removed by Filter: 909667
  • Heap Blocks: exact=85484
8. 195.973 195.973 ↑ 1.0 2,499,298 1

Bitmap Index Scan on index_donation_person_recipient_parties_on_recipient_party_code (cost=0.00..45,116.98 rows=2,499,539 width=0) (actual time=195.973..195.973 rows=2,499,298 loops=1)

  • Index Cond: ((recipient_party_code)::text = 'rep'::text)
9. 65.728 747.087 ↑ 1.0 105,301 1

Sort (cost=146,574.00..146,839.40 rows=106,159 width=82) (actual time=732.240..747.087 rows=105,301 loops=1)

  • Sort Key: donation_people.id
  • Sort Method: quicksort Memory: 17887kB
10. 659.972 681.359 ↑ 1.0 105,343 1

Bitmap Heap Scan on donation_people (cost=1,537.56..137,711.92 rows=106,159 width=82) (actual time=36.108..681.359 rows=105,343 loops=1)

  • Recheck Cond: (((city)::text = ANY ('{Charlotte,CHARLOTTE,"Charlotte ",Cornelius,CORNELIUS,"Cornelius ","Cornelius ","Cornelius ","Cornelius,",Huntersville,HUNTERSVILLE,"Huntersville ","Huntersville,"," Huntersville","HUNTERSVILLE,","Huntersville Nc","Huntersville, Nc",Matthews,MATTHEWS,"Matthews ","Mint Hill","MINT HILL","Mint Hill ","Mint Hill Nc",Pineville,PINEVILLE,"Pineville ","Pineville ","Pineville ",PINEVILLEI,"Pineville "}'::text[])) AND (deleted_at IS NULL))
  • Heap Blocks: exact=74234
11. 21.387 21.387 ↑ 1.0 106,115 1

Bitmap Index Scan on index_donation_people_on_city (cost=0.00..1,511.02 rows=106,159 width=0) (actual time=21.387..21.387 rows=106,115 loops=1)

  • Index Cond: ((city)::text = ANY ('{Charlotte,CHARLOTTE,"Charlotte ",Cornelius,CORNELIUS,"Cornelius ","Cornelius ","Cornelius ","Cornelius,",Huntersville,HUNTERSVILLE,"Huntersville ","Huntersville,"," Huntersville","HUNTERSVILLE,","Huntersville Nc","Huntersville, Nc",Matthews,MATTHEWS,"Matthews ","Mint Hill","MINT HILL","Mint Hill ","Mint Hill Nc",Pineville,PINEVILLE,"Pineville ","Pineville ","Pineville ",PINEVILLEI,"Pineville "}'::text[]))
12. 1,414.413 1,414.413 ↓ 0.0 0 5,181

Index Scan using index_donation_person_amount_election_cycles_donor_and_election on donation_person_amount_election_cycles (cost=0.56..2.08 rows=1 width=4) (actual time=0.271..0.273 rows=0 loops=5,181)

  • Index Cond: (donor_id = donation_person_recipient_parties.donor_id)
  • Filter: ((transaction_amount >= '1000'::double precision) AND (election_cycle = ANY ('{2016,2018,2020}'::integer[])))
  • Rows Removed by Filter: 1
13. 0.007 0.021 ↑ 1.0 57 1

Hash (cost=1.57..1.57 rows=57 width=7) (actual time=0.021..0.021 rows=57 loops=1)

  • Buckets: 1024 Batches: 1 Memory Usage: 11kB
14. 0.014 0.014 ↑ 1.0 57 1

Seq Scan on mappy_states (cost=0.00..1.57 rows=57 width=7) (actual time=0.003..0.014 rows=57 loops=1)

15. 4.940 165.490 ↑ 1.0 1 2,470

GroupAggregate (cost=0.57..51.39 rows=1 width=12) (actual time=0.067..0.067 rows=1 loops=2,470)

  • Group Key: donation_individual_contributions.donor_id
16. 160.550 160.550 ↑ 1.0 14 2,470

Index Scan using index_donation_individual_contributions_on_donor_id on donation_individual_contributions (cost=0.57..51.31 rows=14 width=12) (actual time=0.011..0.065 rows=14 loops=2,470)

  • Index Cond: (donor_id = donation_person_recipient_parties.donor_id)
  • Filter: (election_cycle = ANY ('{2016,2018,2020}'::integer[]))
  • Rows Removed by Filter: 9
Planning time : 26.329 ms
Execution time : 5,005.010 ms