2007/3/29, Toke Høiland-Jørgensen <toke@xxxxxxx>:
I have a table with ~5 million rows containing ranges of large (8-digit) numbers. The table has an int4 field for the range start and the range end, and a field which is null if that particular range is expired, and has a value otherwise. 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 My problem is that when I run a query like the above, the query planner does a sequential scan, even though i have an index on both the query columns separately, as well as an index containing both columns. The indexes are defined like this: CREATE INDEX range_start_end_index ON table_name USING btree (range_start, range_end) WHERE expired IS NULL CREATE INDEX range_start_index ON table_name USING btree (range_start) WHERE expired IS NULL CREATE INDEX range_end_index ON table_name USING btree (range_end) WHERE expired IS NULL When I do a query for smaller numbers (7-digit and below, as far as I can see), the query planner uses the index(es) and the query is instantaneous. However, when I run a query like the above, the planner decides to do a sequential scan of the entire table. I realize this probably has something to do with the planner only searching for the first part of the WHERE clause (i.e. range_start <= 87654321) and deciding that this will probably yield so many rows that a sequential scan will yield results that are just as good. However, the data is structured in such a way that multiple ranges containing the same number (and which are not expired) do not exist. So in reality there will be either 1 or 0 results for a query like the above. How do I make the query planner realize that using the index is a Good Thing(tm)? Any help will be greatly appreciated. Regards, -Toke
Can you send an explain analyze for that query? Alejandro
