explain.depesz.com

PostgreSQL's explain analyze made readable

Result: Bd1x

Settings
# exclusive inclusive rows x rows loops node
1. 12.378 1,379.571 ↓ 55.5 5,109 1

HashAggregate (cost=2,188,447.21..2,188,448.82 rows=92 width=72) (actual time=1,377.713..1,379.571 rows=5,109 loops=1)

  • Group Key: flights.departuredate, (((flights.departuredate - '364 days'::interval))::date - prep_held_multi.observation_date), json_each_text.key
2. 39.642 1,367.193 ↑ 177.3 9,033 1

Nested Loop (cost=1.01..2,164,417.21 rows=1,602,000 width=72) (actual time=178.790..1,367.193 rows=9,033 loops=1)

3. 74.766 849.175 ↑ 13.4 119,594 1

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

4. 22.549 424.345 ↓ 2.4 38,896 1

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

5. 0.212 324.516 ↑ 1.2 120 1

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

6. 323.464 323.464 ↑ 1.2 120 1

Seq Scan on flights (cost=0.00..97,795.42 rows=149 width=8) (actual time=177.041..323.464 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
7. 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.id = id)
8. 77.280 77.280 ↓ 1.6 324 120

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

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

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

10. 478.376 478.376 ↓ 0.0 0 119,594

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

  • Filter: (key = 'ADL-HKG'::text)
  • Rows Removed by Filter: 1
Planning time : 0.460 ms
Execution time : 1,380.582 ms