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?
