explain.depesz.com

PostgreSQL's explain analyze made readable

Result: B0U8

Settings
# exclusive inclusive rows x rows loops node
1. 0.468 2,094.116 ↑ 518.6 8 1

Unique (cost=165,767.94..165,799.06 rows=4,149 width=39) (actual time=2,093.558..2,094.116 rows=8 loops=1)

2. 4.320 2,093.648 ↑ 3.5 1,199 1

Sort (cost=165,767.94..165,778.31 rows=4,149 width=39) (actual time=2,093.557..2,093.648 rows=1,199 loops=1)

  • Sort Key: seat.aircraft_code, (string_agg((((bp.seat_no)::text || ' => '::text) || ((count(ROW(ac.aircraft_code, (ac.model ->> 'ru'::text), bp.seat_no))))::text), ', '::text) OVER (?))
  • Sort Method: quicksort Memory: 3624kB
3. 1.454 2,089.328 ↑ 3.5 1,199 1

WindowAgg (cost=165,219.11..165,518.62 rows=4,149 width=39) (actual time=2,074.592..2,089.328 rows=1,199 loops=1)

4. 12.157 2,087.874 ↑ 3.5 1,199 1

GroupAggregate (cost=165,219.11..165,414.89 rows=4,149 width=15) (actual time=2,073.754..2,087.874 rows=1,199 loops=1)

  • Group Key: seat.aircraft_code, bp.seat_no
5. 38.858 2,075.717 ↓ 1.8 22,190 1

Sort (cost=165,219.11..165,249.97 rows=12,343 width=55) (actual time=2,073.738..2,075.717 rows=22,190 loops=1)

  • Sort Key: seat.aircraft_code, bp.seat_no
  • Sort Method: quicksort Memory: 3706kB
6. 7.364 2,036.859 ↓ 1.8 22,190 1

Hash Join (cost=5,897.14..164,380.32 rows=12,343 width=55) (actual time=2,005.571..2,036.859 rows=22,190 loops=1)

  • Hash Cond: (fl.aircraft_code = ac.aircraft_code)
7. 8.998 2,029.469 ↓ 3.5 22,190 1

Hash Join (cost=5,895.94..164,354.41 rows=6,400 width=11) (actual time=2,005.530..2,029.469 rows=22,190 loops=1)

  • Hash Cond: ((fl.aircraft_code = seat.aircraft_code) AND ((bp.seat_no)::text = (seat.seat_no)::text))
8. 890.164 2,019.907 ↓ 1.0 22,190 1

Hash Join (cost=5,854.47..164,197.26 rows=22,022 width=7) (actual time=2,004.939..2,019.907 rows=22,190 loops=1)

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

Seq Scan on boarding_passes bp (cost=0.00..137,537.12 rows=7,925,812 width=7) (actual time=0.015..1,033.264 rows=7,925,812 loops=1)

10. 0.325 96.479 ↑ 1.1 541 1

Hash (cost=5,847.00..5,847.00 rows=597 width=8) (actual time=96.479..96.479 rows=541 loops=1)

  • Buckets: 1024 Batches: 1 Memory Usage: 30kB
11. 96.154 96.154 ↑ 1.1 541 1

Seq Scan on flights fl (cost=0.00..5,847.00 rows=597 width=8) (actual time=0.396..96.154 rows=541 loops=1)

  • Filter: (actual_departure > ('2017-08-15 18:00:00+03'::timestamp with time zone - '1 day'::interval))
  • Rows Removed by Filter: 214326
12. 0.319 0.564 ↑ 1.0 1,339 1

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

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

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

14. 0.012 0.026 ↑ 1.0 9 1

Hash (cost=1.09..1.09 rows=9 width=48) (actual time=0.025..0.026 rows=9 loops=1)

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

Seq Scan on aircrafts_data ac (cost=0.00..1.09 rows=9 width=48) (actual time=0.012..0.014 rows=9 loops=1)