使用ORDERBY扫描查询时如何跳过分区

Question

我正在尝试用order by和limit查询按范围表分区的Postgres
有没有办法让它按顺序扫描分区，并在达到20的限制后跳过其余分区？

SELECT *
FROM gateway_samples_test
WHERE gateway_id = 14920 
ORDER BY timestamp DESC 
LIMIT 1

当在Postgres11.3:中运行时

Limit  (cost=2.39..1521.70 rows=1 width=411)
->  Merge Append  (cost=2.39..13445838.34 rows=8850 width=411)
Sort Key: gateway_samples_2022_8_28."timestamp" DESC
->  Index Scan Backward using gateway_samples_2022_8_28_timestamp_idx on gateway_samples_old  (cost=0.57..12393096.00 rows=8787 width=411)
Filter: (gateway_id = 14920)
->  Index Scan Backward using gateway_samples_2022_8_29_timestamp_idx on gateway_samples_2022_8_29  (cost=0.42..507283.89 rows=28 width=414)
Filter: (gateway_id = 14920)
->  Index Scan Backward using gateway_samples_2022_8_30_timestamp_idx on gateway_samples_2022_8_30  (cost=0.42..471569.21 rows=27 width=414)
Filter: (gateway_id = 14920)
->  Index Scan Backward using gateway_samples_2022_8_31_timestamp_idx on gateway_samples_2022_8_31  (cost=0.29..72649.94 rows=4 width=414)
Filter: (gateway_id = 14920)
->  Index Scan Backward using gateway_samples_2022_9_1_timestamp_idx on gateway_samples_2022_9_1  (cost=0.14..265.54 rows=1 width=974)
Filter: (gateway_id = 14920)
->  Index Scan Backward using gateway_samples_2022_9_2_timestamp_idx on gateway_samples_2022_9_2  (cost=0.14..265.54 rows=1 width=974)
Filter: (gateway_id = 14920)
->  Index Scan Backward using gateway_samples_2022_9_3_timestamp_idx on gateway_samples_2022_9_3  (cost=0.14..265.54 rows=1 width=974)
Filter: (gateway_id = 14920)
->  Index Scan Backward using gateway_samples_default_timestamp_idx on gateway_samples_default  (cost=0.14..265.54 rows=1 width=974)
Filter: (gateway_id = 14920)

当在Postgres12中运行时(这里第一个分区只包含数据的子集(：

Limit  (cost=271.63..271.63 rows=1 width=449)
->  Sort  (cost=271.63..271.78 rows=62 width=449)
Sort Key: gateway_samples_2022_8_28_test."timestamp" DESC
->  Append  (cost=0.42..271.32 rows=62 width=449)
->  Index Scan using gateway_samples_2022_8_28_test_gateway_id_idx on gateway_samples_old_test  (cost=0.42..109.85 rows=27 width=414)
Index Cond: (gateway_id = 14920)
->  Index Scan using gateway_samples_2022_8_29_test_gateway_id_idx on gateway_samples_2022_8_29_test  (cost=0.42..104.92 rows=26 width=414)
Index Cond: (gateway_id = 14920)
->  Bitmap Heap Scan on gateway_samples_2022_8_30_test  (cost=4.33..23.60 rows=5 width=414)
Recheck Cond: (gateway_id = 14920)
->  Bitmap Index Scan on gateway_samples_2022_8_30_test_gateway_id_idx  (cost=0.00..4.33 rows=5 width=0)
Index Cond: (gateway_id = 14920)
->  Index Scan using gateway_samples_2022_8_31_test_gateway_id_idx on gateway_samples_2022_8_31_test  (cost=0.14..8.16 rows=1 width=974)
Index Cond: (gateway_id = 14920)
->  Index Scan using gateway_samples_2022_9_1_test_gateway_id_idx on gateway_samples_2022_9_1_test  (cost=0.14..8.16 rows=1 width=974)
Index Cond: (gateway_id = 14920)
->  Index Scan using gateway_samples_2022_9_2_test_gateway_id_idx on gateway_samples_2022_9_2_test  (cost=0.14..8.16 rows=1 width=974)
Index Cond: (gateway_id = 14920)
->  Index Scan using gateway_samples_default_test_gateway_id_idx on gateway_samples_default_test  (cost=0.14..8.16 rows=1 width=974)
Index Cond: (gateway_id = 14920)

出于某种原因，我发现它最近没有使用时间戳索引。

Answer 1

v11中没有。

如果你有一个以"开头的索引，这将自动工作；时间戳"；，并且您至少升级到v12。

这似乎可以在没有索引的情况下工作(依次对每个分区进行排序，从范围的适当末端开始，直到它达到极限(，但我想没有人愿意实现这一点。

但是由于gateway_id = 14920条件的原因，您的新查询有所不同。它仍然可以使用时间戳索引按顺序扫描分区，但它认为在gateway_id上使用高度选择性索引会更快。您可以通过set enable_sort=off强制它使用时间戳索引，即使情况更糟。但更好的做法是制作一个可以同时满足这两种需求的新索引，(gateway_id, timestamp)