Toke =?utf-8?q?H=C3=B8iland-J=C3=B8rgensen?= <toke@xxxxxxx> 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
