Re: BUG #9833: daterange is not utilizing index correctly - Mailing list pgsql-bugs
From | Shahar |
---|---|
Subject | Re: BUG #9833: daterange is not utilizing index correctly |
Date | |
Msg-id | CAHEK1RHLsqmPk2+_+xFpAc8D_v3XLT3fvkpskWwZeHc_U=QnYg@mail.gmail.com Whole thread Raw |
In response to | Re: BUG #9833: daterange is not utilizing index correctly (bricklen <bricklen@gmail.com>) |
List | pgsql-bugs |
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@gmail.com> wrote: > > 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? >
pgsql-bugs by date: