1 演示数据库
探索 Airlines 演示数据库：其模式、bookings、tickets、flights、routes，以及 PostgreSQL 18 示例中使用的数据。
1. 基本信息
要处理更复杂的查询，我们需要一个规模可观的数据库：其中有九张填充了逼真数据的表。我们选择航空旅行作为主题。这个数据库包含某个时间段内由一家虚构航空公司运营的航班信息。
数据库模式如下图所示。我们尽量让它既不过于复杂，也不过于简单：足够简单，可以避免不必要的细节；又足够丰富，可以支持有意义且有趣的查询。
网站上提供四种大小的数据库。本书关于查询优化的示例使用的是 2025 年 9 月 1 日的版本，大小约为 2.7 GB，其中包含六个月的航班数据。
可以使用下面的函数检查版本：
SELECT bookings.version();
version
-----------------------------------
PostgresPro 2025-09-01 (181 days)
(1 row)
图 1.1 Airlines 数据库模式
2. 主要实体
核心实体是一个预订（bookings）。
SELECT * FROM bookings b WHERE b.book_ref = '03RUKL';
book_ref | book_date | total_amount
----------+-------------------------------+--------------
03RUKL | 2026-01-04 08:01:43.367932+03 | 84850.00
(1 row)
这行数据给出了预订日期和总金额。按照真实时钟，距离预订的时间每天都会增加：
SELECT now() - b.book_date
FROM bookings b
WHERE b.book_ref = '03RUKL';
?column?
--------------------------
264 days 09:13:04.888436
(1 row)
不过，演示数据库有一个固定的“当前”时间：
SELECT bookings.now();
now
------------------------
2026-03-01 03:00:00+03
(1 row)
相对于这个时钟，距离预订的时间就短得多：
SELECT bookings.now() - b.book_date
FROM bookings b
WHERE b.book_ref = '03RUKL';
?column?
-------------------------
55 days 18:58:16.632068
(1 row)
一笔预订可以包含多名乘客。每名乘客都会为去程行程获得一张单独的机票（tickets）；如果需要返程，还会再获得一张机票。
所选预订包含以下机票：
SELECT t.ticket_no, t.passenger_id, t.passenger_name
FROM bookings b
JOIN tickets t ON t.book_ref = b.book_ref
WHERE b.book_ref = '03RUKL';
ticket_no | passenger_id | passenger_name
---------------+------------------+--------------------
0005435998767 | RU 3761090132061 | Evgeniy Kondratyev
0005435998770 | RU 3761090132061 | Evgeniy Kondratyev
0005435998774 | RU 5595520101762 | Oleg Golubev
0005435998776 | RU 5595520101762 | Oleg Golubev
(4 rows)
如果出发地和目的地之间有直达航班，一张机票只包含一个航段（segments）；如果旅程需要转机，则会包含多个航段。
我们的乘客经过哪些航段？只需查看一名乘客，因为同一笔预订中的所有乘客都遵循相同的行程：
SELECT s.*
FROM tickets t
JOIN segments s ON s.ticket_no = t.ticket_no
WHERE t.ticket_no = '0005435998767';
ticket_no | flight_id | fare_conditions | price
---------------+-----------+-----------------+----------
0005435998767 | 17386 | Economy | 6750.00
0005435998767 | 17606 | Economy | 14500.00
(2 rows)
这是去程机票。把它与返程机票进行比较：
ticket_no | flight_id | fare_conditions | price
---------------+-----------+-----------------+---------
0005435998770 | 26693 | Comfort | 3575.00
0005435998770 | 27013 | Comfort | 5525.00
0005435998770 | 27375 | Economy | 2750.00
0005435998770 | 27537 | Economy | 8250.00
(4 rows)
去程和返程的行程不必相同：返程时，Evgeniy 要转机三次，而且其中两趟航班（flights）属于 Comfort 舱等。
每趟航班都沿着一条航线（routes）从一个机场飞往另一个机场。航线号相同的航线具有相同的出发机场和到达机场，而单独的航班则由出发日期区分。
要找出所选航班对应的航线，必须考虑这两张表之间的时间关系。航班时刻表会随时间变化，因此连接条件不仅要匹配航线号，还要匹配航线的有效时间区间与航班出发时间。
SELECT r.route_no, f.scheduled_departure, f.status,
r.departure_airport dep, r.arrival_airport arr
FROM tickets t
JOIN segments s ON s.ticket_no = t.ticket_no
JOIN flights f ON f.flight_id = s.flight_id
JOIN routes r ON r.route_no = f.route_no
AND r.validity @> f.scheduled_departure
WHERE t.ticket_no = '0005435998767'
ORDER BY f.scheduled_departure;
route_no | scheduled_departure | status | dep | arr
----------+------------------------+---------+-----+-----
PG0177 | 2026-01-05 15:05:00+03 | Arrived | SVO | AER
PG0587 | 2026-01-06 19:00:00+03 | Arrived | AER | LHR
(2 rows)
时刻表（timetable）视图隐藏了这种复杂性。它像航班信息显示屏一样，在隐藏两张表之间连接的同时，展示我们需要的信息：
SELECT tt.route_no, tt.scheduled_departure, tt.status,
tt.departure_airport dep, tt.arrival_airport arr
FROM tickets t
JOIN segments s ON s.ticket_no = t.ticket_no
JOIN timetable tt ON tt.flight_id = s.flight_id
WHERE t.ticket_no = '0005435998770'
ORDER BY tt.scheduled_departure;
route_no | scheduled_departure | status | dep | arr
----------+------------------------+-----------+-----+-----
PG0272 | 2026-02-27 06:25:00+03 | Arrived | LHR | CDG
PG0781 | 2026-03-01 02:50:00+03 | Departed | CDG | MUC
PG0358 | 2026-03-03 03:50:00+03 | Scheduled | MUC | VIE
PG0165 | 2026-03-04 02:20:00+03 | Scheduled | VIE | SVO
(4 rows)
两趟去程航班和一趟返程航班已经抵达（状态为 Arrived）。按照演示数据库的当前时间，乘客正在从 CDG 飞往 MUC（状态为 Departed）。其余航班的值机尚未开放（状态为 Scheduled）。
机场（airports）使用标准的三字母机场代码作为键。下面是一座机场的完整记录：
SELECT * FROM airports WHERE airport_code = 'CDG' \gx
-[ RECORD 1 ]+------------------
airport_code | CDG
airport_name | Charles de Gaulle
city | Paris
country | France
coordinates | (2.55,49.0128)
timezone | Europe/Paris
下面是我们关心的航线：
SELECT lower(r.validity) valid_from,
upper(r.validity) valid_to, r.airplane_code,
r.days_of_week, r.scheduled_time, r.duration
FROM routes r
WHERE r.route_no = 'PG0781'
AND r.validity @> '2026-03-01 02:50:00+03'::timestamptz \gx
-[ RECORD 1 ]--+-----------------------
valid_from | 2026-02-01 03:00:00+03
valid_to | 2026-03-01 03:00:00+03
airplane_code | CR7
days_of_week | {1,2,3,4,5,6,7}
scheduled_time | 00:50:00
duration | 01:25:00
在这个有效期内，航线每周运行七天，使用一架 CR7 飞机（airplanes）：
SELECT * FROM airplanes WHERE airplane_code = 'CR7';
airplane_code | model | range | speed
---------------+-------------------+-------+-------
CR7 | Bombardier CRJ700 | 3100 | 829
(1 row)
座位（seats）的数量及其在各票价舱等中的分布，取决于航班使用的飞机型号。在演示数据库中，同一型号的所有飞机都有相同的客舱配置。
例如，下面是第一排座位：
SELECT s.*
FROM airplanes a
JOIN seats s ON s.airplane_code = a.airplane_code
WHERE a.airplane_code = 'CR7'
AND s.seat_no ~ '^1.$';
airplane_code | seat_no | fare_conditions
---------------+---------+-----------------
CR7 | 1A | Business
CR7 | 1C | Business
CR7 | 1D | Business
(3 rows)
这些是 Business 舱座位。下面是这架飞机每个票价舱等中的座位总数：
SELECT s.fare_conditions, count(*)
FROM seats s
WHERE s.airplane_code = 'CR7'
GROUP BY s.fare_conditions;
fare_conditions | count
-----------------+-------
Business | 6
Comfort | 12
Economy | 52
(3 rows)
下面是所选航线上的一趟航班的完整记录。实际时间可能与时刻表不同：通常不会相差太多（本例中的出发时间延误了六分钟），但有时也可能相差数小时：
SELECT * FROM flights f
WHERE f.flight_id = 27013 \gx
-[ RECORD 1 ]-------+----------------------------
flight_id | 27013
route_no | PG0781
status | Departed
scheduled_departure | 2026-03-01 02:50:00+03
scheduled_arrival | 2026-03-01 04:15:00+03
actual_departure | 2026-03-01 02:56:02.8521+03
actual_arrival |
当乘客为第一趟航班办理值机时，会为机票上的所有航班签发登机牌（boarding_passes）。登机牌会指定座位。航班和座位的组合是唯一的：两名乘客不能占用同一个座位。登机时，登机牌会记录乘客的登机顺序号以及其通过登机口的时间。
我们的乘客坐过、或将要坐哪些座位？
SELECT f.status, f.flight_id, bp.seat_no, bp.boarding_no
FROM tickets t
JOIN segments s ON s.ticket_no = t.ticket_no
JOIN flights f ON f.flight_id = s.flight_id
LEFT JOIN boarding_passes bp
ON bp.ticket_no = s.ticket_no
AND bp.flight_id = s.flight_id
WHERE t.ticket_no IN ('0005435998767','0005435998770')
ORDER BY f.scheduled_departure;
status | flight_id | seat_no | boarding_no
-----------+-----------+---------+-------------
Arrived | 17386 | 50A | 41
Arrived | 17606 | 21D | 16
Arrived | 26693 | 11A | 176
Departed | 27013 | 7C | 49
Scheduled | 27375 | 45D |
Scheduled | 27537 | 13B |
(6 rows)
演示数据库可以用不同语言显示机场、城市、国家和飞机名称。显示语言由数据库级别设置的配置参数控制：
SHOW bookings.lang;
bookings.lang
---------------
en
(1 row)
把当前会话切换为俄语：
SET bookings.lang = 'ru';
SELECT * FROM airports a WHERE a.airport_code = 'SVO' \gx
-[ RECORD 1 ]+------------------
airport_code | SVO
airport_name | Шереметьево
city | Москва
country | Россия
coordinates | (37.4146,55.9726)
timezone | Europe/Moscow
实现方式是在以 JSON 存储译名的基表之上创建视图：
SELECT ml.airport_name, ml.country, ml.city
FROM airports_data ml
WHERE ml.airport_code = 'SVO' \gx
-[ RECORD 1 ]+--------------------------------------------
airport_name | {"en": "Sheremetyevo", "ru": "Шереметьево"}
country | {"en": "Russia", "ru": "Россия"}
city | {"en": "Moscow", "ru": "Москва"}
最后总结一下，演示数据库中包含以下表和视图：
\dtv
List of relations
Schema | Name | Type | Owner
----------+-----------------+-------+----------
bookings | airplanes | view | postgres
bookings | airplanes_data | table | postgres
bookings | airports | view | postgres
bookings | airports_data | table | postgres
bookings | boarding_passes | table | postgres
bookings | bookings | table | postgres
bookings | flights | table | postgres
bookings | routes | table | postgres
bookings | seats | table | postgres
bookings | segments | table | postgres
bookings | tickets | table | postgres
bookings | timetable | view | postgres
(12 rows)