PostgreSQL简单关联查询性能缓慢问题排查与优化咨询

原始查询语句 #

explain analyze 
SELECT "booked_rooms".* 
FROM "booked_rooms" 
INNER JOIN "bookings" 
    ON "bookings"."id" = "booked_rooms"."booking_id" 
WHERE (booked_rooms.arrival_date <= '2022-08-01' 
AND booked_rooms.departure_date >= '2022-07-01') 
AND "booked_rooms"."hotel_id" = 935 
AND "bookings"."status" IN ('confirmed', 'modified')

原始执行计划 #

+--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| QUERY PLAN                                                                                                                                                                     |
|--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------|
| Gather  (cost=1134.98..12664.78 rows=2714 width=407) (actual time=1.453..13.914 rows=1784 loops=1)                                                                             |
|   Workers Planned: 2                                                                                                                                                           |
|   Workers Launched: 2                                                                                                                                                          |
|   ->  Nested Loop  (cost=134.98..11393.38 rows=1131 width=407) (actual time=0.416..5.306 rows=595 loops=3)                                                                     |
|         ->  Parallel Bitmap Heap Scan on booked_rooms  (cost=134.89..6080.30 rows=1458 width=407) (actual time=0.371..2.046 rows=675 loops=3)                                  |
|               Recheck Cond: ((hotel_id = 935) AND (arrival_date <= '2022-08-01'::date) AND (departure_date >= '2022-07-01'::date))                                             |
|               Heap Blocks: exact=714                                                                                                                                           |
|               ->  Bitmap Index Scan on idx_booked_rooms_on_hotel_id_arrival_departure_date  (cost=0.00..134.72 rows=3499 width=0) (actual time=0.862..0.862 rows=2024 loops=1) |
|                     Index Cond: ((hotel_id = 935) AND (arrival_date <= '2022-08-01'::date) AND (departure_date >= '2022-07-01'::date))                                         |
|         ->  Index Scan using bookings_pkey on bookings  (cost=0.09..3.64 rows=1 width=8) (actual time=0.004..0.004 rows=1 loops=2024)                                          |
|               Index Cond: (id = booked_rooms.booking_id)                                                                                                                       |
|               Filter: ((status)::text = ANY ('{confirmed,modified}'::text[]))                                                                                                  |
|               Rows Removed by Filter: 0                                                                                                                                        |
| Planning Time: 0.431 ms                                                                                                                                                        |
| Execution Time: 14.080 ms                                                                                                                                                      |
+--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
EXPLAIN 15
Time: 0.097s

现有索引定义 #

• booked_rooms表："idx_booked_rooms_on_hotel_id_arrival_departure_date" btree (hotel_id, arrival_date, departure_date)
• bookings表："bookings_pkey" PRIMARY KEY, btree (id) 以及 "index_bookings_on_status" btree (status)

表数据量 #

用户疑问与问题 #

该查询实际执行耗时约1.5秒，性能不佳。无法理解为何Parallel Bitmap Heap Scan会在相同条件的Index Cond之前执行，且该步骤占用较多时间，寻求查询优化建议。

更新：扩大日期范围后的查询 #

explain analyze SELECT "booked_rooms".* FROM "booked_rooms" INNER JOIN "bookings" ON "bookings"."id" = "booked_rooms"."booking_id" WHERE "booked_rooms"."hotel_id" = 935 AND "bookings"."status" IN ('confirmed', 'modified') AND (booked_rooms.arrival_date <= '2022-10-01' AND booked_rooms.departure_date >= '2022-05-01')

对应的执行计划 #

+-----------------------------------------------------------------------------------------------------------------------------------------------------+
| QUERY PLAN                                                                                                                                          |
|-----------------------------------------------------------------------------------------------------------------------------------------------------|
| Gather  (cost=1156.54..20006.21 rows=4732 width=407) (actual time=3.550..37.854 rows=4074 loops=1)                                                  |
|   Workers Planned: 2                                                                                                                                |
|   Workers Launched: 2                                                                                                                               |
|   ->  Nested Loop  (cost=156.54..18533.01 rows=1972 width=407) (actual time=1.122..12.234 rows=1358 loops=3)                                        |
|         ->  Parallel Bitmap Heap Scan on booked_rooms  (cost=156.45..9851.57 rows=2542 width=407) (actual time=1.035..4.323 rows=1657 loops=3)      |
|               Recheck Cond: ((hotel_id = 935) AND (arrival_date <= '2022-10-01'::date) AND (departure_date >= '2022-05-01'::date))                  |
|               Heap Blocks: exact=2462                                                                                                               |
|               ->  Bitmap Index Scan on idx_booked_rooms_on_hotel_id_arrival_departure_date  (cost=0.00..156.15 rows=6101 width=0) (actual time=2.504)|
|                     Index Cond: ((hotel_id = 935) AND (arrival_date <= '2022-10-01'::date) AND (departure_date >= '2022-05-01'::date))              |
|         ->  Index Scan using bookings_pkey on bookings  (cost=0.09..3.42 rows=1 width=8) (actual time=0.004..0.004 rows=1 loops=4971)               |
|               Index Cond: (id = booked_rooms.booking_id)                                                                                            |
|               Filter: ((status)::text = ANY ('{confirmed,modified}'::text[]))                                                                       |
|               Rows Removed by Filter: 0                                                                                                             |
| Planning Time: 0.385 ms                                                                                                                             |
| Execution Time: 38.190 ms                                                                                                                           |
+-----------------------------------------------------------------------------------------------------------------------------------------------------+
EXPLAIN 15
Time: 0.124s

优化建议 #

1. 澄清执行计划顺序逻辑 #

执行计划的缩进表示依赖关系：Parallel Bitmap Heap Scan下方的Bitmap Index Scan是它的子步骤——先执行索引扫描生成位图，再由堆扫描根据位图抓取数据，并非堆扫描先于索引执行，只是格式缩进造成的误解。

2. 优化索引策略 #

• 调整bookings表索引：创建联合索引btree(status, id)，过滤status时可直接通过索引获取id，避免先通过主键索引定位行再过滤status的额外IO开销。
• 增强booked_rooms索引：PostgreSQL 11+可创建包含booking_id的覆盖索引，减少堆扫描的IO：

  CREATE INDEX idx_booked_rooms_hotel_arrival_departure_booking ON booked_rooms (hotel_id, arrival_date, departure_date) INCLUDE (booking_id);
  

  索引扫描后可直接拿到booking_id进行关联，无需回表读取额外字段。

3. 调整执行计划类型 #

当前使用Nested Loop，当返回行数较多时（如扩大日期范围后返回4074行），Hash Join效率更高。可临时关闭嵌套循环测试：

SET enable_nestloop = off;
-- 重新执行查询

若性能提升，可调整random_page_cost参数（降低该值会让优化器更倾向索引扫描），或在会话级别固定该设置。

4. 更新统计信息 #

确保优化器拥有最新表统计数据，生成更准确的执行计划：

ANALYZE booked_rooms;
ANALYZE bookings;

5. 评估并行扫描必要性 #

并行扫描的调度开销可能超过收益（尤其是小数据集场景），可尝试关闭并行扫描测试：

SET max_parallel_workers_per_gather = 0;
-- 重新执行查询

若性能提升，可全局调整该参数或针对单个查询设置。

6. 优化过滤顺序 #

先筛选bookings表的有效记录，再关联booked_rooms，减少关联数据量：

SELECT br.*
FROM booked_rooms br
JOIN (
    SELECT id FROM bookings WHERE status IN ('confirmed', 'modified')
) b ON br.booking_id = b.id
WHERE br.hotel_id = 935
  AND br.arrival_date <= '2022-08-01'
  AND br.departure_date >= '2022-07-01';

内容的提问来源于stack exchange，提问作者zauzaj