explain.depesz.com

PostgreSQL's explain analyze made readable

Result: rmlf

Settings
# exclusive inclusive rows x rows loops node
1. 6.072 414.110 ↓ 4,234.0 4,234 1

Unique (cost=14,201.75..14,201.78 rows=1 width=608) (actual time=407.313..414.110 rows=4,234 loops=1)

2. 17.958 408.038 ↓ 5,759.0 5,759 1

Sort (cost=14,201.75..14,201.75 rows=1 width=608) (actual time=407.309..408.038 rows=5,759 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: 1655kB
3. 8.745 390.080 ↓ 5,759.0 5,759 1

WindowAgg (cost=14,201.68..14,201.74 rows=1 width=608) (actual time=385.705..390.080 rows=5,759 loops=1)

4. 4.367 381.335 ↓ 5,759.0 5,759 1

Unique (cost=14,201.68..14,201.71 rows=1 width=608) (actual time=376.082..381.335 rows=5,759 loops=1)

5. 24.515 376.968 ↓ 5,759.0 5,759 1

Sort (cost=14,201.68..14,201.68 rows=1 width=608) (actual time=376.080..376.968 rows=5,759 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: 1655kB
6. 1.701 352.453 ↓ 5,759.0 5,759 1

Subquery Scan on r (cost=14,201.61..14,201.67 rows=1 width=608) (actual time=344.939..352.453 rows=5,759 loops=1)

  • Filter: (r.rank = 1)
7. 4.811 350.752 ↓ 5,759.0 5,759 1

Unique (cost=14,201.61..14,201.65 rows=1 width=644) (actual time=344.932..350.752 rows=5,759 loops=1)

8. 43.658 345.941 ↓ 9,422.0 9,422 1

Sort (cost=14,201.61..14,201.62 rows=1 width=644) (actual time=344.930..345.941 rows=9,422 loops=1)

  • Sort Key: s.share_holder_meeting_date 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: 2887kB
9. 14.234 302.283 ↓ 9,422.0 9,422 1

WindowAgg (cost=14,201.57..14,201.60 rows=1 width=644) (actual time=286.868..302.283 rows=9,422 loops=1)

10. 14.267 288.049 ↓ 9,422.0 9,422 1

Sort (cost=14,201.57..14,201.57 rows=1 width=170) (actual time=286.832..288.049 rows=9,422 loops=1)

  • Sort Key: fin.company_id, fin.share_holder_meeting_date DESC, s.submission_date DESC NULLS LAST
  • Sort Method: quicksort Memory: 2887kB
11. 2.081 273.782 ↓ 9,422.0 9,422 1

Nested Loop Left Join (cost=2,395.69..14,201.56 rows=1 width=170) (actual time=139.594..273.782 rows=9,422 loops=1)

12. 2.075 250.884 ↓ 6,939.0 6,939 1

Nested Loop (cost=2,395.40..14,201.23 rows=1 width=162) (actual time=139.570..250.884 rows=6,939 loops=1)

13. 0.000 227.992 ↓ 6,939.0 6,939 1

Gather (cost=2,394.98..14,200.58 rows=1 width=170) (actual time=139.535..227.992 rows=6,939 loops=1)

  • Workers Planned: 2
  • Workers Launched: 2
14. 21.579 228.158 ↓ 2,313.0 2,313 3

Nested Loop (cost=1,394.98..13,200.48 rows=1 width=170) (actual time=118.533..228.158 rows=2,313 loops=3)

  • Join Filter: ((sp.company_id = fin.company_id) AND (sp.fiscal_year_id = fin.fiscal_year_id))
  • Rows Removed by Join Filter: 67
15. 28.443 206.571 ↓ 82.6 2,313 3

Nested Loop (cost=1,394.56..13,175.25 rows=28 width=127) (actual time=118.459..206.571 rows=2,313 loops=3)

16. 4.434 178.117 ↓ 82.6 2,313 3

Hash Join (cost=1,394.14..13,084.24 rows=28 width=64) (actual time=118.361..178.117 rows=2,313 loops=3)

  • Hash Cond: (sp.shareholder_proposals_id = s.shareholder_proposals_id)
17. 55.944 55.944 ↓ 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.114..55.944 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. 5.722 117.739 ↓ 1.0 6,939 3

Hash (cost=1,307.42..1,307.42 rows=6,938 width=24) (actual time=117.739..117.739 rows=6,939 loops=3)

  • Buckets: 8192 Batches: 1 Memory Usage: 444kB
19. 112.017 112.017 ↓ 1.0 6,939 3

Seq Scan on sop_sub_date s (cost=0.00..1,307.42 rows=6,938 width=24) (actual time=0.271..112.017 rows=6,939 loops=3)

  • Filter: (share_holder_meeting_date >= (to_timestamp(((CURRENT_DATE - '1 year'::interval))::text, 'YYYY-MM-DD HH24:MI:SS'::text))::timestamp without time zone)
  • Rows Removed by Filter: 29549
20. 0.011 0.011 ↑ 1.0 1 6,939

Index Scan using master_company_pkey on master_company mc (cost=0.42..3.25 rows=1 width=63) (actual time=0.011..0.011 rows=1 loops=6,939)

  • Index Cond: (company_id = sp.company_id)
21. 0.008 0.008 ↑ 5.0 1 6,939

Index Scan using financial_idx1 on financial fin (cost=0.42..0.83 rows=5 width=51) (actual time=0.008..0.008 rows=1 loops=6,939)

  • Index Cond: (company_id = mc.company_id)
22. 20.817 20.817 ↑ 1.0 1 6,939

Index Scan using financial_calc_pkey on financial_calc finc (cost=0.42..0.65 rows=1 width=16) (actual time=0.003..0.003 rows=1 loops=6,939)

  • Index Cond: (fiscal_year_id = fin.fiscal_year_id)
23. 20.817 20.817 ↑ 1.0 1 6,939

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=6,939)

  • Index Cond: (company_id = mc.company_id)