PostgreSQL gist index

Question

I have a table with two date like dateTo and dateFrom, i would like use daterange approach in queries and a gist index, but it seem doesn't work. The table looks like:

CREATE TABLE test (
  id         bigeserial,
  begin_date date,
  end_date   date
);
CREATE INDEX "idx1"
  ON test
  USING gist (daterange(begin_date, end_date));

Then when i try to explain a query like:

SELECT t.*
FROM test t
WHERE daterange(t.begin_date,t.end_date,'[]') && daterange('2015-12-30 00:00:00.0','2016-10-28 00:00:00.0','[]')

i get a Seq Scan.

Is this usage of gist index wrong, or is this scenario not feasible?

Why not a B-tree multicolumn index on begin_date and end_date? I think that you might get a seq scan if you have few rows. Maybe you need enough data to stress test your implementation. — andreim
– andreim, Commented Apr 18, 2017 at 15:53
You are trying to use an expression index with the wrong expression. You have daterange(begin_date, end_date) in the index, but you query your table with daterange(begin_date, end_date, '[]') (note the third parameter). — pozs
– pozs, Commented Apr 18, 2017 at 15:56
@andrei-macarie i'm using gist index because b-tree does not support range operators. — angcap
– angcap, Commented Apr 18, 2017 at 16:09
You need to provide the explain analyze with the seqscan, and also set enable_seqscan = 0; and the same explain analyze. — Evan Carroll
– Evan Carroll, Commented Apr 18, 2017 at 16:54

pozs · Accepted Answer · 2017-04-18 17:42:04Z

You have an index on the expression daterange(begin_date, end_date), but you query your table with daterange(begin_date, end_date, '[]') && .... PostgreSQL won't do math instead of you. To re-phrase your problem, it is like you're indexing (int_col + 2) and querying WHERE int_col + 1 > 2. Because the two expressions are different, the index will not be used in any circumstances. But as you can see, you can do the math (i.e. re-phrase the formula) sometimes.

You'll either need:

CREATE INDEX idx1 ON test USING gist (daterange(begin_date, end_date, '[]'));

Or:

CREATE INDEX idx2 ON test USING gist (daterange(begin_date, end_date + 1));

Note: both of them creates a range which includes end_date. The latter one uses the fact that daterange is discrete.

And use the following predicates for each of the indexes above:

WHERE daterange(begin_date, end_date, '[]') && daterange(?, ?, ?)

Or:

WHERE daterange(begin_date, end_date + 1) && daterange(?, ?, ?)

Note: the third parameter of the range constructor on the right side of && does not matter (in the context of index usage).

Collectives™ on Stack Overflow

PostgreSQL gist index

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related