explain.depesz.com

PostgreSQL's explain analyze made readable

Result: oKZ2

Settings
# exclusive inclusive rows x rows loops node
1. 11.489 18,092.311 ↓ 555.0 555 1

GroupAggregate (cost=343,446.830..343,446.860 rows=1 width=24) (actual time=18,070.762..18,092.311 rows=555 loops=1)

  • Group Key: three_three.victor, three_three.hotel_charlie, three_three.two
2.          

CTE hotel_november

3. 243.244 14,833.224 ↓ 1.0 314,023 1

WindowAgg (cost=319,222.260..336,280.020 rows=303,249 width=66) (actual time=14,216.045..14,833.224 rows=314,023 loops=1)

4. 130.987 14,589.980 ↓ 1.0 314,023 1

WindowAgg (cost=319,222.260..326,045.370 rows=303,249 width=54) (actual time=14,216.018..14,589.980 rows=314,023 loops=1)

5. 1,125.578 14,458.993 ↓ 1.0 314,023 1

Sort (cost=319,222.260..319,980.390 rows=303,249 width=46) (actual time=14,216.005..14,458.993 rows=314,023 loops=1)

  • Sort Key: charlie.zulu, charlie.victor, charlie.delta_lima
  • Sort Method: external merge Disk: 19112kB
6. 13,333.415 13,333.415 ↓ 1.0 314,023 1

Seq Scan on charlie (cost=0.000..282,280.220 rows=303,249 width=46) (actual time=0.104..13,333.415 rows=314,023 loops=1)

  • Filter: ((delta_lima <= CURRENT_DATE) AND (delta_lima >= ((CURRENT_DATE - 'foxtrot'::interval))::date))
7.          

CTE kilo_golf

8. 159.948 15,687.312 ↓ 207.1 314,018 1

Sort (cost=7,066.660..7,070.450 rows=1,516 width=254) (actual time=15,650.193..15,687.312 rows=314,018 loops=1)

  • Sort Key: hotel_november.delta_zulu
  • Sort Method: external merge Disk: 19112kB
9. 176.533 15,527.364 ↓ 207.1 314,018 1

WindowAgg (cost=6,903.190..6,986.570 rows=1,516 width=254) (actual time=15,081.115..15,527.364 rows=314,018 loops=1)

10. 142.820 15,350.831 ↓ 207.1 314,018 1

WindowAgg (cost=6,903.190..6,941.090 rows=1,516 width=250) (actual time=15,081.106..15,350.831 rows=314,018 loops=1)

11. 233.964 15,208.011 ↓ 207.1 314,018 1

Sort (cost=6,903.190..6,906.980 rows=1,516 width=246) (actual time=15,081.098..15,208.011 rows=314,018 loops=1)

  • Sort Key: hotel_november.zulu, hotel_november.victor, hotel_november.juliet
  • Sort Method: external merge Disk: 16624kB
12. 14,974.047 14,974.047 ↓ 207.1 314,018 1

CTE Scan on hotel_november (cost=0.000..6,823.100 rows=1,516 width=246) (actual time=14,216.047..14,974.047 rows=314,018 loops=1)

  • Filter: (kilo_papa = 2)
  • Rows Removed by Filter: 5
13.          

CTE november_romeo

14. 172.953 15,907.038 ↓ 1,568.2 313,643 1

HashAggregate (cost=53.060..55.060 rows=200 width=246) (actual time=15,841.023..15,907.038 rows=313,643 loops=1)

  • Group Key: kilo_golf.zulu, kilo_golf.victor, kilo_golf.mike, kilo_golf.three_romeo, kilo_golf.delta_lima
15. 15,734.085 15,734.085 ↓ 207.1 314,018 1

CTE Scan on kilo_golf (cost=0.000..30.320 rows=1,516 width=246) (actual time=15,650.194..15,734.085 rows=314,018 loops=1)

16.          

CTE papa

17. 144.417 17,342.321 ↓ 1,568.2 313,643 1

WindowAgg (cost=11.640..17.140 rows=200 width=258) (actual time=16,922.392..17,342.321 rows=313,643 loops=1)

18. 1,202.333 17,197.904 ↓ 1,568.2 313,643 1

Sort (cost=11.640..12.140 rows=200 width=246) (actual time=16,922.378..17,197.904 rows=313,643 loops=1)

  • Sort Key: november_romeo.zulu, november_romeo.victor, november_romeo.delta_zulu
  • Sort Method: external merge Disk: 15376kB
19. 15,995.571 15,995.571 ↓ 1,568.2 313,643 1

CTE Scan on november_romeo (cost=0.000..4.000 rows=200 width=246) (actual time=15,841.025..15,995.571 rows=313,643 loops=1)

20.          

CTE three_three

21. 243.561 17,918.887 ↓ 1,568.2 313,643 1

WindowAgg (cost=11.640..19.640 rows=200 width=274) (actual time=17,547.126..17,918.887 rows=313,643 loops=1)

22. 230.869 17,675.326 ↓ 1,568.2 313,643 1

Sort (cost=11.640..12.140 rows=200 width=250) (actual time=17,547.089..17,675.326 rows=313,643 loops=1)

  • Sort Key: papa.zulu, papa.victor, papa.november_bravo, papa.tango
  • Sort Method: external merge Disk: 15768kB
23. 17,444.457 17,444.457 ↓ 1,568.2 313,643 1

CTE Scan on papa (cost=0.000..4.000 rows=200 width=250) (actual time=16,922.393..17,444.457 rows=313,643 loops=1)

24. 49.800 18,080.822 ↓ 105,141.0 105,141 1

Sort (cost=4.510..4.510 rows=1 width=24) (actual time=18,070.709..18,080.822 rows=105,141 loops=1)

  • Sort Key: three_three.victor, three_three.hotel_charlie, three_three.two
  • Sort Method: external merge Disk: 3504kB
25. 18,031.022 18,031.022 ↓ 105,141.0 105,141 1

CTE Scan on three_three (cost=0.000..4.500 rows=1 width=24) (actual time=17,547.129..18,031.022 rows=105,141 loops=1)

  • Filter: (xray = 1)
  • Rows Removed by Filter: 208502