Re: BUG #9833: daterange is not utilizing index correctly - Mailing list pgsql-bugs

On Fri, Apr 4, 2014 at 7:36 AM, Greg Stark <stark@mit.edu> wrote:

>
> On 3 Apr 2014 21:39, "bricklen" <bricklen@gmail.com> wrote:
> >
>
> > 1). [1978-07-15,1983-03-01) means "include 1978-07-15 and everything up
> to - but not including - 1983-03-01", whereas BETWEEN is inclusive.
>
> That means we can't simply rewrite the with as a BETWEEN clause. However
> conceptually btree could be extended to handle @> operators like this.
>
You might have over looked something.
When using a '[<some date>, <some date>]' range, postgres automatically
change it to '[<some date>, <some date> + 1)'
The explain shows it:
EXPLAIN ANALYZE SELECT *
FROM users
WHERE user_birthday <@ daterange('[1978-07-15,1983-03-02]')

results in:
"Seq Scan on users  (cost=0.00..53248.25 rows=11101 width=241) (actual
time=0.023..546.617 rows=209154 loops=1)"
"  Filter: (user_birthday <@ '[1978-07-15,1983-03-03)'::daterange)"
"  Rows Removed by Filter: 900946"
"Total runtime: 558.557 ms"

>

> This is a perfectly reasonable feature request but it's just not something
> btree can handle currently. Btree operator classes can handle a few
> specific operators <, <=, =, >=, and > and that's it I believe. Adding more
> is non trivial work and range data types are fairly new.
>
My assumption was, that due to the fact that a range has an upper and lower
limit, it would be more efficient than between, as the planner can always
assume that the upper limit is always equal or above the lower limit.
Wouldn't that make the index search much faster, as by the time you found
the index for the lower limit, you're search options for the upper limit
are limited and then easier to plan on.

> > 2). I haven't tested, but wouldn't you need to create a GiST index for
> <@ to use the index?
>
How do you do that? create a GiST index for a data field with <@ operator?
that's the relevant table:

CREATE TABLE users
(
  user_id bigserial NOT NULL,
  user_email text NOT NULL,
  user_password text,
  user_first_name text NOT NULL,
  user_middle_name text,
  user_last_name text NOT NULL,
  user_birthday date,
  CONSTRAINT pk_users PRIMARY KEY (user_id)
)

The users table has 1.1 million records with full randomness on the dates
between the dates: 1974-04-01 and 1998-04-03.
all I was able to do was create this, which wouldn't affect this query...

CREATE INDEX ix_users_birthday_gist
  ON glances.users
  USING gist
  (user_birthday)
  WHERE NOT user_birthday IS NULL;

>  > 3). Related to #2, have you tried installing the btree_gist extension
> to allow the query planner to use the index?
>
> Gist is more flexible about adding new operators. I don't know if that
> means it has this one though. If it does you would have to build a new
> index using the btree_gist operator class which is may not be worth it's
> weight in your database.
>
btree_gist is enabled and loaded, otherwise I wouldn't be able to create
the GiST index on user_birthday.
but as I said - it didn't had any affect.

Shahar