explain.depesz.com

PostgreSQL's explain analyze made readable

Result: vaCh

Settings
# exclusive inclusive rows x rows loops node
1. 2.903 2,753.158 ↓ 12.0 2,470 1

Nested Loop Left Join (cost=451,115.10..479,699.27 rows=206 width=93) (actual time=1,910.271..2,753.158 rows=2,470 loops=1)

2. 0.717 2,695.915 ↓ 12.0 2,470 1

Hash Left Join (cost=451,114.53..469,108.52 rows=206 width=85) (actual time=1,910.216..2,695.915 rows=2,470 loops=1)

  • Hash Cond: (donation_people_1.mappy_state_id = mappy_states.id)
3. 2.662 2,695.178 ↓ 12.0 2,470 1

Nested Loop (cost=451,112.25..469,103.50 rows=206 width=86) (actual time=1,910.182..2,695.178 rows=2,470 loops=1)

  • Join Filter: (donation_person_recipient_parties.donor_id = donation_people_1.id)
4. 0.724 2,685.106 ↓ 12.0 2,470 1

Nested Loop (cost=451,111.68..468,756.35 rows=206 width=12) (actual time=1,910.167..2,685.106 rows=2,470 loops=1)

5. 83.289 2,658.477 ↓ 3.0 5,181 1

Merge Join (cost=451,111.12..464,481.89 rows=1,714 width=8) (actual time=1,910.133..2,658.477 rows=5,181 loops=1)

  • Merge Cond: (donation_person_recipient_parties.donor_id = donation_people.id)
6. 222.238 2,365.957 ↓ 2.5 1,375,699 1

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

  • Group Key: donation_person_recipient_parties.donor_id
7. 1,422.749 2,143.719 ↓ 1.4 1,589,631 1

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

  • Sort Key: donation_person_recipient_parties.donor_id
  • Sort Method: external merge Disk: 21752kB
8. 536.005 720.970 ↓ 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=207.156..720.970 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
9. 184.965 184.965 ↑ 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=184.965..184.965 rows=2,499,298 loops=1)

  • Index Cond: ((recipient_party_code)::text = 'rep'::text)
10. 83.104 209.231 ↑ 1.0 105,301 1

Sort (cost=146,574.00..146,839.40 rows=106,159 width=4) (actual time=197.462..209.231 rows=105,301 loops=1)

  • Sort Key: donation_people.id
  • Sort Method: quicksort Memory: 8010kB
11. 106.917 126.127 ↑ 1.0 105,343 1

Bitmap Heap Scan on donation_people (cost=1,537.56..137,711.92 rows=106,159 width=4) (actual time=37.121..126.127 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
12. 19.210 19.210 ↑ 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=19.210..19.210 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[]))
13. 25.905 25.905 ↓ 0.0 0 5,181

Index Scan using donation_person_amount_election_cycles_donor_id on donation_person_amount_election_cycles (cost=0.56..2.48 rows=1 width=4) (actual time=0.004..0.005 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
14. 7.410 7.410 ↑ 1.0 1 2,470

Index Scan using donation_people_pkey on donation_people donation_people_1 (cost=0.56..1.67 rows=1 width=82) (actual time=0.003..0.003 rows=1 loops=2,470)

  • Index Cond: (id = donation_person_amount_election_cycles.donor_id)
15. 0.008 0.020 ↑ 1.0 57 1

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

  • Buckets: 1024 Batches: 1 Memory Usage: 11kB
16. 0.012 0.012 ↑ 1.0 57 1

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

17. 4.940 54.340 ↑ 1.0 1 2,470

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

  • Group Key: donation_individual_contributions.donor_id
18. 49.400 49.400 ↑ 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.004..0.020 rows=14 loops=2,470)

  • Index Cond: (donor_id = donation_people.id)
  • Filter: (election_cycle = ANY ('{2016,2018,2020}'::integer[]))
  • Rows Removed by Filter: 9
Planning time : 24.506 ms
Execution time : 2,758.878 ms