From: | Chris Browne <cbbrowne(at)acm(dot)org> |
---|---|
To: | pgsql-hackers(at)postgresql(dot)org |
Subject: | Re: Range Types - efficiency |
Date: | 2011-02-09 23:07:14 |
Message-ID: | 87y65oyfh9.fsf@cbbrowne.afilias-int.info |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-hackers |
pgsql(at)j-davis(dot)com (Jeff Davis) writes:
> On Wed, 2011-02-09 at 16:20 -0500, Chris Browne wrote:
>> rangetest(at)localhost-> explain analyze select * from some_data where '[2010-01-01,2010-02-01)'::daterange @> whensit;
>> QUERY PLAN
>> ---------------------------------------------------------------------------------------------------------
>> Seq Scan on some_data (cost=0.00..634.00 rows=1 width=8) (actual time=1.045..111.739 rows=390 loops=1)
>> Filter: ('[ 2010-01-01, 2010-02-01 )'::daterange @> whensit)
>> Total runtime: 111.780 ms
>> (3 rows)
>>
>> This, alas, reverts to a seq scan on the table, rather than restricting
>> itself to the tuples of interest.
>>
>> I realize that, after a fashion, I'm using this backwards. But when I'm
>> doing temporal stuff, that tends to be the pattern:
>
> Yes. The index is a btree index on a normal column, so range types can't
> exactly help with that directly -- except maybe as a rewrite like you
> say.
>
> One thing you might try is a functional index on (range(whensit)) and
> then do: where '...' @> range(whensit).
>
> Does that work for you?
That doesn't appear to actually help:
rangetest(at)localhost-> create index i2 on some_data (range(whensit));
CREATE INDEX
rangetest(at)localhost-> explain analyze select * from some_data where '[2010-01-01,2010-02-01)'::daterange @> range(whensit);
QUERY PLAN
-------------------------------------------------------------------------------------------------------------
Seq Scan on some_data (cost=0.00..727.60 rows=12480 width=8) (actual time=1.030..110.542 rows=390 loops=1)
Filter: ('[ 2010-01-01, 2010-02-01 )'::daterange @> range(whensit))
Total runtime: 110.585 ms
(3 rows)
In any case, I suggest that as a "couple steps down the road" thing, it
would be desirable to have that query rewrite. Seems like a reasonable
ToDo item to consider for the future, if not in the first deployment.
Maybe that's something to add in 9.2 CommitFest #3! :-)
--
"There isn't any reason why Linux can't be implemented as an
enterprise computing solution. Find out what you've been missing
while you've been rebooting Windows NT." - Infoworld
From | Date | Subject | |
---|---|---|---|
Next Message | Nicolas Barbier | 2011-02-09 23:30:50 | Re: query execution question |
Previous Message | Jeff Davis | 2011-02-09 22:58:49 | Re: Range Types - efficiency |