#	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)

                                                                                            QUERY PLAN
--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
 Unique  (cost=165767.94..165799.06 rows=4149 width=39) (actual time=2093.558..2094.116 rows=8 loops=1)
   ->  Sort  (cost=165767.94..165778.31 rows=4149 width=39) (actual time=2093.557..2093.648 rows=1199 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
         ->  WindowAgg  (cost=165219.11..165518.62 rows=4149 width=39) (actual time=2074.592..2089.328 rows=1199 loops=1)
               ->  GroupAggregate  (cost=165219.11..165414.89 rows=4149 width=15) (actual time=2073.754..2087.874 rows=1199 loops=1)
                     Group Key: seat.aircraft_code, bp.seat_no
                     ->  Sort  (cost=165219.11..165249.97 rows=12343 width=55) (actual time=2073.738..2075.717 rows=22190 loops=1)
                           Sort Key: seat.aircraft_code, bp.seat_no
                           Sort Method: quicksort  Memory: 3706kB
                           ->  Hash Join  (cost=5897.14..164380.32 rows=12343 width=55) (actual time=2005.571..2036.859 rows=22190 loops=1)
                                 Hash Cond: (fl.aircraft_code = ac.aircraft_code)
                                 ->  Hash Join  (cost=5895.94..164354.41 rows=6400 width=11) (actual time=2005.530..2029.469 rows=22190 loops=1)
                                       Hash Cond: ((fl.aircraft_code = seat.aircraft_code) AND ((bp.seat_no)::text = (seat.seat_no)::text))
                                       ->  Hash Join  (cost=5854.47..164197.26 rows=22022 width=7) (actual time=2004.939..2019.907 rows=22190 loops=1)
                                             Hash Cond: (bp.flight_id = fl.flight_id)
                                             ->  Seq Scan on boarding_passes bp  (cost=0.00..137537.12 rows=7925812 width=7) (actual time=0.015..1033.264 rows=7925812 loops=1)
                                             ->  Hash  (cost=5847.00..5847.00 rows=597 width=8) (actual time=96.479..96.479 rows=541 loops=1)
                                                   Buckets: 1024  Batches: 1  Memory Usage: 30kB
                                                   ->  Seq Scan on flights fl  (cost=0.00..5847.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
                                       ->  Hash  (cost=21.39..21.39 rows=1339 width=7) (actual time=0.564..0.564 rows=1339 loops=1)
                                             Buckets: 2048  Batches: 1  Memory Usage: 68kB
                                             ->  Seq Scan on seats seat  (cost=0.00..21.39 rows=1339 width=7) (actual time=0.019..0.245 rows=1339 loops=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
                                       ->  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)
 Planning time: 2.352 ms
 Execution time: 2094.591 ms
(30 rows)

Per node type stats

node type	count	sum of times	% of query
GroupAggregate	1	12.157 ms	0.6 %
Hash	3	0.656 ms	0.0 %
Hash Join	3	906.526 ms	43.3 %
Seq Scan	4	1,129.677 ms	53.9 %
Sort	2	43.178 ms	2.1 %
Unique	1	0.468 ms	0.0 %
WindowAgg	1	1.454 ms	0.1 %

Per table stats

Table name	Scan count	Total time	% of query
scan type	count	sum of times	% of table
aircrafts_data	1	0.014 ms	0.0 %
Seq Scan	1	0.014 ms	100.0 %
boarding_passes	1	1,033.264 ms	49.3 %
Seq Scan	1	1,033.264 ms	100.0 %
flights	1	96.154 ms	4.6 %
Seq Scan	1	96.154 ms	100.0 %
seats	1	0.245 ms	0.0 %
Seq Scan	1	0.245 ms	100.0 %

explain.depesz.com

PostgreSQL's explain analyze made readable

Result: eLZq

Color mode:

Visible columns:

Per node type stats

Per table stats