explain.depesz.com

PostgreSQL's explain analyze made readable

Result: 3grB

Settings
# exclusive inclusive rows x rows loops node
1. 2.865 43.643 ↑ 1,456.6 8 1

HashAggregate (cost=7,978.82..8,124.48 rows=11,653 width=71) (actual time=43.591..43.643 rows=8 loops=1)

  • Group 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 (?)
2. 1.116 40.778 ↑ 9.7 1,199 1

WindowAgg (cost=7,570.96..7,920.55 rows=11,653 width=71) (actual time=39.673..40.778 rows=1,199 loops=1)

3. 0.635 39.662 ↑ 9.7 1,199 1

Sort (cost=7,570.96..7,600.10 rows=11,653 width=47) (actual time=39.569..39.662 rows=1,199 loops=1)

  • Sort Key: seat.aircraft_code
  • Sort Method: quicksort Memory: 148kB
4. 14.800 39.027 ↑ 9.7 1,199 1

HashAggregate (cost=6,638.23..6,783.90 rows=11,653 width=47) (actual time=38.688..39.027 rows=1,199 loops=1)

  • Group Key: (ac.model ->> 'ru'::text), bp.seat_no, seat.aircraft_code
5. 12.979 24.227 ↓ 1.9 22,190 1

Hash Join (cost=55.90..6,492.57 rows=11,653 width=107) (actual time=0.839..24.227 rows=22,190 loops=1)

  • Hash Cond: ((ac.aircraft_code = seat.aircraft_code) AND ((bp.seat_no)::text = (seat.seat_no)::text))
6. 3.237 10.698 ↓ 1.1 22,190 1

Nested Loop (cost=14.43..6,312.63 rows=20,805 width=75) (actual time=0.263..10.698 rows=22,190 loops=1)

7. 0.346 1.510 ↑ 1.0 541 1

Hash Join (cost=14.00..1,390.12 rows=564 width=76) (actual time=0.227..1.510 rows=541 loops=1)

  • Hash Cond: (fl.aircraft_code = ac.aircraft_code)
8. 1.011 1.149 ↑ 1.0 541 1

Bitmap Heap Scan on flights fl (cost=12.79..1,386.73 rows=564 width=8) (actual time=0.200..1.149 rows=541 loops=1)

  • Recheck Cond: (actual_departure > ('2017-08-15 18:00:00+03'::timestamp with time zone - '1 day'::interval))
  • Heap Blocks: exact=485
9. 0.138 0.138 ↑ 1.0 541 1

Bitmap Index Scan on flight_departure_idx (cost=0.00..12.65 rows=564 width=0) (actual time=0.138..0.138 rows=541 loops=1)

  • Index Cond: (actual_departure > ('2017-08-15 18:00:00+03'::timestamp with time zone - '1 day'::interval))
10. 0.005 0.015 ↑ 1.0 9 1

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

  • Buckets: 1024 Batches: 1 Memory Usage: 9kB
11. 0.010 0.010 ↑ 1.0 9 1

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

12. 5.951 5.951 ↑ 3.9 41 541

Index Only Scan using boarding_passes_flight_id_seat_no_key on boarding_passes bp (cost=0.43..7.15 rows=158 width=7) (actual time=0.005..0.011 rows=41 loops=541)

  • Index Cond: (flight_id = fl.flight_id)
  • Heap Fetches: 0
13. 0.333 0.550 ↑ 1.0 1,339 1

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

  • Buckets: 2048 Batches: 1 Memory Usage: 68kB
14. 0.217 0.217 ↑ 1.0 1,339 1

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