explain.depesz.com

PostgreSQL's explain analyze made readable

Result: x1DC

Settings
# exclusive inclusive rows x rows loops node
1. 0.122 21.994 ↓ 1.2 642 1

Unique (cost=26,877.57..26,887.85 rows=514 width=40) (actual time=21.827..21.994 rows=642 loops=1)

  • Buffers: shared hit=5845
2. 0.310 21.872 ↓ 1.2 642 1

Sort (cost=26,877.57..26,878.85 rows=514 width=40) (actual time=21.826..21.872 rows=642 loops=1)

  • Sort Key: nd.effectivedate, nd.tradingitemid, nd.estimateanalystid, nd.estimatebrokerid, rs.numericvalue, rs_last.numericvalue, nd_last.effectivedate
  • Sort Method: quicksort Memory: 75kB
  • Buffers: shared hit=5845
3. 0.474 21.562 ↓ 1.2 642 1

Hash Join (cost=852.18..26,854.42 rows=514 width=40) (actual time=8.425..21.562 rows=642 loops=1)

  • Hash Cond: ((nd_last.dataitemvalue)::integer = rs_last.brokerrecommendationscaleid)
  • Buffers: shared hit=5842
4. 0.000 16.995 ↓ 1.2 642 1

Nested Loop (cost=426.73..26,421.26 rows=514 width=36) (actual time=4.264..16.995 rows=642 loops=1)

  • Buffers: shared hit=5752
5. 0.454 10.502 ↓ 1.4 724 1

Hash Join (cost=426.15..17,498.22 rows=514 width=26) (actual time=4.234..10.502 rows=724 loops=1)

  • Hash Cond: ((nd.dataitemvalue)::integer = rs.brokerrecommendationscaleid)
  • Buffers: shared hit=2354
6. 5.934 5.934 ↓ 1.4 724 1

Index Only Scan using ix_ciqestimatedetailnumericdata_multi_col on ciqestimatedetailnumericdata nd (cost=0.70..17,065.06 rows=514 width=30) (actual time=0.048..5.934 rows=724 loops=1)

  • Index Cond: ((tradingitemid = ANY ('{24687740,20204501,20141008,33975500,20124216,223886738,20204501,20124216}'::integer[])) AND (dataitemid = 21625) AND (effectivedate >= '2018-11-05 00:00:00'::timestamp without time zone) AND (effectivedate <= '2020-02-06 00:00:00'::timestamp without time zone))
  • Heap Fetches: 11
  • Buffers: shared hit=2264
7. 2.328 4.114 ↑ 1.0 14,775 1

Hash (cost=239.09..239.09 rows=14,909 width=6) (actual time=4.114..4.114 rows=14,775 loops=1)

  • Buckets: 16384 Batches: 1 Memory Usage: 706kB
  • Buffers: shared hit=90
8. 1.786 1.786 ↑ 1.0 14,775 1

Seq Scan on ciqbrokerrecommendationscale rs (cost=0.00..239.09 rows=14,909 width=6) (actual time=0.004..1.786 rows=14,775 loops=1)

  • Buffers: shared hit=90
9. 0.724 6.516 ↑ 1.0 1 724

Limit (cost=0.57..17.34 rows=1 width=80) (actual time=0.008..0.009 rows=1 loops=724)

  • Buffers: shared hit=3398
10. 5.792 5.792 ↑ 1.0 1 724

Index Scan Backward using ix_ciqestimatedetailnumericdata_multi_col_find_what_analyst_cov on ciqestimatedetailnumericdata nd_last (cost=0.57..17.34 rows=1 width=80) (actual time=0.008..0.008 rows=1 loops=724)

  • Index Cond: ((dataitemid = nd.dataitemid) AND (nd.estimateanalystid = estimateanalystid) AND (effectivedate < nd.effectivedate) AND (nd.tradingitemid = tradingitemid))
  • Buffers: shared hit=3398
11. 2.168 4.093 ↑ 1.0 14,775 1

Hash (cost=239.09..239.09 rows=14,909 width=6) (actual time=4.093..4.093 rows=14,775 loops=1)

  • Buckets: 16384 Batches: 1 Memory Usage: 706kB
  • Buffers: shared hit=90
12. 1.925 1.925 ↑ 1.0 14,775 1

Seq Scan on ciqbrokerrecommendationscale rs_last (cost=0.00..239.09 rows=14,909 width=6) (actual time=0.006..1.925 rows=14,775 loops=1)

  • Buffers: shared hit=90
Planning time : 0.589 ms
Execution time : 22.188 ms