explain.depesz.com

PostgreSQL's explain analyze made readable

Result: jECN

Settings
# exclusive inclusive rows x rows loops node
1. 0.028 5,397.678 ↓ 6.0 6 1

Sort (cost=551,416.67..551,416.68 rows=1 width=162) (actual time=5,397.678..5,397.678 rows=6 loops=1)

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

CTE ranked_messages

3. 0.044 5,397.212 ↓ 11.1 367 1

Limit (cost=551,415.10..551,415.92 rows=33 width=91) (actual time=5,396.742..5,397.212 rows=367 loops=1)

4. 0.395 5,397.168 ↓ 11.1 367 1

WindowAgg (cost=551,415.10..551,415.92 rows=33 width=91) (actual time=5,396.742..5,397.168 rows=367 loops=1)

5. 0.857 5,396.773 ↓ 11.1 367 1

Sort (cost=551,415.10..551,415.18 rows=33 width=81) (actual time=5,396.723..5,396.773 rows=367 loops=1)

  • Sort Key: ciqestimateperiod.fiscalquarter, ciqestimateperiod.fiscalyear, ciqestimateperiod.periodtypeid, nd1.effectivedate DESC
  • Sort Method: quicksort Memory: 76kB
6. 12.855 5,395.916 ↓ 11.1 367 1

Nested Loop Left Join (cost=397,900.58..551,414.26 rows=33 width=81) (actual time=4,881.878..5,395.916 rows=367 loops=1)

  • Join Filter: (nd1.currencyid = c.currencyid)
  • Rows Removed by Join Filter: 84410
7. 0.203 5,376.088 ↓ 11.1 367 1

Nested Loop (cost=397,900.58..551,295.03 rows=33 width=77) (actual time=4,881.733..5,376.088 rows=367 loops=1)

8. 1,191.853 5,203.205 ↓ 3.0 12 1

Hash Join (cost=397,899.87..536,944.68 rows=4 width=38) (actual time=4,867.312..5,203.205 rows=12 loops=1)

  • Hash Cond: (ciqestimateconsensus.estimateperiodid = ciqestimateperiod.estimateperiodid)
9. 978.494 978.494 ↑ 1.0 6,146,587 1

Seq Scan on ciqestimateconsensus (cost=0.00..100,626.78 rows=6,146,878 width=12) (actual time=0.006..978.494 rows=6,146,587 loops=1)

10. 0.024 3,032.858 ↓ 5.0 15 1

Hash (cost=397,899.84..397,899.84 rows=3 width=30) (actual time=3,032.858..3,032.858 rows=15 loops=1)

  • Buckets: 1024 Batches: 1 Memory Usage: 9kB
11. 3,032.834 3,032.834 ↓ 5.0 15 1

Seq Scan on ciqestimateperiod (cost=0.00..397,899.84 rows=3 width=30) (actual time=1,756.439..3,032.834 rows=15 loops=1)

  • Filter: ((periodenddate >= '2019-09-30 00:00:00'::timestamp without time zone) AND (periodtypeid = ANY ('{2,10}'::integer[])) AND (companyid = 36478) AND (calendaryear = ANY ('{2019,2020,2021}'::integer[])) AND (calendarquarter = ANY ('{1,2,3,4}'::integer[])))
  • Rows Removed by Filter: 11177633
12. 172.680 172.680 ↑ 43.8 31 12

Index Scan using pk_ciqestimatenumericdata on ciqestimatenumericdata nd1 (cost=0.70..3,574.02 rows=1,357 width=43) (actual time=5.590..14.390 rows=31 loops=12)

  • Index Cond: ((estimateconsensusid = ciqestimateconsensus.estimateconsensusid) AND (dataitemid = 100180))
13. 6.905 6.973 ↑ 1.0 231 367

Materialize (cost=0.00..5.47 rows=231 width=6) (actual time=0.000..0.019 rows=231 loops=367)

14. 0.068 0.068 ↑ 1.0 231 1

Seq Scan on ciqcurrency c (cost=0.00..4.31 rows=231 width=6) (actual time=0.010..0.068 rows=231 loops=1)

15. 5,397.650 5,397.650 ↓ 6.0 6 1

CTE Scan on ranked_messages (cost=0.00..0.74 rows=1 width=162) (actual time=5,396.753..5,397.650 rows=6 loops=1)

  • Filter: (rn = 1)
  • Rows Removed by Filter: 361
Planning time : 3.340 ms
Execution time : 5,397.886 ms