Btree indexes, large numbers and <= comparisons

From: Toke Høiland-Jørgensen <toke(at)toke(dot)dk>
To: pgsql-general(at)postgresql(dot)org
Subject: Btree indexes, large numbers and <= comparisons
Date: 2007-03-29 09:43:05
Message-ID: 200703291143.05627.toke@toke.dk
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: 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

Responses

Browse pgsql-general by date

  From Date Subject
Next Message Alejandro D. Burne 2007-03-29 11:33:50 Re: Btree indexes, large numbers and <= comparisons
Previous Message A. Kretschmer 2007-03-29 08:03:32 Re: COPY command details