BUG #9833: daterange is not utilizing index correctly

From: shaharhd(at)gmail(dot)com
To: pgsql-bugs(at)postgresql(dot)org
Subject: BUG #9833: daterange is not utilizing index correctly
Date: 2014-04-02 21:10:18
Message-ID: 20140402211018.309.99248@wrigleys.postgresql.org
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-bugs

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?

Responses

Browse pgsql-bugs by date

  From Date Subject
Next Message Tom Lane 2014-04-02 21:13:14 Re: BUG #9518: temporary login failure - "missing pg_hba entry"
Previous Message John R Pierce 2014-04-02 20:46:41 Re: Configuring Standby Server in PostgreSQL 9.3.3