From: | Jeff Davis <pgsql(at)j-davis(dot)com> |
---|---|
To: | Chris Browne <cbbrowne(at)acm(dot)org> |
Cc: | pgsql-hackers(at)postgresql(dot)org |
Subject: | Re: Range Types - efficiency |
Date: | 2011-02-09 22:58:49 |
Message-ID: | 1297292329.1747.6.camel@jdavis-ux.asterdata.local |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-hackers |
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?
Regards,
Jeff Davis
From | Date | Subject | |
---|---|---|---|
Next Message | Chris Browne | 2011-02-09 23:07:14 | Re: Range Types - efficiency |
Previous Message | Peter Eisentraut | 2011-02-09 22:22:04 | Re: pl/python explicit subtransactions |