explain.depesz.com

PostgreSQL's explain analyze made readable

Result: MCwx : 3_4

Settings
# exclusive inclusive rows x rows loops node
1. 0.614 17,483.900 ↑ 518.6 8 1

Unique (cost=338,729.03..338,760.15 rows=4,149 width=71) (actual time=17,481.061..17,483.900 rows=8 loops=1)

2. 15.851 17,483.286 ↑ 3.5 1,199 1

Sort (cost=338,729.03..338,739.40 rows=4,149 width=71) (actual time=17,481.060..17,483.286 rows=1,199 loops=1)

  • Sort Key: ((ac.model ->> 'ru'::text)), (string_agg((((bp.seat_no)::text || ' => '::text) || ((count(ROW(ac.aircraft_code, ((ac.model ->> 'ru'::text)), bp.seat_no))))::text), ', '::text) OVER (?))
  • Sort Method: external sort Disk: 3616kB
3. 1.685 17,467.435 ↑ 3.5 1,199 1

WindowAgg (cost=338,355.23..338,479.70 rows=4,149 width=71) (actual time=17,465.751..17,467.435 rows=1,199 loops=1)

4. 0.604 17,465.750 ↑ 3.5 1,199 1

Sort (cost=338,355.23..338,365.61 rows=4,149 width=47) (actual time=17,465.644..17,465.750 rows=1,199 loops=1)

  • Sort Key: ac.aircraft_code
  • Sort Method: quicksort Memory: 148kB
5. 5,108.676 17,465.146 ↑ 3.5 1,199 1

HashAggregate (cost=338,054.05..338,105.91 rows=4,149 width=47) (actual time=17,464.823..17,465.146 rows=1,199 loops=1)

  • Group Key: (ac.model ->> 'ru'::text), bp.seat_no, ac.aircraft_code
6. 3,760.893 12,356.470 ↓ 1.9 7,916,179 1

Hash Join (cost=9,138.41..286,922.05 rows=4,090,560 width=103) (actual time=164.677..12,356.470 rows=7,916,179 loops=1)

  • Hash Cond: (fl.aircraft_code = ac.aircraft_code)
7. 3,162.910 8,595.563 ↓ 3.7 7,916,179 1

Hash Join (cost=9,137.20..268,537.47 rows=2,112,599 width=11) (actual time=164.652..8,595.563 rows=7,916,179 loops=1)

  • Hash Cond: ((fl.aircraft_code = seat.aircraft_code) AND ((bp.seat_no)::text = (seat.seat_no)::text))
8. 3,668.902 5,432.101 ↓ 1.1 7,916,179 1

Hash Join (cost=9,095.73..230,136.18 rows=7,302,948 width=7) (actual time=164.090..5,432.101 rows=7,916,179 loops=1)

  • Hash Cond: (bp.flight_id = fl.flight_id)
9. 1,599.869 1,599.869 ↑ 1.0 7,925,812 1

Seq Scan on boarding_passes bp (cost=0.00..137,538.44 rows=7,925,944 width=7) (actual time=0.031..1,599.869 rows=7,925,812 loops=1)

10. 49.938 163.330 ↓ 1.0 198,043 1

Hash (cost=5,847.00..5,847.00 rows=197,978 width=8) (actual time=163.330..163.330 rows=198,043 loops=1)

  • Buckets: 131072 Batches: 4 Memory Usage: 2975kB
11. 113.392 113.392 ↓ 1.0 198,043 1

Seq Scan on flights fl (cost=0.00..5,847.00 rows=197,978 width=8) (actual time=0.063..113.392 rows=198,043 loops=1)

  • Filter: (actual_departure > ('2017-08-15 18:00:00+03'::timestamp with time zone - '365 days'::interval))
  • Rows Removed by Filter: 16824
12. 0.340 0.552 ↑ 1.0 1,339 1

Hash (cost=21.39..21.39 rows=1,339 width=7) (actual time=0.552..0.552 rows=1,339 loops=1)

  • Buckets: 2048 Batches: 1 Memory Usage: 68kB
13. 0.212 0.212 ↑ 1.0 1,339 1

Seq Scan on seats seat (cost=0.00..21.39 rows=1,339 width=7) (actual time=0.007..0.212 rows=1,339 loops=1)

14. 0.005 0.014 ↑ 1.0 9 1

Hash (cost=1.09..1.09 rows=9 width=68) (actual time=0.014..0.014 rows=9 loops=1)

  • Buckets: 1024 Batches: 1 Memory Usage: 9kB
15. 0.009 0.009 ↑ 1.0 9 1

Seq Scan on aircrafts_data ac (cost=0.00..1.09 rows=9 width=68) (actual time=0.007..0.009 rows=9 loops=1)