explain.depesz.com

PostgreSQL's explain analyze made readable

Result: ZK9M

Settings
# exclusive inclusive rows x rows loops node
1. 97.260 18,988.602 ↓ 2.2 14,005 1

Hash Join (cost=63,176.60..254,612.67 rows=6,394 width=46) (actual time=1,511.961..18,988.602 rows=14,005 loops=1)

  • Hash Cond: (nd.estimateperiodid = ep.estimateperiodid)
  • Buffers: shared hit=159713 read=12561, temp read=3718 written=3712
  • I/O Timings: read=18288.696
2. 6.796 18,447.368 ↓ 1.1 34,189 1

Nested Loop (cost=3,936.24..160,192.64 rows=31,838 width=38) (actual time=1,065.578..18,447.368 rows=34,189 loops=1)

  • Buffers: shared hit=10900 read=12561
  • I/O Timings: read=18288.696
3. 0.626 1,032.616 ↓ 2.4 212 1

HashAggregate (cost=3,935.54..3,936.43 rows=89 width=4) (actual time=1,032.354..1,032.616 rows=212 loops=1)

  • Group Key: ti.tradingitemid
  • Buffers: shared hit=2558 read=778
  • I/O Timings: read=1002.033
4. 0.492 1,031.990 ↓ 2.4 212 1

Nested Loop (cost=2.12..3,935.31 rows=89 width=4) (actual time=7.782..1,031.990 rows=212 loops=1)

  • Buffers: shared hit=2558 read=778
  • I/O Timings: read=1002.033
5. 0.319 668.766 ↓ 2.2 212 1

Nested Loop (cost=1.56..3,718.59 rows=95 width=8) (actual time=5.273..668.766 rows=212 loops=1)

  • Buffers: shared hit=1677 read=533
  • I/O Timings: read=642.862
6. 0.403 424.367 ↓ 1.2 216 1

Nested Loop (cost=1.00..3,111.52 rows=173 width=4) (actual time=4.479..424.367 rows=216 loops=1)

  • Buffers: shared hit=759 read=351
  • I/O Timings: read=402.314
7. 0.061 20.010 ↓ 1.2 218 1

Nested Loop (cost=0.44..20.65 rows=180 width=4) (actual time=2.416..20.010 rows=218 loops=1)

  • Buffers: shared hit=7 read=24
  • I/O Timings: read=2.002
8. 0.005 0.005 ↑ 1.0 1 1

Index Only Scan using ix_ciqsubtypetogics on ciqsubtypetogics gic (cost=0.15..4.17 rows=1 width=4) (actual time=0.004..0.005 rows=1 loops=1)

  • Index Cond: (gic = 40102010)
  • Heap Fetches: 0
  • Buffers: shared hit=2
9. 19.944 19.944 ↑ 1.4 218 1

Index Only Scan using ix_ciqcompanyindustry_companyid on ciqcompanyindustry ind (cost=0.29..13.50 rows=298 width=8) (actual time=2.408..19.944 rows=218 loops=1)

  • Index Cond: (industryid = gic.subtypeid)
  • Heap Fetches: 240
  • Buffers: shared hit=5 read=24
  • I/O Timings: read=2.002
10. 403.954 403.954 ↑ 3.0 1 218

Index Only Scan using ix_ciqsecurity_multi_columns on ciqsecurity se (cost=0.56..17.14 rows=3 width=8) (actual time=1.844..1.853 rows=1 loops=218)

  • Index Cond: ((companyid = ind.companyid) AND (primaryflag = 1))
  • Filter: ((securitysubtypeid = 1) OR (securitysubtypeid = 2))
  • Rows Removed by Filter: 0
  • Heap Fetches: 139
  • Buffers: shared hit=752 read=327
  • I/O Timings: read=400.312
11. 244.080 244.080 ↑ 1.0 1 216

Index Only Scan using ix_ciqsymbol_active on ciqsymbol sy (cost=0.56..3.50 rows=1 width=4) (actual time=1.103..1.130 rows=1 loops=216)

  • Index Cond: ((objectid = se.securityid) AND (activeflag = 1))
  • Heap Fetches: 144
  • Buffers: shared hit=918 read=182
  • I/O Timings: read=240.548
12. 362.732 362.732 ↑ 1.0 1 212

Index Only Scan using ix_ciqtradingitem_multiple on ciqtradingitem ti (cost=0.56..2.27 rows=1 width=8) (actual time=1.518..1.711 rows=1 loops=212)

  • Index Cond: ((securityid = sy.objectid) AND (primaryflag = 1))
  • Heap Fetches: 212
  • Buffers: shared hit=881 read=245
  • I/O Timings: read=359.171
13. 17,407.956 17,407.956 ↑ 2.2 161 212

Index Only Scan using ix_ciqestimatedetailnumericdata_multi_col on ciqestimatedetailnumericdata nd (cost=0.70..1,752.11 rows=358 width=38) (actual time=10.312..82.113 rows=161 loops=212)

  • Index Cond: ((tradingitemid = ti.tradingitemid) AND (dataitemid = 21634) AND (effectivedate >= '2018-07-27 00:00:00'::timestamp without time zone) AND (effectivedate <= '2020-02-05 00:00:00'::timestamp without time zone))
  • Heap Fetches: 180
  • Buffers: shared hit=8342 read=11783
  • I/O Timings: read=17286.663
14. 223.502 443.974 ↑ 1.0 961,316 1

Hash (cost=41,836.86..41,836.86 rows=1,001,160 width=16) (actual time=443.974..443.974 rows=961,316 loops=1)

  • Buckets: 524288 Batches: 4 Memory Usage: 16280kB
  • Buffers: shared hit=148813, temp written=3522
15. 220.472 220.472 ↑ 1.0 961,316 1

Index Only Scan using ix_estimateperiod_multi_columns_fisc on ciqestimateperiod ep (cost=0.56..41,836.86 rows=1,001,160 width=16) (actual time=0.037..220.472 rows=961,316 loops=1)

  • Index Cond: (periodtypeid = 1)
  • Heap Fetches: 0
  • Buffers: shared hit=148813
Planning time : 2.275 ms
Execution time : 18,990.559 ms