Re: Btree indexes, large numbers and <= comparisons

> 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