explain.depesz.com

PostgreSQL's explain analyze made readable

Result: SUOs

Settings
# exclusive inclusive rows x rows loops node
1. 17.004 2,504.568 ↓ 2.0 495 1

Hash Right Join (cost=229,000.23..230,196.07 rows=251 width=73) (actual time=2,413.948..2,504.568 rows=495 loops=1)

  • Hash Cond: (donation_person_amounts.donor_id = donors.id)
2. 216.284 363.664 ↓ 4.7 235,992 1

HashAggregate (cost=172,149.16..172,651.35 rows=50,219 width=12) (actual time=289.461..363.664 rows=235,992 loops=1)

  • Group Key: donation_person_amounts.donor_id
3. 147.380 147.380 ↓ 1.0 288,419 1

Index Scan using index_donation_person_amounts_on_recipient_id on donation_person_amounts (cost=0.56..170,732.77 rows=283,278 width=12) (actual time=0.014..147.380 rows=288,419 loops=1)

  • Index Cond: (recipient_id = ANY ('{189150,142605,12047671}'::integer[]))
4. 0.366 2,123.900 ↓ 495.0 495 1

Hash (cost=56,851.06..56,851.06 rows=1 width=65) (actual time=2,123.900..2,123.900 rows=495 loops=1)

  • Buckets: 1024 Batches: 1 Memory Usage: 55kB
5. 0.828 2,123.534 ↓ 495.0 495 1

Nested Loop (cost=56,508.08..56,851.06 rows=1 width=65) (actual time=1,099.704..2,123.534 rows=495 loops=1)

6. 0.433 2,120.726 ↓ 495.0 495 1

Hash Anti Join (cost=56,508.08..56,849.34 rows=1 width=66) (actual time=1,099.696..2,120.726 rows=495 loops=1)

  • Hash Cond: (donation_person_amount_election_cycles.donor_id = query_98973.id)
7. 1.379 2,020.290 ↓ 556.0 556 1

Nested Loop (cost=674.42..1,015.66 rows=1 width=70) (actual time=995.626..2,020.290 rows=556 loops=1)

  • Join Filter: (donors.id = donation_person_amount_election_cycles.donor_id)
8. 0.000 2,003.211 ↓ 3,140.0 3,140 1

Nested Loop (cost=673.86..1,013.10 rows=1 width=66) (actual time=994.197..2,003.211 rows=3,140 loops=1)

9. 205.007 1,075.823 ↓ 2,510.6 235,992 1

HashAggregate (cost=673.29..674.23 rows=94 width=4) (actual time=993.215..1,075.823 rows=235,992 loops=1)

  • Group Key: donors.id
10. 111.828 870.816 ↓ 3,068.3 288,419 1

Nested Loop (cost=1.56..673.06 rows=94 width=4) (actual time=0.038..870.816 rows=288,419 loops=1)

11. 31.808 182.150 ↓ 3,068.3 288,419 1

Nested Loop (cost=0.99..595.30 rows=94 width=4) (actual time=0.024..182.150 rows=288,419 loops=1)

12. 0.030 0.030 ↑ 1.0 3 1

Index Scan using index_donation_committees_on_donation_super_committee_id on donation_committees recipients (cost=0.43..18.66 rows=3 width=4) (actual time=0.011..0.030 rows=3 loops=1)

  • Index Cond: (donation_super_committee_id = 69062806)
  • Filter: (election_cycle = ANY ('{2020,2018,2016}'::integer[]))
  • Rows Removed by Filter: 8
13. 150.312 150.312 ↓ 374.1 96,140 3

Index Scan using index_donation_person_amounts_on_recipient_id on donation_person_amounts donation_person_amounts_1 (cost=0.56..189.64 rows=257 width=8) (actual time=0.012..50.104 rows=96,140 loops=3)

  • Index Cond: (recipient_id = recipients.id)
14. 576.838 576.838 ↑ 1.0 1 288,419

Index Only Scan using donation_people_pkey on donation_people donors (cost=0.56..0.82 rows=1 width=4) (actual time=0.002..0.002 rows=1 loops=288,419)

  • Index Cond: (id = donation_person_amounts_1.donor_id)
  • Heap Fetches: 79349
15. 943.968 943.968 ↓ 0.0 0 235,992

Index Scan using donation_people_pkey on donation_people (cost=0.56..3.58 rows=1 width=62) (actual time=0.004..0.004 rows=0 loops=235,992)

  • Index Cond: (id = donors.id)
  • Filter: ((deleted_at IS NULL) AND (mappy_state_id = 9))
  • Rows Removed by Filter: 1
16. 15.700 15.700 ↓ 0.0 0 3,140

Index Scan using index_donation_person_amount_election_cycles_donor_and_election on donation_person_amount_election_cycles (cost=0.56..2.55 rows=1 width=4) (actual time=0.005..0.005 rows=0 loops=3,140)

  • Index Cond: ((donor_id = donation_people.id) AND (election_cycle = 2020))
  • Filter: (transaction_amount >= '100'::double precision)
  • Rows Removed by Filter: 0
17. 10.540 100.003 ↓ 5.3 75,551 1

Hash (cost=55,655.80..55,655.80 rows=14,229 width=4) (actual time=100.003..100.003 rows=75,551 loops=1)

  • Buckets: 131072 (originally 16384) Batches: 1 (originally 1) Memory Usage: 3681kB
18. 6.897 89.463 ↓ 5.3 75,551 1

Subquery Scan on query_98973 (cost=55,371.22..55,655.80 rows=14,229 width=4) (actual time=67.490..89.463 rows=75,551 loops=1)

19. 37.622 82.566 ↓ 5.3 75,551 1

HashAggregate (cost=55,371.22..55,513.51 rows=14,229 width=4) (actual time=67.489..82.566 rows=75,551 loops=1)

  • Group Key: donation_person_amounts_2.donor_id
20. 44.944 44.944 ↑ 1.0 80,217 1

Index Scan using index_donation_person_amounts_on_recipient_id on donation_person_amounts donation_person_amounts_2 (cost=0.56..55,170.57 rows=80,260 width=4) (actual time=0.010..44.944 rows=80,217 loops=1)

  • Index Cond: (recipient_id = ANY ('{145744,12050289,192052}'::integer[]))
21. 1.980 1.980 ↑ 1.0 1 495

Seq Scan on mappy_states (cost=0.00..1.71 rows=1 width=7) (actual time=0.001..0.004 rows=1 loops=495)

  • Filter: (id = 9)
  • Rows Removed by Filter: 56
Planning time : 5.409 ms
Execution time : 2,504.957 ms