explain.depesz.com

PostgreSQL's explain analyze made readable

Result: 7Evp

Settings
# exclusive inclusive rows x rows loops node
1. 1.393 614.942 ↓ 2,501.0 2,501 1

Unique (cost=18,211.98..18,212.01 rows=1 width=600) (actual time=613.339..614.942 rows=2,501 loops=1)

2. 4.246 613.549 ↓ 3,379.0 3,379 1

Sort (cost=18,211.98..18,211.98 rows=1 width=600) (actual time=613.338..613.549 rows=3,379 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: 837kB
3. 2.402 609.303 ↓ 3,379.0 3,379 1

WindowAgg (cost=18,211.91..18,211.97 rows=1 width=600) (actual time=608.192..609.303 rows=3,379 loops=1)

4. 1.156 606.901 ↓ 3,379.0 3,379 1

Unique (cost=18,211.91..18,211.95 rows=1 width=600) (actual time=605.480..606.901 rows=3,379 loops=1)

5. 11.591 605.745 ↓ 3,379.0 3,379 1

Sort (cost=18,211.91..18,211.92 rows=1 width=600) (actual time=605.479..605.745 rows=3,379 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: 837kB
6. 0.908 594.154 ↓ 3,379.0 3,379 1

Subquery Scan on r (cost=18,211.85..18,211.90 rows=1 width=600) (actual time=591.629..594.154 rows=3,379 loops=1)

  • Filter: (r.rank = 1)
7. 1.290 593.246 ↓ 3,379.0 3,379 1

Unique (cost=18,211.85..18,211.89 rows=1 width=620) (actual time=591.622..593.246 rows=3,379 loops=1)

8. 8.302 591.956 ↓ 3,379.0 3,379 1

Sort (cost=18,211.85..18,211.85 rows=1 width=620) (actual time=591.620..591.956 rows=3,379 loops=1)

  • Sort Key: fin.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))
  • Sort Method: quicksort Memory: 992kB
9. 4.148 583.654 ↓ 3,379.0 3,379 1

WindowAgg (cost=18,211.81..18,211.84 rows=1 width=620) (actual time=579.015..583.654 rows=3,379 loops=1)

10. 5.168 579.506 ↓ 3,379.0 3,379 1

Sort (cost=18,211.81..18,211.81 rows=1 width=146) (actual time=578.993..579.506 rows=3,379 loops=1)

  • Sort Key: fin.company_id, fin.share_holder_meeting_date DESC NULLS LAST
  • Sort Method: quicksort Memory: 994kB
11. 1.581 574.338 ↓ 3,379.0 3,379 1

Nested Loop Left Join (cost=5,236.60..18,211.80 rows=1 width=146) (actual time=52.287..574.338 rows=3,379 loops=1)

12. 0.000 560.252 ↓ 2,501.0 2,501 1

Nested Loop (cost=5,236.31..18,211.47 rows=1 width=138) (actual time=52.265..560.252 rows=2,501 loops=1)

13. 0.000 553.676 ↓ 2,501.0 2,501 1

Gather (cost=5,235.89..18,207.87 rows=1 width=146) (actual time=52.243..553.676 rows=2,501 loops=1)

  • Workers Planned: 2
  • Workers Launched: 2
14. 442.735 556.830 ↓ 834.0 834 3

Nested Loop (cost=4,235.89..17,207.77 rows=1 width=146) (actual time=42.823..556.830 rows=834 loops=3)

  • Join Filter: ((sp.company_id = fin.company_id) AND (sp.fiscal_year_id = fin.fiscal_year_id))
  • Rows Removed by Join Filter: 5523
15. 15.863 114.041 ↓ 5.6 7,971 3

Parallel Hash Join (cost=4,235.48..15,880.83 rows=1,429 width=103) (actual time=38.818..114.041 rows=7,971 loops=3)

  • Hash Cond: (sp.company_id = mc.company_id)
16. 60.256 60.256 ↓ 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.050..60.256 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
17. 19.751 37.922 ↑ 1.8 40,056 3

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

  • Buckets: 131072 Batches: 1 Memory Usage: 10016kB
18. 18.171 18.171 ↑ 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.025..18.171 rows=40,056 loops=3)

19. 0.054 0.054 ↑ 1.0 1 23,914

Index Scan using financial_idx1 on financial fin (cost=0.42..0.91 rows=1 width=51) (actual time=0.019..0.054 rows=1 loops=23,914)

  • Index Cond: (company_id = mc.company_id)
  • 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: 13
20. 10.004 10.004 ↑ 1.0 1 2,501

Index Scan using financial_calc_pkey on financial_calc finc (cost=0.42..3.60 rows=1 width=16) (actual time=0.004..0.004 rows=1 loops=2,501)

  • Index Cond: (fiscal_year_id = fin.fiscal_year_id)
21. 12.505 12.505 ↑ 1.0 1 2,501

Index Scan using master_company_sector_idx1 on master_company_sector mcs (cost=0.29..0.32 rows=1 width=16) (actual time=0.004..0.005 rows=1 loops=2,501)

  • Index Cond: (company_id = mc.company_id)
Planning time : 23.654 ms
Execution time : 622.403 ms