PostgreSQL范围查询索引

Question

假设我在PostgreSQL中有一个简单的表：

CREATE TABLE bingest.some_data (
report_date DATE NOT NULL,
client_id UUID NOT NULL,
value_1 INT, value_2 INT, value_3 INT,
value_4 INT, value_5 INT, value_6 INT,
value_7 INT, value_8 INT, value_9 INT,
value_10 INT, value_11 INT, value_12 INT,
value_13 INT, value_14 INT, value_15 INT,
value_16 INT, value_17 INT, value_18 INT,
value_19 INT,
PRIMARY KEY (report_date, client_id));

我想在下面的查询中使用索引

SELECT * FROM some_data WHERE report_date > '2018-10-30';

对于上面的查询，EXPLAIN命令会给我这个

Seq Scan on some_data  (cost=0.00..18.12 rows=217 width=96)
Filter: (report_date > '2018-10-30'::date)
Planning time: 0.061 ms
Execution time: 0.019 ms

当我指定的上限和下限时，会使用索引

SELECT * FROM some_data WHERE report_date > '2018-10-30' AND < '2019-10-30'

对于这个EXPLAIN给出以下输出：

Bitmap Heap Scan on some_data  (cost=4.18..11.30 rows=3 width=96)
Recheck Cond: ((report_date > '2018-10-30'::date) AND (report_date < '2019-10-30'::date))
->  Bitmap Index Scan on some_data_pkey  (cost=0.00..4.18 rows=3 width=0)
Index Cond: ((report_date > '2018-10-30'::date) AND (report_date < '2019-10-30'::date))
Planning time: 0.072 ms
Execution time: 0.027 ms

我还没有任何数据，但我想为这个表准备合适的索引。

Answer 1

如果一个表完全为空，PostgreSQL估计它包含10个页面(参见src/backend/optimizer/util/plancat.c中的estimate_rel_size(。

这样，如果一个表包含几行并且还没有被自动清空，我们就不会得到灾难性的低估。

结果是，对空表的估计完全是假的，你的观察没有任何意义。

若要测试是否可以使用索引，请将enable_seqscan设置为关闭并解释查询。如果它仍然选择昂贵的顺序扫描，它这样做是因为它不能使用索引。

Answer 2

对于空表，任何关于性能调优的讨论都是理论性的。要想真正确切地知道任何事情，您需要在表中放入大量数据，并使这些数据与您可以合理得出的生产数据集一样真实。

也就是说，在理论上。。。

表格扫描与索引

对于空表，最有效的执行计划通常是全表扫描。为什么？一个full的启动成本/开销非常低。

索引的使用开销很高(也就是说，无论找到什么，都要简单地搜索索引的基线成本(，但每行的成本极低。全表扫描开销极低，每行开销最大。话虽如此，但有两条经验法则：

• 表越小，索引的用处就越小
• 索引搜索的选择性越强，就越有用。搜索索引以匹配表中90%的行是非常低效的，而RDBMS通常足够聪明，不会这样做

估计和；统计

根据以上经验法则，RDBMS要决定哪种计划最有效，就需要知道数据的一些细节，特别是表中的行数，以及查询可能匹配的行数。

我们遇到了第二十二条军规，因为它需要查询数据，以了解有多少数据会返回，这样它才能选择最有效的查询计划。

因此，这一切的工作方式是RDBMS像Postgres一样存储有关数据的统计信息，供查询计划器使用。这些统计数据在特定时间点是准确的，而且是估计数。统计示例：

1. 表的近似行数
2. 对于索引，每个键的近似行数
3. 列中特定值的频率

再次记住，这些都是估计值。对于Postgres来说，保证统计数据在任何时候都非常精确是非常昂贵的，但我们不需要高精度来选择执行计划。1行表和2行表之间的区别是无关紧要的。但是，一行表与千行表或百万行表的对比非常重要。

这里有一些不错的阅读：https://www.postgresql.org/docs/9.6/static/planner-stats.html

摘要

尽管如此，Postgres可能不确定表中到底有多少行，但知道它很小。因此，何时使用索引的阈值很高，因此您的第一个查询会进行表扫描。对于第二次日期检查，它可能会认为查询在检查主键索引时会匹配较少的行数，因此它会选择索引。