explain.depesz.com

PostgreSQL's explain analyze made readable

Result: r4GK

Settings
# exclusive inclusive rows x rows loops node
1. 0.009 14.190 ↓ 3.0 3 1

Sort (cost=3,086.14..3,086.15 rows=1 width=162) (actual time=14.189..14.190 rows=3 loops=1)

  • Sort Key: ranked_messages.periodenddate
  • Sort Method: quicksort Memory: 25kB
2.          

CTE ranked_messages

3. 0.022 14.019 ↓ 155.0 155 1

Limit (cost=3,086.08..3,086.11 rows=1 width=91) (actual time=13.848..14.019 rows=155 loops=1)

4. 0.127 13.997 ↓ 155.0 155 1

WindowAgg (cost=3,086.08..3,086.11 rows=1 width=91) (actual time=13.847..13.997 rows=155 loops=1)

5. 0.258 13.870 ↓ 155.0 155 1

Sort (cost=3,086.08..3,086.09 rows=1 width=81) (actual time=13.837..13.870 rows=155 loops=1)

  • Sort Key: ciqestimateperiod.fiscalquarter, ciqestimateperiod.fiscalyear, nd1.effectivedate DESC
  • Sort Method: quicksort Memory: 46kB
6. 5.579 13.612 ↓ 155.0 155 1

Nested Loop Left Join (cost=1.57..3,086.07 rows=1 width=81) (actual time=2.080..13.612 rows=155 loops=1)

  • Join Filter: (nd1.currencyid = c.currencyid)
  • Rows Removed by Join Filter: 35650
7. 0.047 3.383 ↓ 155.0 155 1

Nested Loop (cost=1.57..3,078.87 rows=1 width=77) (actual time=2.021..3.383 rows=155 loops=1)

8. 0.014 3.048 ↓ 3.0 3 1

Nested Loop (cost=0.87..1,212.42 rows=1 width=38) (actual time=1.989..3.048 rows=3 loops=1)

9. 2.678 2.678 ↑ 1.1 89 1

Index Scan using ix_ciqestimateconsensus_tradingitemid on ciqestimateconsensus (cost=0.43..348.10 rows=102 width=12) (actual time=1.701..2.678 rows=89 loops=1)

  • Index Cond: (tradingitemid = 130824631)
10. 0.356 0.356 ↓ 0.0 0 89

Index Scan using pk_ciqestimateperiod on ciqestimateperiod (cost=0.43..8.46 rows=1 width=30) (actual time=0.004..0.004 rows=0 loops=89)

  • Index Cond: ((estimateperiodid = ciqestimateconsensus.estimateperiodid) AND (periodtypeid = 1))
  • Filter: ((periodenddate >= '2019-12-27 00:00:00'::timestamp without time zone) AND (companyid = 8955572) AND (calendaryear = ANY ('{2019,2020,2021}'::integer[])))
  • Rows Removed by Filter: 0
11. 0.288 0.288 ↑ 12.1 52 3

Index Scan using pk_ciqestimatenumericdata on ciqestimatenumericdata nd1 (cost=0.70..1,860.17 rows=628 width=43) (actual time=0.019..0.096 rows=52 loops=3)

  • Index Cond: ((estimateconsensusid = ciqestimateconsensus.estimateconsensusid) AND (dataitemid = 100208) AND (effectivedate <= '2019-12-27 00:00:00'::timestamp without time zone))
12. 4.650 4.650 ↑ 1.0 231 155

Seq Scan on ciqcurrency c (cost=0.00..4.31 rows=231 width=6) (actual time=0.003..0.030 rows=231 loops=155)

13. 14.181 14.181 ↓ 3.0 3 1

CTE Scan on ranked_messages (cost=0.00..0.02 rows=1 width=162) (actual time=13.856..14.181 rows=3 loops=1)

  • Filter: (rn = 1)
  • Rows Removed by Filter: 152
Planning time : 0.842 ms
Execution time : 14.269 ms