explain.depesz.com

PostgreSQL's explain analyze made readable

Result: ZVvL

Settings
# exclusive inclusive rows x rows loops node
1. 3.323 1,621.389 ↓ 5,079.0 5,079 1

Unique (cost=18,178.02..18,178.05 rows=1 width=608) (actual time=1,617.637..1,621.389 rows=5,079 loops=1)

2. 9.937 1,618.066 ↓ 6,962.0 6,962 1

Sort (cost=18,178.02..18,178.02 rows=1 width=608) (actual time=1,617.636..1,618.066 rows=6,962 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: 1960kB
3. 5.830 1,608.129 ↓ 6,962.0 6,962 1

WindowAgg (cost=18,177.95..18,178.01 rows=1 width=608) (actual time=1,605.761..1,608.129 rows=6,962 loops=1)

4. 3.022 1,602.299 ↓ 6,962.0 6,962 1

Unique (cost=18,177.95..18,177.98 rows=1 width=608) (actual time=1,598.612..1,602.299 rows=6,962 loops=1)

5. 27.554 1,599.277 ↓ 6,962.0 6,962 1

Sort (cost=18,177.95..18,177.95 rows=1 width=608) (actual time=1,598.611..1,599.277 rows=6,962 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: 1959kB
6. 3.625 1,571.723 ↓ 6,962.0 6,962 1

Subquery Scan on r (cost=18,177.88..18,177.94 rows=1 width=608) (actual time=1,553.338..1,571.723 rows=6,962 loops=1)

  • Filter: (r.rank = 1)
  • Rows Removed by Filter: 21459
7. 12.244 1,568.098 ↓ 28,421.0 28,421 1

Unique (cost=18,177.88..18,177.92 rows=1 width=644) (actual time=1,553.330..1,568.098 rows=28,421 loops=1)

8. 63.796 1,555.854 ↓ 28,421.0 28,421 1

Sort (cost=18,177.88..18,177.89 rows=1 width=644) (actual time=1,553.328..1,555.854 rows=28,421 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: 8318kB
9. 36.643 1,492.058 ↓ 28,421.0 28,421 1

WindowAgg (cost=18,177.84..18,177.87 rows=1 width=644) (actual time=1,451.384..1,492.058 rows=28,421 loops=1)

10. 53.769 1,455.415 ↓ 28,421.0 28,421 1

Sort (cost=18,177.84..18,177.84 rows=1 width=170) (actual time=1,451.352..1,455.415 rows=28,421 loops=1)

  • Sort Key: fin.company_id, fin.share_holder_meeting_date DESC, s.submission_date DESC NULLS LAST
  • Sort Method: quicksort Memory: 8318kB
11. 31.620 1,401.646 ↓ 28,421.0 28,421 1

Nested Loop (cost=5,237.03..18,177.83 rows=1 width=170) (actual time=41.686..1,401.646 rows=28,421 loops=1)

12. 27.314 309.738 ↓ 33,134.0 33,134 1

Nested Loop Left Join (cost=5,236.60..18,169.55 rows=1 width=154) (actual time=41.620..309.738 rows=33,134 loops=1)

13. 27.579 210.820 ↓ 23,868.0 23,868 1

Nested Loop (cost=5,236.31..18,169.22 rows=1 width=146) (actual time=41.608..210.820 rows=23,868 loops=1)

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

Gather (cost=5,235.89..18,168.57 rows=1 width=154) (actual time=41.593..63.901 rows=23,868 loops=1)

  • Workers Planned: 2
  • Workers Launched: 2
15. 117.911 226.104 ↓ 7,956.0 7,956 3

Nested Loop (cost=4,235.89..17,168.47 rows=1 width=154) (actual time=33.772..226.104 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: 24937
16. 11.638 108.181 ↓ 5.6 7,971 3

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

  • Hash Cond: (sp.company_id = mc.company_id)
17. 63.638 63.638 ↓ 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..63.638 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.686 32.905 ↑ 1.8 40,056 3

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

  • Buckets: 131072 Batches: 1 Memory Usage: 10048kB
19. 15.219 15.219 ↑ 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.219 rows=40,056 loops=3)

20. 0.012 0.012 ↑ 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.006..0.012 rows=4 loops=23,914)

  • Index Cond: (company_id = mc.company_id)
21. 119.340 119.340 ↑ 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.005..0.005 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.003..0.003 rows=1 loops=23,868)

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

Index Scan using sop_sub_date_idx on sop_sub_date s (cost=0.43..8.27 rows=1 width=24) (actual time=0.032..0.032 rows=1 loops=33,134)

  • Index Cond: (shareholder_proposals_id = sp.shareholder_proposals_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
Planning time : 16.108 ms
Execution time : 1,628.265 ms