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
---------------------------(end of broadcast)---------------------------
TIP 9: In versions below 8.0, the planner will ignore your desire to
choose an index scan if your joining column's datatypes do not
match
