explain.depesz.com

PostgreSQL's explain analyze made readable

Result: 6dZX

Settings
# exclusive inclusive rows x rows loops node
1. 2.782 3,350.396 ↓ 13.4 2,470 1

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

2. 0.754 3,266.104 ↓ 13.4 2,470 1

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

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

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

  • Join Filter: (donation_person_recipient_parties.donor_id = donation_people_1.id)
4. 3.853 3,253.396 ↓ 12.0 2,470 1

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

5. 82.743 3,197.733 ↓ 3.0 5,181 1

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

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

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

  • Group Key: donation_person_recipient_parties.donor_id
7. 1,421.552 2,476.742 ↓ 1.4 1,589,631 1

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

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

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

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

  • Sort Key: donation_people.id
  • Sort Method: quicksort Memory: 8010kB
11. 357.869 377.779 ↑ 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=34.020..377.779 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.910 19.910 ↑ 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.910..19.910 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. 51.810 51.810 ↓ 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.009..0.010 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.004..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 81.510 ↑ 1.0 1 2,470

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

  • Group Key: donation_individual_contributions.donor_id
18. 76.570 76.570 ↑ 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.006..0.031 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 : 26.109 ms
Execution time : 3,356.240 ms