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

I have a more elaborate documentation of the issue in :
http://stackoverflow.com/questions/22824314/postgresql-daterange-not-using-index-correctly

As per your questions:
1. Doesn't really make a difference - its just a one day range, different
dates will give the same result
2. Yes, tried to create a gist index on user_birthday, didn't have any
affect.
3. The btree_gist in loaded. It must be if you want to create a gist index
on a date field (simple types)

this time the email with Reply-All

On Thu, Apr 3, 2014 at 9:39 PM, bricklen <bricklen(at)gmail(dot)com> wrote:

>
> On Wed, Apr 2, 2014 at 2:10 PM, <shaharhd(at)gmail(dot)com> wrote:
>
>> The following bug has been logged on the website:
>>
>> Bug reference: 9833
>> Logged by: Shahar Hadas
>> Email address: shaharhd(at)gmail(dot)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?
>