Btree indexes, large numbers and <= comparisons
От | Toke Høiland-Jørgensen |
---|---|
Тема | Btree indexes, large numbers and <= comparisons |
Дата | |
Msg-id | 200703291143.05627.toke@toke.dk обсуждение исходный текст |
Ответы |
Re: Btree indexes, large numbers and <= comparisons
Re: Btree indexes, large numbers and <= comparisons |
Список | pgsql-general |
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
В списке pgsql-general по дате отправления: