postgresql 使用带有时区列的时间戳索引的SQL查询

vlf7wbxs  于 2023-06-22  发布在  PostgreSQL
关注(0)|答案(1)|浏览(168)

PostgreSQL我有这样的表:

create table if not exists some_table
               (
        some_id         varchar(40)              not null,      
        
        created_at      timestamp with time zone not null,
        
        constraint some_pkey
            primary key (some_id)

下一步我在created_at字段上创建索引

create index concurrently if not exists some_table__created_at
        on statement (created_at);

我无法使用条件在created_at上创建部分索引,因为此条件包含可变值
如果我像这样使用查询:

EXPLAIN ANALYSE
select t1.created_at
FROM some_table t1
where t1.created_at < '2023-06-19 10:17:20.830627+00:00';

索引扫描正在工作

Index Only Scan using statement__created_at on statement t1  (cost=0.42..5991.71 rows=210245 width=8) (actual time=0.016..27.510 rows=210277 loops=1)
  Index Cond: (created_at < '2023-06-19 10:17:20.830627+00'::timestamp with time zone)
  Heap Fetches: 0
Planning Time: 0.098 ms
Execution Time: 37.884 ms

但我想得到字段some_id与使用索引扫描

EXPLAIN ANALYSE
select t1.some_id
FROM statement t1
where t1.created_at < '2023-06-19 10:17:20.830627+00:00';

现在我得到了

Seq Scan on statement t1  (cost=0.00..6535.50 rows=210245 width=37) (actual time=0.007..33.904 rows=210277 loops=1)
  Filter: (created_at < '2023-06-19 10:17:20.830627+00'::timestamp with time zone)
  Rows Removed by Filter: 2723
Planning Time: 0.099 ms
Execution Time: 44.463 ms

我尝试在2列上使用index(some_id和created)。对我没什么帮助。
也许我应该使用not btree索引或其他东西?

snz8szmq

snz8szmq1#

为什么选择some_id字段时没有 index-only-scan?因为index-only-scan是在所有需要的数据都可以从索引中获取时执行的,所以实际的表根本不需要访问。当您选择some_id字段时,无论如何都必须访问实际的表来获取该字段的值。
在这种情况下,它取决于where子句的selectivity。如果计划器确定where子句的选择性不好,即它将选择大多数行,那么即使索引可用,执行顺序表扫描也是有意义的,因为如果之后几乎所有的表都必须被加载以从匹配的行中获取数据,则首先访问索引没有意义。
要验证这种行为,请确保表中有适当数量的行(也许有一百万行?),并在where子句中使用时间戳,只匹配其中的一小部分。您应该看到一个使用索引而不是执行顺序扫描的查询计划,尽管它不是仅索引扫描。

相关问题