explain.depesz.com

PostgreSQL's explain analyze made readable

Result: iWpx : 3_3

Settings
# exclusive inclusive rows x rows loops node
1. 0.605 66,320.175 ↑ 4,667.6 8 1

Unique (cost=1,280,131.54..1,280,411.60 rows=37,341 width=71) (actual time=66,317.303..66,320.175 rows=8 loops=1)

2. 12.693 66,319.570 ↑ 31.1 1,199 1

Sort (cost=1,280,131.54..1,280,224.90 rows=37,341 width=71) (actual time=66,317.301..66,319.570 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.204 66,306.877 ↑ 31.1 1,199 1

WindowAgg (cost=1,276,175.55..1,277,295.78 rows=37,341 width=71) (actual time=66,305.677..66,306.877 rows=1,199 loops=1)

4. 1.203 66,305.673 ↑ 31.1 1,199 1

Sort (cost=1,276,175.55..1,276,268.90 rows=37,341 width=47) (actual time=66,305.581..66,305.673 rows=1,199 loops=1)

  • Sort Key: seat.aircraft_code
  • Sort Method: quicksort Memory: 148kB
5. 4,086.288 66,304.470 ↑ 31.1 1,199 1

GroupAggregate (cost=1,211,514.62..1,273,339.79 rows=37,341 width=47) (actual time=57,111.616..66,304.470 rows=1,199 loops=1)

  • Group Key: ((ac.model ->> 'ru'::text)), bp.seat_no, seat.aircraft_code
6. 38,852.388 62,218.182 ↓ 1.9 7,916,179 1

Sort (cost=1,211,514.62..1,221,741.02 rows=4,090,560 width=107) (actual time=57,107.603..62,218.182 rows=7,916,179 loops=1)

  • Sort Key: ((ac.model ->> 'ru'::text)), bp.seat_no, seat.aircraft_code
  • Sort Method: external merge Disk: 889704kB
7. 3,674.177 23,365.794 ↓ 1.9 7,916,179 1

Hash Join (cost=9,138.41..286,922.05 rows=4,090,560 width=107) (actual time=166.417..23,365.794 rows=7,916,179 loops=1)

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

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

  • Hash Cond: ((fl.aircraft_code = seat.aircraft_code) AND ((bp.seat_no)::text = (seat.seat_no)::text))
9. 14,646.580 16,428.867 ↓ 1.1 7,916,179 1

Hash Join (cost=9,095.73..230,136.18 rows=7,302,948 width=7) (actual time=165.889..16,428.867 rows=7,916,179 loops=1)

  • Hash Cond: (bp.flight_id = fl.flight_id)
10. 1,616.780 1,616.780 ↑ 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.040..1,616.780 rows=7,925,812 loops=1)

11. 50.309 165.507 ↓ 1.0 198,043 1

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

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

Seq Scan on flights fl (cost=0.00..5,847.00 rows=197,978 width=8) (actual time=0.064..115.198 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
13. 0.302 0.490 ↑ 1.0 1,339 1

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

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

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

15. 0.005 0.016 ↑ 1.0 9 1

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

  • Buckets: 1024 Batches: 1 Memory Usage: 9kB
16. 0.011 0.011 ↑ 1.0 9 1

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