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

Question

我创建了下表和索引:

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 显示 f_date4 索引正在条件 d > '2019-09-20'::date 上使用，但我无法获取 INDEX仅扫描。可能的原因是什么，为什么会发生这种情况以及如何避免这种情况？

执行计划:

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

提前致谢!

Answer 1

完整时间戳的索引可用于与截断时间戳相同的目的。

---

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)