Re: Btree indexes, large numbers and <= comparisons

Toke =?utf-8?q?H=C3=B8iland-J=C3=B8rgensen?= <toke(at)toke(dot)dk> writes:
> I need to query this table to find a range containing a particular number,
> e.g. a query might look like this:

> SELECT * FROM table_name WHERE range_start <= 87654321 AND range_end >=
> 87654321 AND expired IS NULL

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