BUG #9833: daterange is not utilizing index correctly
От | shaharhd@gmail.com |
---|---|
Тема | BUG #9833: daterange is not utilizing index correctly |
Дата | |
Msg-id | 20140402211018.309.99248@wrigleys.postgresql.org обсуждение исходный текст |
Ответы |
Re: BUG #9833: daterange is not utilizing index correctly
|
Список | pgsql-bugs |
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?
В списке pgsql-bugs по дате отправления: