explain.depesz.com

PostgreSQL's explain analyze made readable

Result: EJpi

Settings
# exclusive inclusive rows x rows loops node
1. 1.755 602.175 ↓ 0.0 0 1

Insert on single_out (cost=135,321.76..135,417.53 rows=1,277 width=306) (actual time=602.175..602.175 rows=0 loops=1)

2. 0.584 600.420 ↑ 1.1 1,120 1

Subquery Scan on *SELECT* (cost=135,321.76..135,417.53 rows=1,277 width=306) (actual time=561.782..600.420 rows=1,120 loops=1)

3. 8.237 599.836 ↑ 1.1 1,120 1

Unique (cost=135,321.76..135,401.57 rows=1,277 width=346) (actual time=561.779..599.836 rows=1,120 loops=1)

4. 28.111 591.599 ↓ 13.9 17,761 1

WindowAgg (cost=135,321.76..135,376.03 rows=1,277 width=346) (actual time=561.777..591.599 rows=17,761 loops=1)

5. 67.544 562.718 ↓ 13.9 17,761 1

Sort (cost=135,315.71..135,318.91 rows=1,277 width=148) (actual time=560.653..562.718 rows=17,761 loops=1)

  • Sort Key: cc.transaction_amt, cc.state, cc.cmte_nm, cc.transaction_dt, cc.name, cc.transaction_pgi, cc.city, cc.zip_code
  • Sort Method: quicksort Memory: 4831kB
6. 7.139 495.174 ↓ 13.9 17,761 1

Subquery Scan on cc (cost=135,233.87..135,249.83 rows=1,277 width=148) (actual time=483.731..495.174 rows=17,761 loops=1)

7. 1.474 488.035 ↓ 13.9 17,761 1

Limit (cost=135,233.87..135,237.06 rows=1,277 width=12,876) (actual time=483.728..488.035 rows=17,761 loops=1)

8. 21.995 486.561 ↓ 13.9 17,761 1

Sort (cost=135,233.87..135,237.06 rows=1,277 width=12,876) (actual time=483.727..486.561 rows=17,761 loops=1)

  • Sort Key: cc_1.created_dt
  • Sort Method: quicksort Memory: 4639kB
9. 39.927 464.566 ↓ 13.9 17,761 1

Nested Loop Left Join (cost=1.15..131,043.38 rows=1,277 width=12,876) (actual time=2.996..464.566 rows=17,761 loops=1)

10. 15.749 406.878 ↓ 13.9 17,761 1

Nested Loop Left Join (cost=0.87..130,637.32 rows=1,277 width=140) (actual time=2.987..406.878 rows=17,761 loops=1)

11. 7.908 284.563 ↓ 13.9 17,761 1

Nested Loop (cost=0.58..130,244.64 rows=1,277 width=104) (actual time=2.965..284.563 rows=17,761 loops=1)

12. 0.099 0.099 ↓ 1.2 119 1

Function Scan on json_array_elements v1 (cost=0.00..1.00 rows=100 width=32) (actual time=0.062..0.099 rows=119 loops=1)

13. 276.556 276.556 ↓ 8.8 149 119

Index Scan using ix_contributions_fec_contributions_name_state_lw on contributions_fec_contributions cc_1 (cost=0.57..1,302.27 rows=17 width=104) (actual time=0.657..2.324 rows=149 loops=119)

  • Index Cond: (lower((name)::text) = lower((v1.value ->> 0)))
  • Filter: (((transaction_tp IS NULL) OR ((transaction_tp)::text <> '24T'::text)) AND (transaction_dt >= '1970-01-01'::date) AND (transaction_dt <= '2019-06-10'::date) AND ((state IS NULL) OR (lower((state)::text) = lower((v1.value ->> 1)))))
  • Rows Removed by Filter: 573
14. 106.566 106.566 ↑ 1.0 1 17,761

Index Scan using contributions_fec_committees_pkey on contributions_fec_committees cfm (cost=0.29..0.31 rows=1 width=55) (actual time=0.006..0.006 rows=1 loops=17,761)

  • Index Cond: ((cc_1.cmte_id)::text = (cmte_id)::text)
15. 17.761 17.761 ↓ 0.0 0 17,761

Index Scan using contributions_fec_candidates_pkey on contributions_fec_candidates cfc (cost=0.29..0.32 rows=1 width=27) (actual time=0.001..0.001 rows=0 loops=17,761)

  • Index Cond: ((cfm.cand_id)::text = (cand_id)::text)
16.          

SubPlan (forWindowAgg)

17. 0.002 0.016 ↓ 0.0 0 1

ProjectSet (cost=2.25..2.77 rows=100 width=8) (actual time=0.016..0.016 rows=0 loops=1)

18. 0.002 0.014 ↑ 1.0 1 1

Aggregate (cost=2.25..2.26 rows=1 width=32) (actual time=0.014..0.014 rows=1 loops=1)

19. 0.012 0.012 ↓ 0.0 0 1

Function Scan on json_array_elements (cost=0.00..1.00 rows=100 width=32) (actual time=0.012..0.012 rows=0 loops=1)

20. 0.106 0.754 ↓ 11.7 1,166 1

ProjectSet (cost=2.25..2.77 rows=100 width=8) (actual time=0.651..0.754 rows=1,166 loops=1)

21. 0.364 0.648 ↑ 1.0 1 1

Aggregate (cost=2.25..2.26 rows=1 width=32) (actual time=0.648..0.648 rows=1 loops=1)

22. 0.284 0.284 ↓ 11.7 1,166 1

Function Scan on json_array_elements json_array_elements_1 (cost=0.00..1.00 rows=100 width=32) (actual time=0.220..0.284 rows=1,166 loops=1)