explain.depesz.com

PostgreSQL's explain analyze made readable

Result: F7Dr

Settings
# exclusive inclusive rows x rows loops node
1. 1.584 2,624.979 ↓ 13.4 2,470 1

Nested Loop Left Join (cost=451,115.10..478,567.92 rows=184 width=93) (actual time=1,755.292..2,624.979 rows=2,470 loops=1)

2. 0.711 2,566.585 ↓ 13.4 2,470 1

Hash Left Join (cost=451,114.53..469,108.23 rows=184 width=85) (actual time=1,755.240..2,566.585 rows=2,470 loops=1)

  • Hash Cond: (donation_people_1.mappy_state_id = mappy_states.id)
3. 0.599 2,565.854 ↓ 13.4 2,470 1

Nested Loop (cost=451,112.25..469,103.50 rows=184 width=86) (actual time=1,755.206..2,565.854 rows=2,470 loops=1)

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

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

5. 82.849 2,528.002 ↓ 3.0 5,181 1

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

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

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

  • Group Key: donation_person_recipient_parties.donor_id
7. 1,373.294 2,069.488 ↓ 1.4 1,589,631 1

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

  • Sort Key: donation_person_recipient_parties.donor_id
  • Sort Method: external merge Disk: 21752kB
8. 526.177 696.194 ↓ 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=191.222..696.194 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. 170.017 170.017 ↑ 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=170.017..170.017 rows=2,499,298 loops=1)

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

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

  • Sort Key: donation_people.id
  • Sort Method: quicksort Memory: 8010kB
11. 100.894 116.055 ↑ 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=29.631..116.055 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. 15.161 15.161 ↑ 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=15.161..15.161 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. 9.880 9.880 ↑ 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.004 rows=1 loops=2,470)

  • Index Cond: (id = donation_person_amount_election_cycles.donor_id)
  • Filter: (deleted_at IS NULL)
15. 0.010 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.010 0.010 ↑ 1.0 57 1

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

17. 4.940 56.810 ↑ 1.0 1 2,470

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

  • Group Key: donation_individual_contributions.donor_id
18. 51.870 51.870 ↑ 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.021 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.632 ms
Execution time : 2,630.710 ms