explain.depesz.com

PostgreSQL's explain analyze made readable

Result: y6pg

Settings
# exclusive inclusive rows x rows loops node
1. 2.437 2,604.683 ↓ 13.4 2,470 1

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

2. 0.725 2,547.906 ↓ 13.4 2,470 1

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

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

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

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

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

5. 88.496 2,509.829 ↓ 3.0 5,181 1

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

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

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

  • Group Key: donation_person_recipient_parties.donor_id
7. 1,330.330 2,033.357 ↓ 1.4 1,589,631 1

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

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

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

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

  • Sort Key: donation_people.id
  • Sort Method: quicksort Memory: 8010kB
11. 98.059 113.217 ↑ 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.102..113.217 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.158 15.158 ↑ 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.158..15.158 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.009 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.011 0.011 ↑ 1.0 57 1

Seq Scan on mappy_states (cost=0.00..1.57 rows=57 width=7) (actual time=0.004..0.011 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 : 32.668 ms
Execution time : 2,610.513 ms