explain.depesz.com

PostgreSQL's explain analyze made readable

Result: Trj : Optimization for: plan #S6CN

Settings
# exclusive inclusive rows x rows loops node
1. 1.040 667.251 ↓ 55.0 55 1

GroupAggregate (cost=8,177.480..8,177.510 rows=1 width=24) (actual time=665.981..667.251 rows=55 loops=1)

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

CTE hotel_november

3. 19.348 432.561 ↑ 1.0 30,069 1

WindowAgg (cost=6,258.090..7,436.090 rows=30,400 width=66) (actual time=412.287..432.561 rows=30,069 loops=1)

4. 74.774 413.213 ↑ 1.0 30,069 1

Sort (cost=6,258.090..6,334.090 rows=30,400 width=46) (actual time=412.268..413.213 rows=30,069 loops=1)

  • Sort Key: delta_juliet.zulu, delta_juliet.victor
  • Sort Method: quicksort Memory: 3118kB
5. 338.439 338.439 ↑ 1.0 30,069 1

Index Scan using four on delta_juliet (cost=0.450..3,994.540 rows=30,400 width=46) (actual time=1.832..338.439 rows=30,069 loops=1)

  • Index Cond: ((delta_lima >= ((CURRENT_DATE - 'foxtrot'::interval))::date) AND (delta_lima <= CURRENT_DATE))
6.          

CTE kilo_golf

7. 12.467 494.171 ↓ 197.8 30,069 1

Sort (cost=703.380..703.760 rows=152 width=254) (actual time=492.408..494.171 rows=30,069 loops=1)

  • Sort Key: hotel_november.delta_zulu
  • Sort Method: external merge Disk: 1832kB
8. 15.952 481.704 ↓ 197.8 30,069 1

WindowAgg (cost=689.510..697.870 rows=152 width=254) (actual time=451.934..481.704 rows=30,069 loops=1)

9. 13.032 465.752 ↓ 197.8 30,069 1

WindowAgg (cost=689.510..693.310 rows=152 width=250) (actual time=451.929..465.752 rows=30,069 loops=1)

10. 7.444 452.720 ↓ 197.8 30,069 1

Sort (cost=689.510..689.890 rows=152 width=246) (actual time=451.925..452.720 rows=30,069 loops=1)

  • Sort Key: hotel_november.zulu, hotel_november.victor, hotel_november.juliet
  • Sort Method: quicksort Memory: 3118kB
11. 445.276 445.276 ↓ 197.8 30,069 1

CTE Scan on hotel_november (cost=0.000..684.000 rows=152 width=246) (actual time=412.289..445.276 rows=30,069 loops=1)

  • Filter: (kilo_papa = 2)
12.          

CTE november_romeo

13. 13.828 512.972 ↓ 197.0 29,939 1

HashAggregate (cost=5.320..6.840 rows=152 width=246) (actual time=508.196..512.972 rows=29,939 loops=1)

  • Group Key: kilo_golf.zulu, kilo_golf.victor, kilo_golf.mike, kilo_golf.three_romeo, kilo_golf.delta_lima
14. 499.144 499.144 ↓ 197.8 30,069 1

CTE Scan on kilo_golf (cost=0.000..3.040 rows=152 width=246) (actual time=492.409..499.144 rows=30,069 loops=1)

15.          

CTE papa

16. 13.860 613.959 ↓ 197.0 29,939 1

WindowAgg (cost=8.550..12.730 rows=152 width=258) (actual time=598.534..613.959 rows=29,939 loops=1)

17. 80.243 600.099 ↓ 197.0 29,939 1

Sort (cost=8.550..8.930 rows=152 width=246) (actual time=598.525..600.099 rows=29,939 loops=1)

  • Sort Key: november_romeo.zulu, november_romeo.victor, november_romeo.delta_zulu
  • Sort Method: quicksort Memory: 3107kB
18. 519.856 519.856 ↓ 197.0 29,939 1

CTE Scan on november_romeo (cost=0.000..3.040 rows=152 width=246) (actual time=508.197..519.856 rows=29,939 loops=1)

19.          

CTE three_three

20. 22.212 651.942 ↓ 197.0 29,939 1

WindowAgg (cost=8.550..14.630 rows=152 width=274) (actual time=628.980..651.942 rows=29,939 loops=1)

21. 8.415 629.730 ↓ 197.0 29,939 1

Sort (cost=8.550..8.930 rows=152 width=250) (actual time=628.962..629.730 rows=29,939 loops=1)

  • Sort Key: papa.zulu, papa.victor, papa.november_bravo, papa.tango
  • Sort Method: quicksort Memory: 3107kB
22. 621.315 621.315 ↓ 197.0 29,939 1

CTE Scan on papa (cost=0.000..3.040 rows=152 width=250) (actual time=598.537..621.315 rows=29,939 loops=1)

23. 2.407 666.211 ↓ 10,271.0 10,271 1

Sort (cost=3.430..3.430 rows=1 width=24) (actual time=665.929..666.211 rows=10,271 loops=1)

  • Sort Key: three_three.victor, three_three.hotel_charlie, three_three.two
  • Sort Method: quicksort Memory: 1183kB
24. 663.804 663.804 ↓ 10,271.0 10,271 1

CTE Scan on three_three (cost=0.000..3.420 rows=1 width=24) (actual time=628.983..663.804 rows=10,271 loops=1)

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