explain.depesz.com

PostgreSQL's explain analyze made readable

Result: 77OE

Settings
# exclusive inclusive rows x rows loops node
1. 22.523 37,403.323 ↓ 11,355.0 11,355 1

Sort (cost=189,622.96..189,622.96 rows=1 width=50) (actual time=37,398.711..37,403.323 rows=11,355 loops=1)

  • Output: ed.effectivedate, (concat(symgv.gvkey, '_', symgv.iid)), ed.dataitemid, ed.dataitemvalue
  • Sort Key: (concat(symgv.gvkey, '_', symgv.iid)), ed.effectivedate
  • Sort Method: quicksort Memory: 1272kB
  • Buffers: shared hit=496392 read=75918
2. 50.406 37,380.800 ↓ 11,355.0 11,355 1

Nested Loop (cost=126,791.37..189,622.95 rows=1 width=50) (actual time=7,728.769..37,380.800 rows=11,355 loops=1)

  • Output: ed.effectivedate, concat(symgv.gvkey, '_', symgv.iid), ed.dataitemid, ed.dataitemvalue
  • Join Filter: (((ed.effectivedate >= symgv.symbolstartdate) OR (symgv.symbolstartdate IS NULL)) AND ((ed.effectivedate <= symgv.symbolenddate) OR (symgv.symbolenddate IS NULL)))
  • Rows Removed by Join Filter: 8
  • Buffers: shared hit=496392 read=75918
3. 6.173 12,331.756 ↓ 1,943.0 1,943 1

Nested Loop (cost=126,791.23..179,784.77 rows=1 width=30) (actual time=7,728.726..12,331.756 rows=1,943 loops=1)

  • Output: ec.estimateconsensusid, symgv.gvkey, symgv.iid, symgv.symbolstartdate, symgv.symbolenddate
  • Inner Unique: true
  • Buffers: shared hit=17214 read=69041
4. 2,831.262 12,286.723 ↓ 1,943.0 1,943 1

Hash Join (cost=126,791.15..179,776.82 rows=1 width=42) (actual time=7,728.695..12,286.723 rows=1,943 loops=1)

  • Output: s.companyid, ep.companyid, ec.estimateconsensusid, symgv.gvkey, symgv.iid, symgv.relatedcompanyid, symgv.symbolstartdate, symgv.symbolenddate
  • Hash Cond: (ec.estimateperiodid = ep.estimateperiodid)
  • Buffers: shared hit=9278 read=69035
5. 2,532.995 2,532.995 ↑ 1.0 5,806,833 1

Seq Scan on public.ciqestimateconsensus ec (cost=0.00..48,628.12 rows=5,810,060 width=8) (actual time=0.050..2,532.995 rows=5,806,833 loops=1)

  • Output: ec.estimateconsensusid, ec.estimateperiodid, ec.tradingitemid, ec.accountingstandardid, ec.parentflag, ec.primaryparentconsolflag
  • Buffers: shared hit=2592 read=34416
6. 1.522 6,922.466 ↓ 2,272.0 2,272 1

Hash (cost=126,791.14..126,791.14 rows=1 width=42) (actual time=6,922.466..6,922.466 rows=2,272 loops=1)

  • Output: s.companyid, ep.companyid, ep.estimateperiodid, symgv.gvkey, symgv.iid, symgv.relatedcompanyid, symgv.symbolstartdate, symgv.symbolenddate
  • Buckets: 4096 (originally 1024) Batches: 1 (originally 1) Memory Usage: 169kB
  • Buffers: shared hit=6686 read=34619
7. 0.000 6,920.944 ↓ 2,272.0 2,272 1

Gather Merge (cost=126,768.72..126,791.14 rows=1 width=42) (actual time=5,462.711..6,920.944 rows=2,272 loops=1)

  • Output: s.companyid, ep.companyid, ep.estimateperiodid, symgv.gvkey, symgv.iid, symgv.relatedcompanyid, symgv.symbolstartdate, symgv.symbolenddate
  • Workers Planned: 2
  • Workers Launched: 2
  • Buffers: shared hit=18750 read=107327
8. 2,083.416 20,116.749 ↓ 757.0 757 3

Merge Join (cost=125,768.71..125,791.03 rows=1 width=42) (actual time=5,415.457..6,705.583 rows=757 loops=3)

  • Output: s.companyid, ep.companyid, ep.estimateperiodid, symgv.gvkey, symgv.iid, symgv.relatedcompanyid, symgv.symbolstartdate, symgv.symbolenddate
  • Merge Cond: (ep.companyid = s.companyid)
  • Buffers: shared hit=18750 read=107327
  • Worker 0: actual time=5452.956..6763.670 rows=701 loops=1
  • Buffers: shared hit=7063 read=33639
  • Worker 1: actual time=5341.348..6523.793 rows=578 loops=1
  • Buffers: shared hit=5001 read=39069
9. 5,380.584 9,635.118 ↓ 44.5 992,708 3

Sort (cost=111,576.76..111,587.92 rows=22,315 width=8) (actual time=2,596.068..3,211.706 rows=992,708 loops=3)

  • Output: ep.companyid, ep.estimateperiodid
  • Sort Key: ep.companyid
  • Sort Method: quicksort Memory: 104259kB
  • Buffers: shared hit=200 read=99905
  • Worker 0: actual time=2601.219..3206.842 rows=987039 loops=1
  • Buffers: shared hit=134 read=31894
  • Worker 1: actual time=2627.921..3191.238 rows=1043371 loops=1
  • Buffers: shared hit=52 read=35344
10. 4,254.534 4,254.534 ↓ 54.6 1,219,284 3

Parallel Seq Scan on public.ciqestimateperiod ep (cost=0.00..111,254.41 rows=22,315 width=8) (actual time=0.031..1,418.178 rows=1,219,284 loops=3)

  • Output: ep.companyid, ep.estimateperiodid
  • Filter: (ep.periodtypeid = 2)
  • Rows Removed by Filter: 2346786
  • Buffers: shared hit=192 read=99905
  • Worker 0: actual time=0.036..1382.137 rows=1193926 loops=1
  • Buffers: shared hit=130 read=31894
  • Worker 1: actual time=0.043..1445.611 rows=1288317 loops=1
  • Buffers: shared hit=48 read=35344
11. 1.119 8,398.215 ↓ 758.0 758 3

Sort (cost=14,191.95..14,191.95 rows=1 width=34) (actual time=2,799.067..2,799.405 rows=758 loops=3)

  • Output: s.companyid, symgv.gvkey, symgv.iid, symgv.relatedcompanyid, symgv.symbolstartdate, symgv.symbolenddate
  • Sort Key: s.companyid
  • Sort Method: quicksort Memory: 26kB
  • Buffers: shared hit=18550 read=7422
  • Worker 0: actual time=2818.783..2819.090 rows=702 loops=1
  • Buffers: shared hit=6929 read=1745
  • Worker 1: actual time=2703.313..2703.558 rows=579 loops=1
  • Buffers: shared hit=4949 read=3725
12. 27.963 8,397.096 ↓ 14.0 14 3

Hash Join (cost=12,203.93..14,191.95 rows=1 width=34) (actual time=2,633.140..2,799.032 rows=14 loops=3)

  • Output: s.companyid, symgv.gvkey, symgv.iid, symgv.relatedcompanyid, symgv.symbolstartdate, symgv.symbolenddate
  • Inner Unique: true
  • Hash Cond: ((symgv.relatedcompanyid = s.companyid) AND (ti.securityid = s.securityid))
  • Buffers: shared hit=18550 read=7422
  • Worker 0: actual time=2629.856..2818.747 rows=14 loops=1
  • Buffers: shared hit=6929 read=1745
  • Worker 1: actual time=2598.458..2703.281 rows=14 loops=1
  • Buffers: shared hit=4949 read=3725
13. 16.215 5,407.575 ↑ 636.3 14 3

Hash Join (cost=6,530.16..8,508.82 rows=8,908 width=34) (actual time=1,636.665..1,802.525 rows=14 loops=3)

  • Output: ti.securityid, symgv.gvkey, symgv.iid, symgv.relatedcompanyid, symgv.symbolstartdate, symgv.symbolenddate
  • Inner Unique: true
  • Hash Cond: (symgv.objectid = ti.tradingitemid)
  • Buffers: shared hit=11383 read=3893
  • Worker 0: actual time=1645.083..1833.942 rows=14 loops=1
  • Buffers: shared hit=3904 read=1190
  • Worker 1: actual time=1636.292..1741.086 rows=14 loops=1
  • Buffers: shared hit=3049 read=2045
14. 565.536 565.536 ↑ 636.3 14 3

Seq Scan on public.ciqgvkeyiid symgv (cost=0.00..1,973.99 rows=8,908 width=34) (actual time=22.689..188.512 rows=14 loops=3)

  • Output: symgv.symbolid, symgv.gvkey, symgv.iid, symgv.relatedcompanyid, symgv.exchangeid, symgv.objectid, symgv.symbolstartdate, symgv.symbolenddate, symgv.activeflag
  • Filter: (concat(symgv.gvkey, '_', symgv.iid) = ANY ('{153130_01,154357_01,155393_01,155394_02,156155_01,156613_01,156861_01,157057_01,001045_04,001075_01,001078_01,001161_01,001177_01}'::text[]))
  • Rows Removed by Filter: 136713
  • Buffers: shared hit=3570
  • Worker 0: actual time=25.596..214.417 rows=14 loops=1
  • Buffers: shared hit=1192
  • Worker 1: actual time=17.774..122.530 rows=14 loops=1
  • Buffers: shared hit=1192
15. 1,429.008 4,825.824 ↑ 1.0 578,164 3

Hash (cost=5,070.07..5,070.07 rows=584,035 width=8) (actual time=1,608.608..1,608.608 rows=578,164 loops=3)

  • Output: ti.securityid, ti.tradingitemid
  • Buckets: 1048576 Batches: 1 Memory Usage: 30777kB
  • Buffers: shared hit=7813 read=3893
  • Worker 0: actual time=1609.907..1609.908 rows=578164 loops=1
  • Buffers: shared hit=2712 read=1190
  • Worker 1: actual time=1613.771..1613.771 rows=578164 loops=1
  • Buffers: shared hit=1857 read=2045
16. 3,396.816 3,396.816 ↑ 1.0 578,164 3

Seq Scan on public.ciqtradingitem ti (cost=0.00..5,070.07 rows=584,035 width=8) (actual time=0.029..1,132.272 rows=578,164 loops=3)

  • Output: ti.securityid, ti.tradingitemid
  • Buffers: shared hit=7813 read=3893
  • Worker 0: actual time=0.040..1143.503 rows=578164 loops=1
  • Buffers: shared hit=2712 read=1190
  • Worker 1: actual time=0.035..1139.358 rows=578164 loops=1
  • Buffers: shared hit=1857 read=2045
17. 989.745 2,961.558 ↑ 1.0 409,250 3

Hash (cost=4,391.11..4,391.11 rows=427,555 width=8) (actual time=987.185..987.186 rows=409,250 loops=3)

  • Output: s.companyid, s.securityid
  • Buckets: 524288 Batches: 1 Memory Usage: 20083kB
  • Buffers: shared hit=7081 read=3527
  • Worker 0: actual time=960.208..960.209 rows=409250 loops=1
  • Buffers: shared hit=2983 read=553
  • Worker 1: actual time=959.610..959.611 rows=409250 loops=1
  • Buffers: shared hit=1856 read=1680
18. 1,971.813 1,971.813 ↑ 1.0 409,250 3

Seq Scan on public.ciqsecurity s (cost=0.00..4,391.11 rows=427,555 width=8) (actual time=0.026..657.271 rows=409,250 loops=3)

  • Output: s.companyid, s.securityid
  • Buffers: shared hit=7081 read=3527
  • Worker 0: actual time=0.034..612.065 rows=409250 loops=1
  • Buffers: shared hit=2983 read=553
  • Worker 1: actual time=0.027..637.288 rows=409250 loops=1
  • Buffers: shared hit=1856 read=1680
19. 38.860 38.860 ↑ 1.0 1 1,943

Index Only Scan using pk_ciqcompany on public.ciqcompany c (cost=0.09..7.95 rows=1 width=4) (actual time=0.020..0.020 rows=1 loops=1,943)

  • Output: c.companyid
  • Index Cond: (c.companyid = s.companyid)
  • Heap Fetches: 1943
  • Buffers: shared hit=7936 read=6
20. 24,998.638 24,998.638 ↑ 553.5 6 1,943

Index Scan using pk_ciqestimatenumericdata on public.ciqestimatenumericdata ed (cost=0.14..9,828.22 rows=3,321 width=22) (actual time=4.875..12.866 rows=6 loops=1,943)

  • Output: ed.estimateconsensusid, ed.dataitemid, ed.effectivedate, ed.todate, ed.dataitemvalue, ed.splitfactor, ed.currencyid, ed.estimatescaleid, ed.pacvertofeedpop
  • Index Cond: ((ed.estimateconsensusid = ec.estimateconsensusid) AND (ed.dataitemid = ANY ('{100179,100186,100193,100200,100207,100214,100221,100235,100242,100249,100256,100263,100270,100277,100284,104019,104061,104082,104096,104103,104117,104124,105068,105075,105082,105114,105125,105299,105307,105321,114154,114165,114176,114187,114198,114209,114220,115403,115416,115429,115442,115455,115468,115481,115494,115507,115520,115533,115591}'::integer[])) AND (ed.effectivedate >= '2000-01-01 00:00:00'::timestamp without time zone) AND (ed.effectivedate <= '2018-12-24 00:00:00'::timestamp without time zone))
  • Buffers: shared hit=479178 read=6877