主页 / dba / 问题 / 311345
Accepted
Lucas03
Asked: 2022-04-27 04:48:41 +0800 CST

PostgreSQL 中的简单查询优化（两表连接和排序）

  • 772

我有一个简单的查询应该返回有关预订的数据，但它行为不端，有时不会在 1 分钟内返回数据。

所以我有主要的预订表：

table booking_data
- bid bigint primary key
- user_id varchar(32)
(other columns not relevant here and there is index on user_id)

和带有助手数据的表，例如如果预订中有多个航班，我想将最后一个航班日期时间存储到助手表中：

table itinerary_timestamps
- bid references booking_data.bid
- last_segment_arrival_at    timestamp with time zone not null
(some other columns and index on last_segment_arrival_at)

这是简化的查询：

explain analyze select *
from booking_data join itinerary_timestamps using (bid)
where booking_data.user_id = 'dUZYLebTiZOBG1R3crKLhh'
  and itinerary_timestamps.last_segment_arrival_at < now()
order by itinerary_timestamps.last_segment_arrival_at desc
limit 11;

查询计划：

Limit  (cost=1.00..1253.62 rows=11 width=515) (actual time=7171.379..10008.565 rows=11 loops=1)
  ->  Nested Loop  (cost=1.00..17263562.69 rows=151602 width=515) (actual time=7171.378..10008.551 rows=11 loops=1)
        ->  Index Scan Backward using idx_itinerary_timestamps_last_segment_arrival_at on itinerary_timestamps  (cost=0.44..781981.48 rows=25695264 width=60) (actual time=0.014..1918.143 rows=1760471 loops=1)
              Index Cond: (last_segment_arrival_at < now())
        ->  Index Scan using booking_data_pkey on booking_data  (cost=0.56..0.64 rows=1 width=463) (actual time=0.004..0.004 rows=0 loops=1760471)
              Index Cond: (bid = itinerary_timestamps.bid)
              Filter: ((user_id)::text = 'dUZYLebTiZOBG1R3crKLhh'::text)
              Rows Removed by Filter: 1
Planning Time: 0.423 ms
Execution Time: 10008.630 ms

该用户的 booking_data 中有165867 个预订。itinerary_timestamps 表中有165862条记录，bid 属于 user_id，总共26424052条记录。

您对如何优化这个简单的查询有任何想法吗？

我正在考虑使用出价来过滤该表格，但它具有相似的结果（有时更好有时相同）：

explain analyze select *
from booking_data join itinerary_timestamps using (bid)
where booking_data.user_id = 'aQIDkXXEx3nWY5KvLSuEiK'
  and itinerary_timestamps.last_segment_arrival_at < now()
  and booking_data.bid in (select bid from booking_data where user_id='aQIDkXXEx3nWY5KvLSuEiK')
order by itinerary_timestamps.last_segment_arrival_at desc
limit 11;
postgresql query-performance

1 个回答

  1. Best Answer

    这可能是我们在这里看到的最常见的规划问题。规划器假设 165862 条合格记录随机分布在索引的 26424052 行中（好吧，它认为数字是 151602 和 25695264，因为它使用估计值而不是实际计数——但这并没有改变任何东西。）因此它认为可以在扫描索引的 11/165862 后提前停止。但实际上，这些客户记录缺少后来的时间戳，因此它最终需要遍历索引的 1760471/25695264 才能找到其中的 11 个，这比它想象的要多 1000 倍以上。

    假设记录是随机分散的，您无法做任何事情来让计划者停止。您可以做的是通过将 ORDER BY 更改为order by itinerary_timestamps.last_segment_arrival_at+interval '0 seconds' desc. 这意味着它将必须读取所有 165862 条记录并对其进行排序。这并不理想，但可能比目前的计划更好。

    如果它的性能足够重要，那么通过将 user_id 复制到 itinerary_timestamps 来进行非规范化可能是值得的，那么您只需要 (user_id, last_segment_arrival_at) 上的索引。

    • 3

相关问题