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?
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 |