No index only scan with date conversion in index

Question

I create the following table and indexes:

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));

I fill the table with data and use the following query:

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

EXPLAIN shows that f_date4 index is being used on condition d > '2019-09-20'::date, but i can't get INDEX ONLY SCAN. What are the possible reasons, why this happens and how to avoid this?

Execution plan:

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

I use postgresql 10.6

Thanks in advance!

@a_horse_with_no_name I have edited my question and added a plan. there are no more indexes. Its a test table, created for the purpose of question. It has 1000 rows. The same happens on the table with about 100 million rows. — Hgfdd
– Hgfdd, Commented Nov 6, 2019 at 11:35
Unrelated but why wouldn't you just do WHERE d > '2019-09-20'::TIMESTAMP, then you wouldn't need bother with all these date conversions. — 404
– 404, Commented Nov 6, 2019 at 11:48
@404 an index on a truncated timestamp does not make much sense, either. and WHERE d >= '2019-09-21' would suffice, IMHO. — wildplasser
– wildplasser, Commented Nov 6, 2019 at 11:54

wildplasser · Accepted Answer · 2019-11-06 12:36:33Z

An index on the complete timestamp can serve the same purposes as one the truncated timestamp.

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

Resulting query plan:

                                                       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)

jjanes · Accepted Answer · 2019-11-06 12:31:51Z

0

As the docs say:

However, PostgreSQL's planner is currently not very smart about such cases.

The summary is that one part of the planner thinks it needs "d" to compute date(d) even though another part realizes it already has date(d). Getting those two parts to communicate is not easy.

You can build the index (date(d), id,d) to get around this.

answered Nov 6, 2019 at 12:31

jjanes

44.9k5 gold badges39 silver badges48 bronze badges

Collectives™ on Stack Overflow

No index only scan with date conversion in index

2 Answers 2

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related