explain.depesz.com

PostgreSQL's explain analyze made readable

Result: bfG5

Settings
# exclusive inclusive rows x rows loops node
1. 0.017 610.917 ↓ 3.0 3 1

Sort (cost=118,741.57..118,741.57 rows=1 width=162) (actual time=610.917..610.917 rows=3 loops=1)

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

CTE ranked_messages

3. 0.011 610.801 ↓ 155.0 155 1

Limit (cost=118,741.51..118,741.54 rows=1 width=91) (actual time=610.710..610.801 rows=155 loops=1)

4. 0.083 610.790 ↓ 155.0 155 1

WindowAgg (cost=118,741.51..118,741.54 rows=1 width=91) (actual time=610.710..610.790 rows=155 loops=1)

5. 0.160 610.707 ↓ 155.0 155 1

Sort (cost=118,741.51..118,741.51 rows=1 width=81) (actual time=610.697..610.707 rows=155 loops=1)

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

Nested Loop Left Join (cost=1.14..118,741.50 rows=1 width=81) (actual time=395.495..610.547 rows=155 loops=1)

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

Nested Loop (cost=1.14..118,734.30 rows=1 width=77) (actual time=395.454..604.522 rows=155 loops=1)

8. 0.110 553.089 ↓ 3.0 3 1

Nested Loop (cost=0.43..116,867.85 rows=1 width=38) (actual time=392.054..553.089 rows=3 loops=1)

9. 552.534 552.534 ↑ 1.1 89 1

Seq Scan on ciqestimateconsensus (cost=0.00..116,003.53 rows=102 width=12) (actual time=234.549..552.534 rows=89 loops=1)

  • Filter: (tradingitemid = 130824631)
  • Rows Removed by Filter: 6146498
10. 0.445 0.445 ↓ 0.0 0 89

Index Scan using pk_ciqestimateperiod on ciqestimateperiod (cost=0.43..8.46 rows=1 width=30) (actual time=0.005..0.005 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. 51.372 51.372 ↑ 12.1 52 3

Index Scan using pk_ciqestimatenumericdata on ciqestimatenumericdata nd1 (cost=0.70..1,860.17 rows=628 width=43) (actual time=3.435..17.124 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. 2.790 2.790 ↑ 1.0 231 155

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

13. 610.900 610.900 ↓ 3.0 3 1

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

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