Re: BUG #9833: daterange is not utilizing index correctly

On Wed, Apr 2, 2014 at 2:10 PM, <shaharhd@gmail.com> wrote:

> The following bug has been logged on the website:
>
> Bug reference:      9833
> Logged by:          Shahar Hadas
> Email address:      shaharhd@gmail.com
> PostgreSQL version: 9.3.3
> Operating system:   Mac OSX - Postgres.app
> Description:
>
> Simple table which has a user_birthday field with a type of date (can be
> NULL value)
>
> there's an index (btree) defined on that field, with the rule of NOT
> user_birthday IS NULL.
>
> compare the following queries:
>
> explain analyze SELECT *
> FROM users
> WHERE user_birthday <@ daterange('[1978-07-15,1983-03-01)')
>
> explain analyze SELECT *
> FROM users
> WHERE user_birthday BETWEEN '1978-07-15'::date AND '1983-03-01'::date
>
> at first glance both should have the same execution plan, but for some
> reason, here are the results:
>
> "Seq Scan on users  (cost=0.00..52314.25 rows=11101 width=241) (actual
> time=0.014..478.983 rows=208886 loops=1)"
> "  Filter: (user_birthday <@ '[1978-07-15,1983-03-01)'::daterange)"
> "  Rows Removed by Filter: 901214"
> "Total runtime: 489.584 ms"
>
> "Bitmap Heap Scan on users  (cost=4468.01..46060.53 rows=210301 width=241)
> (actual time=57.104..489.785 rows=209019 loops=1)"
> "  Recheck Cond: ((user_birthday >= '1978-07-15'::date) AND (user_birthday
> <= '1983-03-01'::date))"
> "  Rows Removed by Index Recheck: 611375"
> "  ->  Bitmap Index Scan on ix_users_birthday  (cost=0.00..4415.44
> rows=210301 width=0) (actual time=54.621..54.621 rows=209019 loops=1)"
> "        Index Cond: ((user_birthday >= '1978-07-15'::date) AND
> (user_birthday <= '1983-03-01'::date))"
> "Total runtime: 500.983 ms"
>
> as you can see, the <@ daterange is not utilizing the existing index, while
> the between does.
>
> (note that the actual use case for this rule is in a more complex query,
> which doesn't result in the Recheck Cond and Bitmap Heap scan)
>
> Is this a bug? or how the daterange was designed to function?
>

Three things off the top of my head:
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.
2). I haven't tested, but wouldn't you need to create a GiST index for <@
to use the index?
3). Related to #2, have you tried installing the btree_gist extension to
allow the query planner to use the index?