> 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
