explain.depesz.com

PostgreSQL's explain analyze made readable

Result: 336d

Settings
# exclusive inclusive rows x rows loops node
1. 29,407.506 497,884.994 ↑ 61.4 6,097,239 1

Merge Join (cost=3,813,334.98..18,798,010.44 rows=374,165,724 width=1,329) (actual time=466,448.506..497,884.994 rows=6,097,239 loops=1)

  • Merge Cond: ((p.price_key = c.price_key) AND (p.flight_group_key = c.flight_group_key))
2.          

CTE cte

3. 2,932.459 91,950.442 ↑ 1.0 6,075,468 1

Unique (cost=1,956,854.74..2,019,055.01 rows=6,220,027 width=1,128) (actual time=87,961.889..91,950.442 rows=6,075,468 loops=1)

4. 79,244.076 89,017.983 ↓ 1.0 6,291,382 1

Sort (cost=1,956,854.74..1,972,404.80 rows=6,220,027 width=1,128) (actual time=87,961.887..89,017.983 rows=6,291,382 loops=1)

  • Sort Key: fa_1.dynamic_flight_group_key, p_1.ap_id, p_1.room_group, (CASE WHEN (fa_1.markup_type = 'PREMIUM'::text) THEN ((((p_1.price_current)::numeric + ('2'::numeric * COALESCE(fa_1.adult_price, '0'::numeric))) + ('0'::numeric * COALESCE(fa_1.child_price, '0'::numeric))))::integer ELSE ((p_1.price_current + (2 * COALESCE(fa_1.adult_markup, 0))) + (0 * COALESCE(fa_1.child_markup, 0))) END)
  • Sort Method: quicksort Memory: 3440602kB
5. 6,058.074 9,773.907 ↓ 1.0 6,291,382 1

Hash Join (cost=62,639.41..1,254,971.67 rows=6,220,027 width=1,128) (actual time=351.720..9,773.907 rows=6,291,382 loops=1)

  • Hash Cond: (p_1.flight_group_key = fa_1.flight_group_key)
6. 3,364.171 3,364.171 ↓ 3.3 7,820,154 1

Seq Scan on prices_1100 p_1 (cost=0.00..625,612.00 rows=2,406,200 width=564) (actual time=0.032..3,364.171 rows=7,820,154 loops=1)

7. 189.511 351.662 ↓ 1,140.2 589,469 1

Hash (cost=62,632.95..62,632.95 rows=517 width=736) (actual time=351.662..351.662 rows=589,469 loops=1)

  • Buckets: 1048576 (originally 1024) Batches: 1 (originally 1) Memory Usage: 147975kB
8. 3.682 162.151 ↓ 1,140.2 589,469 1

Gather (cost=1,000.00..62,632.95 rows=517 width=736) (actual time=3.446..162.151 rows=589,469 loops=1)

  • Workers Planned: 2
  • Workers Launched: 2
9. 158.469 158.469 ↓ 913.9 196,490 3

Parallel Seq Scan on flights_all fa_1 (cost=0.00..61,581.25 rows=215 width=736) (actual time=1.235..158.469 rows=196,490 loops=3)

  • Filter: (d3pf AND (seats >= 2) AND (stay = (bedbanks_nights + 1)))
  • Rows Removed by Filter: 10379
10. 217,784.880 221,313.564 ↓ 3.2 7,820,014 1

Sort (cost=880,649.05..886,664.55 rows=2,406,200 width=961) (actual time=220,549.200..221,313.564 rows=7,820,014 loops=1)

  • Sort Key: p.price_key, p.flight_group_key
  • Sort Method: quicksort Memory: 4228875kB
11. 3,528.684 3,528.684 ↓ 3.3 7,820,154 1

Seq Scan on prices_1100 p (cost=0.00..625,612.00 rows=2,406,200 width=961) (actual time=0.007..3,528.684 rows=7,820,154 loops=1)

12. 149,442.197 247,163.924 ↑ 1.0 6,097,239 1

Sort (cost=913,630.92..929,180.99 rows=6,220,027 width=1,276) (actual time=245,896.515..247,163.924 rows=6,097,239 loops=1)

  • Sort Key: c.price_key, c.flight_group_key
  • Sort Method: quicksort Memory: 3329272kB
13. 2,170.001 97,721.727 ↑ 1.0 6,075,468 1

Hash Join (cost=71,019.63..211,747.86 rows=6,220,027 width=1,276) (actual time=88,778.167..97,721.727 rows=6,075,468 loops=1)

  • Hash Cond: ((c.flight_key)::text = (fa.flight_key)::text)
14. 94,739.377 94,739.377 ↑ 1.0 6,075,468 1

CTE Scan on cte c (cost=0.00..124,400.54 rows=6,220,027 width=728) (actual time=87,961.894..94,739.377 rows=6,075,468 loops=1)

15. 321.539 812.349 ↑ 1.0 620,606 1

Hash (cost=63,262.06..63,262.06 rows=620,606 width=768) (actual time=812.349..812.349 rows=620,606 loops=1)

  • Buckets: 1048576 Batches: 1 Memory Usage: 160840kB
16. 490.810 490.810 ↑ 1.0 620,606 1

Seq Scan on flights_all fa (cost=0.00..63,262.06 rows=620,606 width=768) (actual time=0.015..490.810 rows=620,606 loops=1)

Planning time : 7.605 ms