explain.depesz.com

PostgreSQL's explain analyze made readable

Result: 2jMe

Settings
# exclusive inclusive rows x rows loops node
1. 1.379 500.402 ↓ 2,497.0 2,497 1

Unique (cost=18,164.09..18,164.13 rows=1 width=608) (actual time=498.821..500.402 rows=2,497 loops=1)

2. 5.050 499.023 ↓ 3,372.0 3,372 1

Sort (cost=18,164.09..18,164.10 rows=1 width=608) (actual time=498.820..499.023 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.shareholder_proposals_id, (count(r.companyname) OVER (?))
  • Sort Method: quicksort Memory: 953kB
3. 2.782 493.973 ↓ 3,372.0 3,372 1

WindowAgg (cost=18,164.02..18,164.08 rows=1 width=608) (actual time=492.798..493.973 rows=3,372 loops=1)

4. 1.304 491.191 ↓ 3,372.0 3,372 1

Unique (cost=18,164.02..18,164.06 rows=1 width=608) (actual time=489.589..491.191 rows=3,372 loops=1)

5. 11.815 489.887 ↓ 3,372.0 3,372 1

Sort (cost=18,164.02..18,164.03 rows=1 width=608) (actual time=489.587..489.887 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, r.shareholder_proposals_id
  • Sort Method: quicksort Memory: 953kB
6. 0.921 478.072 ↓ 3,372.0 3,372 1

Subquery Scan on r (cost=18,163.96..18,164.01 rows=1 width=608) (actual time=475.548..478.072 rows=3,372 loops=1)

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

Unique (cost=18,163.96..18,164.00 rows=1 width=644) (actual time=475.542..477.151 rows=3,387 loops=1)

8. 8.688 475.871 ↓ 3,387.0 3,387 1

Sort (cost=18,163.96..18,163.96 rows=1 width=644) (actual time=475.541..475.871 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)), sp.shareholder_proposals_id
  • Sort Method: quicksort Memory: 996kB
9. 8.258 467.183 ↓ 3,387.0 3,387 1

WindowAgg (cost=18,163.90..18,163.95 rows=1 width=644) (actual time=458.428..467.183 rows=3,387 loops=1)

10. 6.057 458.925 ↓ 3,387.0 3,387 1

Sort (cost=18,163.90..18,163.90 rows=1 width=162) (actual time=458.401..458.925 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. 123.861 452.868 ↓ 3,387.0 3,387 1

Nested Loop Left Join (cost=5,236.88..18,163.89 rows=1 width=162) (actual time=41.191..452.868 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. 13.262 295.873 ↓ 33,134.0 33,134 1

Nested Loop Left Join (cost=5,236.60..18,163.51 rows=1 width=154) (actual time=41.139..295.873 rows=33,134 loops=1)

13. 14.349 211.007 ↓ 23,868.0 23,868 1

Nested Loop (cost=5,236.31..18,163.18 rows=1 width=146) (actual time=41.118..211.007 rows=23,868 loops=1)

14. 0.000 101.186 ↓ 23,868.0 23,868 1

Gather (cost=5,235.89..18,162.53 rows=1 width=154) (actual time=41.084..101.186 rows=23,868 loops=1)

  • Workers Planned: 2
  • Workers Launched: 2
15. 188.226 300.165 ↓ 7,956.0 7,956 3

Nested Loop (cost=4,235.89..17,162.43 rows=1 width=154) (actual time=33.497..300.165 rows=7,956 loops=3)

  • Join Filter: ((sp.company_id = fin.company_id) AND (sp.fiscal_year_id = fin.fiscal_year_id))
  • Rows Removed by Join Filter: 56527
16. 14.099 111.919 ↓ 5.6 7,971 3

Parallel Hash Join (cost=4,235.48..15,880.83 rows=1,429 width=111) (actual time=33.317..111.919 rows=7,971 loops=3)

  • Hash Cond: (sp.company_id = mc.company_id)
17. 65.158 65.158 ↓ 5.6 7,971 3

Parallel Seq Scan on shareholder_proposals sp (cost=0.00..11,641.60 rows=1,429 width=48) (actual time=0.048..65.158 rows=7,971 loops=3)

  • 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: 113031
18. 17.517 32.662 ↑ 1.8 40,056 3

Parallel Hash (cost=3,351.88..3,351.88 rows=70,688 width=63) (actual time=32.662..32.662 rows=40,056 loops=3)

  • Buckets: 131072 Batches: 1 Memory Usage: 10016kB
19. 15.145 15.145 ↑ 1.8 40,056 3

Parallel Seq Scan on master_company mc (cost=0.00..3,351.88 rows=70,688 width=63) (actual time=0.026..15.145 rows=40,056 loops=3)

20. 0.020 0.020 ↓ 1.6 8 23,914

Index Scan using financial_idx4 on financial fin (cost=0.42..0.82 rows=5 width=51) (actual time=0.007..0.020 rows=8 loops=23,914)

  • Index Cond: (company_id = mc.company_id)
21. 95.472 95.472 ↑ 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.004..0.004 rows=1 loops=23,868)

  • Index Cond: (fiscal_year_id = fin.fiscal_year_id)
22. 71.604 71.604 ↑ 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.002..0.003 rows=1 loops=23,868)

  • Index Cond: (company_id = mc.company_id)
23. 33.134 33.134 ↓ 0.0 0 33,134

Index Scan using sop_sub_date_idx on sop_sub_date s (cost=0.28..0.33 rows=1 width=16) (actual time=0.001..0.001 rows=0 loops=33,134)

  • Index Cond: (sp.shareholder_proposals_id = shareholder_proposals_id)