explain.depesz.com

PostgreSQL's explain analyze made readable

Result: DccB

Settings
# exclusive inclusive rows x rows loops node
1. 2.919 64,091.581 ↓ 0.0 0 1

Insert on single_out (cost=134,015.75..134,015.83 rows=1 width=306) (actual time=64,091.581..64,091.581 rows=0 loops=1)

2. 0.988 64,088.662 ↓ 1,120.0 1,120 1

Subquery Scan on *SELECT* (cost=134,015.75..134,015.83 rows=1 width=306) (actual time=62,289.318..64,088.662 rows=1,120 loops=1)

3. 8.903 64,087.674 ↓ 1,120.0 1,120 1

Unique (cost=134,015.75..134,015.82 rows=1 width=346) (actual time=62,289.314..64,087.674 rows=1,120 loops=1)

4. 1,788.224 64,078.771 ↓ 17,761.0 17,761 1

WindowAgg (cost=134,015.75..134,015.80 rows=1 width=346) (actual time=62,289.312..64,078.771 rows=17,761 loops=1)

5. 60.136 62,289.619 ↓ 17,761.0 17,761 1

Sort (cost=134,009.70..134,009.71 rows=1 width=148) (actual time=62,287.979..62,289.619 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. 6.622 62,229.483 ↓ 17,761.0 17,761 1

Subquery Scan on cc (cost=134,009.68..134,009.69 rows=1 width=148) (actual time=62,220.591..62,229.483 rows=17,761 loops=1)

7. 38.586 62,222.861 ↓ 17,761.0 17,761 1

Sort (cost=134,009.68..134,009.68 rows=1 width=12,876) (actual time=62,220.589..62,222.861 rows=17,761 loops=1)

  • Sort Key: cc_1.created_dt
  • Sort Method: quicksort Memory: 4639kB
8. 28,387.739 62,184.275 ↓ 17,761.0 17,761 1

Nested Loop Left Join (cost=0.87..134,009.67 rows=1 width=12,876) (actual time=7.225..62,184.275 rows=17,761 loops=1)

  • Join Filter: ((cfm.cand_id)::text = (cfc.cand_id)::text)
  • Rows Removed by Join Filter: 336897107
9. 27.131 4,153.427 ↓ 17,761.0 17,761 1

Nested Loop Left Join (cost=0.87..133,100.95 rows=1 width=140) (actual time=2.978..4,153.427 rows=17,761 loops=1)

10. 13.663 3,966.447 ↓ 17,761.0 17,761 1

Nested Loop (cost=0.58..133,098.64 rows=1 width=104) (actual time=2.959..3,966.447 rows=17,761 loops=1)

11. 0.437 0.437 ↓ 1.2 119 1

Function Scan on json_array_elements v1 (cost=0.01..1.00 rows=100 width=32) (actual time=0.362..0.437 rows=119 loops=1)

12. 3,952.347 3,952.347 ↓ 149.0 149 119

Index Scan using ix_contributions_fec_contributions_name_state_lw on contributions_fec_contributions cc_1 (cost=0.57..1,330.97 rows=1 width=104) (actual time=0.854..33.213 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 >= $3) AND (transaction_dt <= $4) AND ((state IS NULL) OR (lower((state)::text) = lower((v1.value ->> 1)))) AND ((($30 ->> 'employee_city'::text) IS NULL) OR (lower((city)::text) = lower(($30 ->> 'employee_city'::text)))) AND ((($30 ->> 'employee_zip'::text) IS NULL) OR (lower((zip_code)::text) = lower(($30 ->> 'employee_zip'::text)))))
  • Rows Removed by Filter: 573
13. 159.849 159.849 ↑ 1.0 1 17,761

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

  • Index Cond: ((cc_1.cmte_id)::text = (cmte_id)::text)
14. 29,643.109 29,643.109 ↑ 1.2 18,969 17,761

Seq Scan on contributions_fec_candidates cfc (cost=0.00..633.32 rows=22,032 width=27) (actual time=0.002..1.669 rows=18,969 loops=17,761)

15.          

SubPlan (forWindowAgg)

16. 0.001 0.117 ↓ 0.0 0 1

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

17. 0.003 0.116 ↑ 1.0 1 1

Aggregate (cost=2.26..2.27 rows=1 width=32) (actual time=0.116..0.116 rows=1 loops=1)

18. 0.113 0.113 ↓ 0.0 0 1

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

19. 0.099 0.811 ↓ 11.7 1,166 1

ProjectSet (cost=2.26..2.77 rows=100 width=8) (actual time=0.716..0.811 rows=1,166 loops=1)

20. 0.342 0.712 ↑ 1.0 1 1

Aggregate (cost=2.26..2.27 rows=1 width=32) (actual time=0.712..0.712 rows=1 loops=1)

21. 0.370 0.370 ↓ 11.7 1,166 1

Function Scan on json_array_elements json_array_elements_1 (cost=0.01..1.00 rows=100 width=32) (actual time=0.310..0.370 rows=1,166 loops=1)