Re: Btree indexes, large numbers and <= comparisons

From: Toke Høiland-Jørgensen <toke(at)toke(dot)dk>
To: Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us>
Cc: pgsql-general(at)postgresql(dot)org
Subject: Re: Btree indexes, large numbers and <= comparisons
Date: 2007-03-29 20:52:16
Message-ID: 200703292252.17344.toke@toke.dk
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-general


> You can't usefully use a two-column btree index for this. btree indexes
> are not magic, they're just ordered lists, and if you think about where
> the rows you want might fall in the sort order, you'll see that the two
> given constraints aren't helpful for constraining the indexscan: it'd
> have to scan every row up to range_start = 87654321, or every row after
> range_end = 87654321, depending on which is the first index column.
> (The btree lacks any way of using the fact that range_start <= range_end
> or that they're probably close together.)
>
> What you need is a different index type that's designed for this kind of
> query. The closest thing available in the stock Postgres distribution
> is the contrib/seg module, which can handle overlap/intersection of line
> segments as an indexable query on a GIST index. You'd store line
> segments representing your ranges in the index, and query using the
> "overlaps" operator. However the seg data type is probably not
> immediately useful to you because it only stores float4 internally,
> and you seem to want more precision than that. You'd need to make a
> modified flavor of seg that stores the endpoints with the same precision
> your range endpoint columns have.
>
> regards, tom lane

I'll look into it. Thank you :)

-Toke

In response to

Browse pgsql-general by date

  From Date Subject
Next Message Bryan Murphy 2007-03-29 21:39:58 Deleted Flag/Unique Constraint
Previous Message Ron Johnson 2007-03-29 20:25:09 Re: COPY command details