没有索引只扫描索引中的日期转换

2024-05-05 • 问答

我创建下表和索引：

CREATE TABLE test
(
 id bigint,d timestamp without time zone
);

CREATE INDEX f_date4
  ON public.test
  USING btree
  (date(d),id);

CREATE INDEX f_date5
  ON public.test
  USING btree
  (id,date(d));

我用数据填充表格并使用以下查询：

SELECT id,date(d)
FROM test
WHERE date(d) > '2019-09-20'::date;

EXPLAIN表明在条件d > '2019-09-20'::date中使用了f_date4索引，但我无法获得INDEX ONLY SCAN。可能的原因是什么，为什么会发生这种情况以及如何避免这种情况？

执行计划：

Index Scan using f_date4 on test  (cost=0.06..0.07 rows=1 width=12) (actual time=0.005..0.005 rows=0 loops=1)
Index Cond: (date(d) > '2019-01-20'::date)
Buffers: shared hit=2
Planning time: 0.131 ms
Execution time: 0.025 ms

我使用PostgreSQL 10.6

谢谢！

完整时间戳记上的索引可以与截断时间戳记使用相同的目的。

CREATE TABLE test
(
 id bigserial PRIMARY KEY,d timestamp without time zone NOT NULL
);

INSERT INTO test(d)
select gs FROM generate_series('2019-01-01'::timestamp,'2020-01-01'::timestamp,'4 hour':: interval) gs
        ;

CREATE INDEX f_date4
  ON test
  USING btree (d,id);

CREATE INDEX f_date5
  ON test
  USING btree (id,d);


VACUUM ANALYZE test;

EXPLAIN ANALYZE
SELECT id,d::date
FROM test
WHERE d >= '2019-09-21' -- NOTE: slightly changed condition
        ;

CREATE TABLE
INSERT 0 2191
CREATE INDEX
CREATE INDEX
VACUUM

结果查询计划：

                                                       QUERY PLAN                                                        
-------------------------------------------------------------------------------------------------------------------------
 Index Only Scan using f_date4 on test  (cost=0.28..17.32 rows=612 width=12) (actual time=0.025..0.160 rows=613 loops=1)
   Index Cond: (d >= '2019-09-21 00:00:00'::timestamp without time zone)
   Heap Fetches: 0
 Planning Time: 0.285 ms
 Execution Time: 0.218 ms
(5 rows)

docs说：

但是，PostgreSQL的计划者目前对这种情况不太聪明。

总结是，计划者的一部分认为它需要“ d”来计算date(d)，即使另一部分意识到它已经具有date(d)。要使这两个部分进行交流并不容易。

您可以建立索引(date(d),id,d)来解决此问题。

没有索引只扫描索引中的日期转换

dhy9062 回答：没有索引只扫描索引中的日期转换

大家都在问