explain.depesz.com

PostgreSQL's explain analyze made readable

Result: pBIw

Settings
# exclusive inclusive rows x rows loops node
1. 0.248 2,646.182 ↓ 1.8 366 1

Sort (cost=4,689,900.47..4,689,900.97 rows=200 width=36) (actual time=2,646.109..2,646.182 rows=366 loops=1)

  • Sort Key: dps.num
  • Sort Method: quicksort Memory: 52kB
2.          

CTE dates

3. 0.036 0.036 ↑ 33.3 30 1

Function Scan on generate_series t (cost=0.00..12.50 rows=1,000 width=8) (actual time=0.029..0.036 rows=30 loops=1)

4.          

CTE dps

5. 0.159 0.159 ↑ 2.7 366 1

Function Scan on generate_series num (cost=0.00..10.00 rows=1,000 width=4) (actual time=0.066..0.159 rows=366 loops=1)

6.          

CTE ty

7. 28.086 1,151.237 ↓ 87.5 8,053 1

HashAggregate (cost=2,301,156.07..2,301,157.22 rows=92 width=72) (actual time=1,148.179..1,151.237 rows=8,053 loops=1)

  • Group Key: flights.departuredate, (flights.departuredate - prep_held_multi.observation_date), json_each_text.key
8. 42.357 1,123.151 ↑ 63.3 25,328 1

Nested Loop (cost=0.58..2,277,108.07 rows=1,603,200 width=72) (actual time=177.448..1,123.151 rows=25,328 loops=1)

9. 81.188 742.442 ↑ 19.0 84,588 1

Nested Loop (cost=0.58..253,068.06 rows=1,603,200 width=40) (actual time=177.414..742.442 rows=84,588 loops=1)

10. 20.696 413.699 ↓ 2.2 35,365 1

Nested Loop Left Join (cost=0.57..221,004.06 rows=16,032 width=145) (actual time=177.205..413.699 rows=35,365 loops=1)

11. 322.803 322.803 ↑ 1.2 120 1

Seq Scan on flights (cost=0.00..97,795.42 rows=149 width=8) (actual time=177.133..322.803 rows=120 loops=1)

  • Filter: ((departuredate >= '2019-08-01'::date) AND (departuredate <= '2019-08-30'::date) AND (cabin = 'Y'::text) AND (""right""(segmentname, 3) = 'HKG'::text))
  • Rows Removed by Filter: 2226797
12. 70.200 70.200 ↓ 1.4 295 120

Index Scan using prep_held_multi_id_obs_date on prep_held_multi (cost=0.57..824.84 rows=206 width=145) (actual time=0.009..0.585 rows=295 loops=120)

  • Index Cond: (flights.id = id)
13. 247.555 247.555 ↑ 50.0 2 35,365

Function Scan on json_each_text json_each_text_1 (cost=0.01..1.00 rows=100 width=32) (actual time=0.007..0.007 rows=2 loops=35,365)

14. 338.352 338.352 ↓ 0.0 0 84,588

Function Scan on json_each_text (cost=0.01..1.26 rows=1 width=64) (actual time=0.004..0.004 rows=0 loops=84,588)

  • Filter: (key = 'MEL-HKG'::text)
  • Rows Removed by Filter: 1
15.          

CTE ly

16. 37.854 1,430.528 ↓ 93.5 8,601 1

HashAggregate (cost=2,188,447.21..2,188,448.82 rows=92 width=72) (actual time=1,427.266..1,430.528 rows=8,601 loops=1)

  • Group Key: flights_1.departuredate, (((flights_1.departuredate - '364 days'::interval))::date - prep_held_multi_1.observation_date), json_each_text_2.key
17. 66.934 1,392.674 ↑ 47.5 33,733 1

Nested Loop (cost=1.01..2,164,417.21 rows=1,602,000 width=72) (actual time=178.016..1,392.674 rows=33,733 loops=1)

18. 70.716 847.364 ↑ 13.4 119,594 1

Nested Loop (cost=1.00..133,882.20 rows=1,602,000 width=40) (actual time=177.981..847.364 rows=119,594 loops=1)

19. 22.626 426.584 ↓ 2.4 38,896 1

Nested Loop Left Join (cost=1.00..101,842.20 rows=16,020 width=145) (actual time=177.707..426.584 rows=38,896 loops=1)

20. 0.290 326.198 ↑ 1.2 120 1

Nested Loop Left Join (cost=0.43..99,047.58 rows=149 width=8) (actual time=177.653..326.198 rows=120 loops=1)

21. 325.068 325.068 ↑ 1.2 120 1

Seq Scan on flights flights_1 (cost=0.00..97,795.42 rows=149 width=8) (actual time=177.543..325.068 rows=120 loops=1)

  • Filter: ((departuredate >= '2019-08-01'::date) AND (departuredate <= '2019-08-30'::date) AND (cabin = 'Y'::text) AND (""right""(segmentname, 3) = 'HKG'::text))
  • Rows Removed by Filter: 2226797
22. 0.840 0.840 ↑ 1.0 1 120

Index Scan using flights_ty_ly_id on flights_ty_ly (cost=0.43..8.39 rows=1 width=8) (actual time=0.006..0.007 rows=1 loops=120)

  • Index Cond: (flights_1.id = id)
23. 77.760 77.760 ↓ 1.6 324 120

Index Scan using prep_held_multi_id_obs_date on prep_held_multi prep_held_multi_1 (cost=0.57..16.70 rows=206 width=145) (actual time=0.010..0.648 rows=324 loops=120)

  • Index Cond: (flights_ty_ly.ly_id = id)
24. 350.064 350.064 ↑ 33.3 3 38,896

Function Scan on json_each_text json_each_text_3 (cost=0.01..1.00 rows=100 width=32) (actual time=0.008..0.009 rows=3 loops=38,896)

25. 478.376 478.376 ↓ 0.0 0 119,594

Function Scan on json_each_text json_each_text_2 (cost=0.01..1.26 rows=1 width=64) (actual time=0.004..0.004 rows=0 loops=119,594)

  • Filter: (key = 'MEL-HKG'::text)
  • Rows Removed by Filter: 1
26. 5.121 2,645.934 ↓ 1.8 366 1

HashAggregate (cost=200,260.28..200,264.28 rows=200 width=36) (actual time=2,645.710..2,645.934 rows=366 loops=1)

  • Group Key: dps.num
27. 9.973 2,640.813 ↑ 91.1 10,980 1

WindowAgg (cost=147,760.28..177,760.28 rows=1,000,000 width=24) (actual time=2,628.016..2,640.813 rows=10,980 loops=1)

28. 11.670 2,630.840 ↑ 91.1 10,980 1

Sort (cost=147,760.28..150,260.28 rows=1,000,000 width=24) (actual time=2,627.988..2,630.840 rows=10,980 loops=1)

  • Sort Key: dates.day, (CASE WHEN (ly.held_ly IS NULL) THEN CASE WHEN (ty.held_ty IS NULL) THEN 0 ELSE 1 END ELSE 1 END), dps.num DESC
  • Sort Method: quicksort Memory: 1202kB
29. 7.425 2,619.170 ↑ 91.1 10,980 1

Hash Full Join (cost=6.44..27,592.44 rows=1,000,000 width=24) (actual time=2,598.458..2,619.170 rows=10,980 loops=1)

  • Hash Cond: ((dps.num = ly.dpd) AND (dates.day = ly.departuredate))
30. 6.287 1,172.617 ↑ 91.1 10,980 1

Hash Full Join (cost=3.22..20,066.22 rows=1,000,000 width=16) (actual time=1,159.301..1,172.617 rows=10,980 loops=1)

  • Hash Cond: ((dps.num = ty.dpd) AND (dates.day = ty.departuredate))
31. 4.537 7.200 ↑ 91.1 10,980 1

Merge Full Join (cost=0.00..12,540.00 rows=1,000,000 width=8) (actual time=0.119..7.200 rows=10,980 loops=1)

32. 0.348 0.348 ↑ 2.7 366 1

CTE Scan on dps (cost=0.00..20.00 rows=1,000 width=4) (actual time=0.074..0.348 rows=366 loops=1)

33. 2.244 2.315 ↓ 10.6 10,615 1

Materialize (cost=0.00..22.50 rows=1,000 width=4) (actual time=0.039..2.315 rows=10,615 loops=1)

34. 0.071 0.071 ↑ 33.3 30 1

CTE Scan on dates (cost=0.00..20.00 rows=1,000 width=4) (actual time=0.030..0.071 rows=30 loops=1)

35. 2.679 1,159.130 ↓ 87.5 8,053 1

Hash (cost=1.84..1.84 rows=92 width=16) (actual time=1,159.130..1,159.130 rows=8,053 loops=1)

  • Buckets: 8192 (originally 1024) Batches: 1 (originally 1) Memory Usage: 442kB
36. 1,156.451 1,156.451 ↓ 87.5 8,053 1

CTE Scan on ty (cost=0.00..1.84 rows=92 width=16) (actual time=1,148.189..1,156.451 rows=8,053 loops=1)

37. 2.913 1,439.128 ↓ 93.5 8,601 1

Hash (cost=1.84..1.84 rows=92 width=16) (actual time=1,439.128..1,439.128 rows=8,601 loops=1)

  • Buckets: 16384 (originally 1024) Batches: 1 (originally 1) Memory Usage: 532kB
38. 1,436.215 1,436.215 ↓ 93.5 8,601 1

CTE Scan on ly (cost=0.00..1.84 rows=92 width=16) (actual time=1,427.276..1,436.215 rows=8,601 loops=1)

Planning time : 1.361 ms
Execution time : 2,647.408 ms