explain.depesz.com

PostgreSQL's explain analyze made readable

Result: ErzB

Settings
# exclusive inclusive rows x rows loops node
1. 0.008 2,510.824 ↓ 6.0 6 1

Sort (cost=550,023.33..550,023.34 rows=1 width=162) (actual time=2,510.824..2,510.824 rows=6 loops=1)

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

CTE ranked_messages

3. 0.025 2,510.608 ↓ 12.7 367 1

Limit (cost=550,021.95..550,022.67 rows=29 width=91) (actual time=2,510.377..2,510.608 rows=367 loops=1)

4. 0.197 2,510.583 ↓ 12.7 367 1

WindowAgg (cost=550,021.95..550,022.67 rows=29 width=91) (actual time=2,510.376..2,510.583 rows=367 loops=1)

5. 0.472 2,510.386 ↓ 12.7 367 1

Sort (cost=550,021.95..550,022.02 rows=29 width=81) (actual time=2,510.361..2,510.386 rows=367 loops=1)

  • Sort Key: ciqestimateperiod.fiscalquarter, ciqestimateperiod.fiscalyear, ciqestimateperiod.periodtypeid, nd1.effectivedate DESC
  • Sort Method: quicksort Memory: 76kB
6. 7.078 2,509.914 ↓ 12.7 367 1

Nested Loop Left Join (cost=397,900.58..550,021.24 rows=29 width=81) (actual time=2,309.527..2,509.914 rows=367 loops=1)

  • Join Filter: (nd1.currencyid = c.currencyid)
  • Rows Removed by Join Filter: 84410
7. 0.117 2,498.799 ↓ 12.7 367 1

Nested Loop (cost=397,900.58..549,915.87 rows=29 width=77) (actual time=2,309.443..2,498.799 rows=367 loops=1)

8. 533.319 2,423.622 ↓ 3.0 12 1

Hash Join (cost=397,899.87..536,939.95 rows=4 width=38) (actual time=2,306.179..2,423.622 rows=12 loops=1)

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

Seq Scan on ciqestimateconsensus (cost=0.00..100,623.87 rows=6,146,587 width=12) (actual time=0.005..447.093 rows=6,146,587 loops=1)

10. 0.011 1,443.210 ↓ 5.0 15 1

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

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

Seq Scan on ciqestimateperiod (cost=0.00..397,899.84 rows=3 width=30) (actual time=859.156..1,443.199 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. 75.060 75.060 ↑ 39.6 31 12

Index Scan using pk_ciqestimatenumericdata on ciqestimatenumericdata nd1 (cost=0.70..3,231.69 rows=1,229 width=43) (actual time=2.439..6.255 rows=31 loops=12)

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

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

14. 0.033 0.033 ↑ 1.0 231 1

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

15. 2,510.816 2,510.816 ↓ 6.0 6 1

CTE Scan on ranked_messages (cost=0.00..0.65 rows=1 width=162) (actual time=2,510.385..2,510.816 rows=6 loops=1)

  • Filter: (rn = 1)
  • Rows Removed by Filter: 361
Planning time : 0.776 ms
Execution time : 2,510.920 ms