explain.depesz.com

PostgreSQL's explain analyze made readable

Result: vXlx

Settings
# exclusive inclusive rows x rows loops node
1. 2.315 12,194.528 ↓ 2,497.0 2,497 1

Unique (cost=26,452.23..26,452.26 rows=1 width=600) (actual time=12,191.906..12,194.528 rows=2,497 loops=1)

2. 6.574 12,192.213 ↓ 3,372.0 3,372 1

Sort (cost=26,452.23..26,452.23 rows=1 width=600) (actual time=12,191.904..12,192.213 rows=3,372 loops=1)

  • Sort Key: r.companyname, r.company_id, r.ticker, r.yes_votes, r.no_votes, r.abstain_votes, r.shareholdermeetingdate, r.revenues, r.assets, r.marketcap, r.shares_out_fy, (count(r.companyname) OVER (?))
  • Sort Method: quicksort Memory: 836kB
3. 4.115 12,185.639 ↓ 3,372.0 3,372 1

WindowAgg (cost=26,452.16..26,452.22 rows=1 width=600) (actual time=12,183.969..12,185.639 rows=3,372 loops=1)

4. 1.979 12,181.524 ↓ 3,372.0 3,372 1

Unique (cost=26,452.16..26,452.19 rows=1 width=600) (actual time=12,179.050..12,181.524 rows=3,372 loops=1)

5. 23.295 12,179.545 ↓ 3,372.0 3,372 1

Sort (cost=26,452.16..26,452.16 rows=1 width=600) (actual time=12,179.047..12,179.545 rows=3,372 loops=1)

  • Sort Key: r.companyname, r.company_id, r.ticker, r.yes_votes, r.no_votes, r.abstain_votes, r.shareholdermeetingdate, r.revenues, r.assets, r.marketcap, r.shares_out_fy, r.sector_id
  • Sort Method: quicksort Memory: 835kB
6. 1.776 12,156.250 ↓ 3,372.0 3,372 1

Subquery Scan on r (cost=26,452.10..26,452.15 rows=1 width=600) (actual time=12,151.285..12,156.250 rows=3,372 loops=1)

  • Filter: (r.rank = 1)
  • Rows Removed by Filter: 15
7. 2.485 12,154.474 ↓ 3,387.0 3,387 1

Unique (cost=26,452.10..26,452.14 rows=1 width=636) (actual time=12,151.274..12,154.474 rows=3,387 loops=1)

8. 16.219 12,151.989 ↓ 3,387.0 3,387 1

Sort (cost=26,452.10..26,452.10 rows=1 width=636) (actual time=12,151.271..12,151.989 rows=3,387 loops=1)

  • Sort Key: (CASE COALESCE((fin.share_holder_meeting_date)::timestamp with time zone, to_timestamp('01/01/0001'::text, 'mm/dd/yyyy'::text)) WHEN to_timestamp('01/01/0001'::text, 'mm/dd/yyyy'::text) THEN COALESCE((s.submission_date)::timestamp with time zone, to_timestamp('01/01/0001'::text, 'mm/dd/yyyy'::text)) ELSE (fin.share_holder_meeting_date)::timestamp with time zone END) DESC NULLS LAST, (rank() OVER (?)), fin.fy_order, (COALESCE(mc.aka_co_name, mc.company_name)), mc.ticker, (CASE COALESCE(sp.yes_votes, '0'::bigint) WHEN '-999999999'::integer THEN '0'::bigint ELSE sp.yes_votes END), (CASE COALESCE(sp.no_votes, '0'::bigint) WHEN '-999999999'::integer THEN '0'::bigint ELSE sp.no_votes END), (CASE COALESCE(sp.abstain_votes, '0'::bigint) WHEN '-999999999'::integer THEN '0'::bigint ELSE sp.abstain_votes END), mc.company_id, mcs.sector_id, fin.revenues, fin.total_assets, finc.market_cap_fy, (COALESCE(fin.sh_out, '-999999999'::bigint))
  • Sort Method: quicksort Memory: 996kB
9. 17.185 12,135.770 ↓ 3,387.0 3,387 1

WindowAgg (cost=26,452.04..26,452.09 rows=1 width=636) (actual time=12,117.608..12,135.770 rows=3,387 loops=1)

10. 14.285 12,118.585 ↓ 3,387.0 3,387 1

Sort (cost=26,452.04..26,452.04 rows=1 width=154) (actual time=12,117.539..12,118.585 rows=3,387 loops=1)

  • Sort Key: fin.company_id, fin.share_holder_meeting_date DESC, s.submission_date DESC NULLS LAST
  • Sort Method: quicksort Memory: 996kB
11. 405.837 12,104.300 ↓ 3,387.0 3,387 1

Nested Loop Left Join (cost=5,351.22..26,452.03 rows=1 width=154) (actual time=137.441..12,104.300 rows=3,387 loops=1)

  • Filter: (CASE COALESCE((fin.share_holder_meeting_date)::timestamp with time zone, to_timestamp('01/01/0001'::text, 'mm/dd/yyyy'::text)) WHEN to_timestamp('01/01/0001'::text, 'mm/dd/yyyy'::text) THEN COALESCE((s.submission_date)::timestamp with time zone, to_timestamp('01/01/0001'::text, 'mm/dd/yyyy'::text)) ELSE (fin.share_holder_meeting_date)::timestamp with time zone END >= (to_timestamp(((CURRENT_DATE - '1 year'::interval))::text, 'YYYY-MM-DD HH24:MI:SS'::text))::timestamp without time zone)
  • Rows Removed by Filter: 29747
12. 204.136 11,135.185 ↓ 33,134.0 33,134 1

Nested Loop Left Join (cost=5,350.80..26,451.53 rows=1 width=154) (actual time=137.230..11,135.185 rows=33,134 loops=1)

13. 63.010 2,581.281 ↓ 33,134.0 33,134 1

Nested Loop Left Join (cost=5,350.35..26,448.63 rows=1 width=173) (actual time=137.037..2,581.281 rows=33,134 loops=1)

14. 66.676 2,087.529 ↓ 33,134.0 33,134 1

Nested Loop Left Join (cost=5,349.93..26,443.73 rows=1 width=154) (actual time=136.936..2,087.529 rows=33,134 loops=1)

15. 75.593 1,758.305 ↓ 23,868.0 23,868 1

Nested Loop (cost=5,349.64..26,443.40 rows=1 width=146) (actual time=127.359..1,758.305 rows=23,868 loops=1)

16. 153.090 1,205.352 ↓ 23,868.0 23,868 1

Nested Loop (cost=5,349.22..26,442.75 rows=1 width=154) (actual time=127.297..1,205.352 rows=23,868 loops=1)

  • Join Filter: ((sp.company_id = fin.company_id) AND (sp.fiscal_year_id = fin.fiscal_year_id))
  • Rows Removed by Join Filter: 74811
17. 62.855 526.154 ↓ 7.0 23,914 1

Hash Join (cost=5,348.80..23,352.05 rows=3,430 width=111) (actual time=127.167..526.154 rows=23,914 loops=1)

  • Hash Cond: (sp.company_id = mc.company_id)
18. 337.020 337.020 ↓ 7.0 23,914 1

Seq Scan on shareholder_proposals sp (cost=0.00..17,994.24 rows=3,430 width=48) (actual time=0.032..337.020 rows=23,914 loops=1)

  • Filter: ((sub_category = 7) AND (sh_proposal_type = 9) AND (((CASE COALESCE(yes_votes, '0'::bigint) WHEN '-999999999'::integer THEN '0'::bigint ELSE COALESCE(yes_votes, '0'::bigint) END + CASE COALESCE(no_votes, '0'::bigint) WHEN '-999999999'::integer THEN '0'::bigint ELSE COALESCE(no_votes, '0'::bigint) END) + CASE COALESCE(abstain_votes, '0'::bigint) WHEN '-999999999'::integer THEN '0'::bigint ELSE COALESCE(abstain_votes, '0'::bigint) END) <> 0))
  • Rows Removed by Filter: 339094
19. 60.517 126.279 ↑ 1.0 120,169 1

Hash (cost=3,846.69..3,846.69 rows=120,169 width=63) (actual time=126.279..126.279 rows=120,169 loops=1)

  • Buckets: 131072 Batches: 1 Memory Usage: 9955kB
20. 65.762 65.762 ↑ 1.0 120,169 1

Seq Scan on master_company mc (cost=0.00..3,846.69 rows=120,169 width=63) (actual time=0.034..65.762 rows=120,169 loops=1)

21. 526.108 526.108 ↑ 1.2 4 23,914

Index Scan using financial_idx1 on financial fin (cost=0.42..0.83 rows=5 width=51) (actual time=0.012..0.022 rows=4 loops=23,914)

  • Index Cond: (company_id = mc.company_id)
22. 477.360 477.360 ↑ 1.0 1 23,868

Index Scan using financial_calc_pkey on financial_calc finc (cost=0.42..0.65 rows=1 width=16) (actual time=0.020..0.020 rows=1 loops=23,868)

  • Index Cond: (fiscal_year_id = fin.fiscal_year_id)
23. 262.548 262.548 ↑ 1.0 1 23,868

Index Scan using master_company_sector_idx1 on master_company_sector mcs (cost=0.29..0.32 rows=1 width=16) (actual time=0.009..0.011 rows=1 loops=23,868)

  • Index Cond: (company_id = mc.company_id)
24. 430.742 430.742 ↑ 1.0 1 33,134

Index Scan using shareholder_proposals_ddown_pkey on shareholder_proposals_ddown spd (cost=0.42..4.90 rows=1 width=35) (actual time=0.013..0.013 rows=1 loops=33,134)

  • Index Cond: (sp.shareholder_proposals_id = shareholder_proposals_id)
25. 8,349.768 8,349.768 ↑ 1.0 1 33,134

Index Scan using document_pkey on document doc (cost=0.45..2.90 rows=1 width=16) (actual time=0.252..0.252 rows=1 loops=33,134)

  • Index Cond: ((substr((spd.yes_votes)::text, 1, (instr((spd.yes_votes)::text, ','::text) - 1)))::bigint = document_id)
26. 563.278 563.278 ↑ 1.0 1 33,134

Index Scan using submission_pkey on submission s (cost=0.43..0.45 rows=1 width=16) (actual time=0.017..0.017 rows=1 loops=33,134)

  • Index Cond: (doc.submission_id = submission_id)
Planning time : 33.130 ms
Execution time : 12,195.589 ms