explain.depesz.com

PostgreSQL's explain analyze made readable

Result: QWy2

Settings
# exclusive inclusive rows x rows loops node
1. 4.879 94,403.053 ↓ 1.1 1,905 1

Nested Loop Left Join (cost=443,578.88..1,395,684.43 rows=1,714 width=93) (actual time=84,143.507..94,403.053 rows=1,905 loops=1)

2. 1.164 84,475.029 ↓ 1.1 1,905 1

Hash Left Join (cost=443,578.31..1,307,565.31 rows=1,714 width=85) (actual time=84,115.890..84,475.029 rows=1,905 loops=1)

  • Hash Cond: (donation_people.mappy_state_id = mappy_states.id)
3. 82.377 84,473.845 ↓ 1.1 1,905 1

Hash Join (cost=443,576.02..1,307,540.18 rows=1,714 width=86) (actual time=84,115.851..84,473.845 rows=1,905 loops=1)

  • Hash Cond: (query_98708.id = donation_people.id)
4. 48.842 83,881.662 ↑ 1.4 391,330 1

Subquery Scan on query_98708 (cost=304,537.12..1,151,052.69 rows=557,806 width=4) (actual time=83,604.966..83,881.662 rows=391,330 loops=1)

5. 2,186.451 83,832.820 ↑ 1.4 391,330 1

HashSetOp Intersect (cost=304,537.12..1,145,474.63 rows=557,806 width=4) (actual time=83,604.965..83,832.820 rows=391,330 loops=1)

6. 276.832 81,646.369 ↓ 1.2 3,734,513 1

Append (cost=304,537.12..1,137,989.08 rows=2,994,219 width=4) (actual time=2,276.777..81,646.369 rows=3,734,513 loops=1)

7. 161.734 3,273.417 ↓ 2.5 1,375,699 1

Subquery Scan on *SELECT* 1 (cost=304,537.12..315,965.44 rows=557,806 width=4) (actual time=2,276.777..3,273.417 rows=1,375,699 loops=1)

8. 279.349 3,111.683 ↓ 2.5 1,375,699 1

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

  • Group Key: donation_person_recipient_parties.donor_id
9. 1,680.352 2,832.334 ↓ 1.4 1,589,631 1

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

  • Sort Key: donation_person_recipient_parties.donor_id
  • Sort Method: external merge Disk: 21752kB
10. 909.816 1,151.982 ↓ 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=264.512..1,151.982 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
11. 242.166 242.166 ↑ 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=242.166..242.166 rows=2,499,298 loops=1)

  • Index Cond: ((recipient_party_code)::text = 'rep'::text)
12. 236.515 78,096.120 ↑ 1.0 2,358,814 1

Subquery Scan on *SELECT* 2 (cost=243,529.63..822,023.65 rows=2,436,413 width=4) (actual time=36,394.311..78,096.120 rows=2,358,814 loops=1)

13. 41,552.912 77,859.605 ↑ 1.0 2,358,814 1

Bitmap Heap Scan on donation_person_amount_election_cycles (cost=243,529.63..797,659.52 rows=2,436,413 width=4) (actual time=36,394.311..77,859.605 rows=2,358,814 loops=1)

  • Recheck Cond: (election_cycle = ANY ('{2016,2018,2020}'::integer[]))
  • Filter: (transaction_amount >= '1000'::double precision)
  • Rows Removed by Filter: 12171047
  • Heap Blocks: exact=286272
14. 36,306.693 36,306.693 ↓ 1.0 15,087,435 1

Bitmap Index Scan on donation_person_amount_election_cycles_election_cycle (cost=0.00..242,920.52 rows=14,529,778 width=0) (actual time=36,306.693..36,306.693 rows=15,087,435 loops=1)

  • Index Cond: (election_cycle = ANY ('{2016,2018,2020}'::integer[]))
15. 47.147 509.806 ↑ 1.0 105,343 1

Hash (cost=137,711.92..137,711.92 rows=106,159 width=82) (actual time=509.806..509.806 rows=105,343 loops=1)

  • Buckets: 131072 Batches: 1 Memory Usage: 12268kB
16. 442.978 462.659 ↑ 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=34.604..462.659 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
17. 19.681 19.681 ↑ 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.681..19.681 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[]))
18. 0.011 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
19. 0.009 0.009 ↑ 1.0 57 1

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

20. 7.620 9,923.145 ↑ 1.0 1 1,905

GroupAggregate (cost=0.57..51.39 rows=1 width=12) (actual time=5.209..5.209 rows=1 loops=1,905)

  • Group Key: donation_individual_contributions.donor_id
21. 9,915.525 9,915.525 ↑ 1.4 10 1,905

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.800..5.205 rows=10 loops=1,905)

  • Index Cond: (donor_id = query_98708.id)
  • Filter: (election_cycle = ANY ('{2016,2018,2020}'::integer[]))
  • Rows Removed by Filter: 6
Planning time : 20.529 ms
Execution time : 94,416.866 ms