explain.depesz.com

PostgreSQL's explain analyze made readable

Result: oh9m

Settings
# exclusive inclusive rows x rows loops node
1. 0.089 14,253.037 ↑ 1.0 100 1

Limit (cost=228,398.30..229,398.48 rows=100 width=97) (actual time=1,560.800..14,253.037 rows=100 loops=1)

2. 1,711.138 14,252.948 ↑ 2.5 100 1

Nested Loop Left Join (cost=228,398.30..230,908.77 rows=251 width=97) (actual time=1,560.800..14,252.948 rows=100 loops=1)

  • Join Filter: (donors.id = donation_person_amounts_2.donor_id)
  • Rows Removed by Join Filter: 23553543
3. 633.519 4,488.510 ↓ 100.0 100 1

Nested Loop Left Join (cost=56,249.14..57,126.86 rows=1 width=89) (actual time=1,249.420..4,488.510 rows=100 loops=1)

  • Join Filter: (donors.id = donation_person_amounts_1.donor_id)
  • Rows Removed by Join Filter: 8688348
  • Filter: ((COALESCE((sum(donation_person_amounts_1.transaction_amount)), '0'::double precision) >= '0'::double precision) AND (COALESCE((sum(donation_person_amounts_1.transaction_amount)), '0'::double precision) <= '0'::double precision))
  • Rows Removed by Filter: 15
4. 0.459 1,278.531 ↓ 115.0 115 1

Nested Loop (cost=677.27..1,021.41 rows=1 width=81) (actual time=1,115.873..1,278.531 rows=115 loops=1)

  • Join Filter: (donors.id = donation_person_amount_election_cycles.donor_id)
5. 1.069 1,275.084 ↓ 498.0 498 1

Nested Loop (cost=676.71..1,018.85 rows=1 width=73) (actual time=1,115.263..1,275.084 rows=498 loops=1)

6. 2.743 1,271.525 ↓ 498.0 498 1

Nested Loop (cost=676.71..1,017.13 rows=1 width=74) (actual time=1,115.253..1,271.525 rows=498 loops=1)

7. 39.652 1,162.840 ↓ 375.7 35,314 1

GroupAggregate (cost=676.14..678.26 rows=94 width=8) (actual time=1,115.223..1,162.840 rows=35,314 loops=1)

  • Group Key: donors.id
  • Filter: (count(DISTINCT recipients.donation_super_committee_id) >= 1)
8. 101.810 1,123.188 ↓ 490.7 46,124 1

Sort (cost=676.14..676.38 rows=94 width=8) (actual time=1,115.202..1,123.188 rows=46,124 loops=1)

  • Sort Key: donors.id
  • Sort Method: quicksort Memory: 21675kB
9. 0.000 1,021.378 ↓ 3,068.3 288,419 1

Nested Loop (cost=1.56..673.06 rows=94 width=8) (actual time=0.053..1,021.378 rows=288,419 loops=1)

10. 36.889 215.666 ↓ 3,068.3 288,419 1

Nested Loop (cost=0.99..595.30 rows=94 width=8) (actual time=0.029..215.666 rows=288,419 loops=1)

11. 0.031 0.031 ↑ 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=8) (actual time=0.011..0.031 rows=3 loops=1)

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

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

  • Index Cond: (recipient_id = recipients.id)
13. 865.257 865.257 ↑ 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.003 rows=1 loops=288,419)

  • Index Cond: (id = donation_person_amounts.donor_id)
  • Heap Fetches: 79349
14. 105.942 105.942 ↓ 0.0 0 35,314

Index Scan using donation_people_pkey on donation_people (cost=0.56..3.58 rows=1 width=62) (actual time=0.003..0.003 rows=0 loops=35,314)

  • Index Cond: (id = donors.id)
  • Filter: ((deleted_at IS NULL) AND (mappy_state_id = 9))
  • Rows Removed by Filter: 1
15. 2.490 2.490 ↑ 1.0 1 498

Seq Scan on mappy_states (cost=0.00..1.71 rows=1 width=7) (actual time=0.002..0.005 rows=1 loops=498)

  • Filter: (id = 9)
  • Rows Removed by Filter: 56
16. 2.988 2.988 ↓ 0.0 0 498

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=12) (actual time=0.006..0.006 rows=0 loops=498)

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

HashAggregate (cost=55,571.87..55,714.16 rows=14,229 width=12) (actual time=0.718..22.404 rows=75,551 loops=115)

  • Group Key: donation_person_amounts_1.donor_id
18. 48.795 48.795 ↑ 1.0 80,217 1

Index Scan using index_donation_person_amounts_on_recipient_id on donation_person_amounts donation_person_amounts_1 (cost=0.56..55,170.57 rows=80,260 width=12) (actual time=0.012..48.795 rows=80,217 loops=1)

  • Index Cond: (recipient_id = ANY ('{145744,12050289,192052}'::integer[]))
19. 7,893.552 8,053.300 ↓ 4.7 235,536 100

HashAggregate (cost=172,149.16..172,651.35 rows=50,219 width=12) (actual time=3.079..80.533 rows=235,536 loops=100)

  • Group Key: donation_person_amounts_2.donor_id
20. 159.748 159.748 ↓ 1.0 288,419 1

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

  • Index Cond: (recipient_id = ANY ('{189150,142605,12047671}'::integer[]))
Planning time : 5.437 ms
Execution time : 14,255.227 ms