explain.depesz.com

PostgreSQL's explain analyze made readable

Result: 0WS1

Settings
# exclusive inclusive rows x rows loops node
1. 13.357 6,219.368 ↓ 5.4 4,600 1

HashAggregate (cost=19,549,024.39..19,549,039.42 rows=859 width=72) (actual time=6,217.771..6,219.368 rows=4,600 loops=1)

  • Group Key: flights.departuredate, (((flights.departuredate - '364 days'::interval))::date - prep_held_multi.observation_date), json_each_text.key
2. 212.891 6,206.011 ↑ 1,605.6 9,328 1

Nested Loop (cost=72.79..19,324,373.89 rows=14,976,700 width=72) (actual time=29.468..6,206.011 rows=9,328 loops=1)

3. 608.543 2,886.632 ↑ 19.3 776,622 1

Nested Loop (cost=72.78..341,406.63 rows=14,976,700 width=40) (actual time=0.526..2,886.632 rows=776,622 loops=1)

4. 137.127 631.087 ↓ 1.6 235,286 1

Nested Loop Left Join (cost=72.77..41,872.62 rows=149,767 width=145) (actual time=0.499..631.087 rows=235,286 loops=1)

5. 2.594 11.170 ↑ 1.1 1,254 1

Nested Loop Left Join (cost=72.20..15,745.80 rows=1,393 width=8) (actual time=0.487..11.170 rows=1,254 loops=1)

6. 1.877 2.306 ↑ 1.1 1,254 1

Bitmap Heap Scan on flights (cost=71.77..4,896.50 rows=1,393 width=8) (actual time=0.473..2.306 rows=1,254 loops=1)

  • Recheck Cond: ((""right""(segmentname, 3) = 'ADL'::text) AND (departuredate >= '2019-08-01'::date) AND (departuredate <= '2019-08-30'::date) AND (cabin = 'Y'::text))
  • Heap Blocks: exact=367
7. 0.429 0.429 ↑ 1.1 1,254 1

Bitmap Index Scan on flights_destination_departuredate_cabin (cost=0.00..71.43 rows=1,393 width=0) (actual time=0.429..0.429 rows=1,254 loops=1)

  • Index Cond: ((""right""(segmentname, 3) = 'ADL'::text) AND (departuredate >= '2019-08-01'::date) AND (departuredate <= '2019-08-30'::date) AND (cabin = 'Y'::text))
8. 6.270 6.270 ↑ 1.0 1 1,254

Index Scan using flights_ty_ly_id on flights_ty_ly (cost=0.43..7.78 rows=1 width=8) (actual time=0.005..0.005 rows=1 loops=1,254)

  • Index Cond: (flights.id = id)
9. 482.790 482.790 ↑ 1.1 187 1,254

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.008..0.385 rows=187 loops=1,254)

  • Index Cond: (flights_ty_ly.ly_id = id)
10. 1,647.002 1,647.002 ↑ 33.3 3 235,286

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=3 loops=235,286)

11. 3,106.488 3,106.488 ↓ 0.0 0 776,622

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

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