Thread: Query composite index range in an efficient way

Hi,

Let's say I have a table (tbl) with two columns: id1, id2.
I have an index on (id1,id2)
And I would like to query the (12;34) - (56;78) range (so it also may contain (12;58), (13;10), (40;80) etc.). With the index this can be done quite efficiently in theory, but I cannot find a way to make this happen. I triy this in the WHERE clause:

WHERE (id1>12 or id1=12 and id2>=34) and (id1<56 or id1=56 and id2<=78)

I created a big enough table (131072 records, and it had also a 3rd field with about 120 character text data).
But Postgres performs a SeqScan. I have analyzed the table before it.
I also tried Row constructors with a Between expression, but in this case Postgres handled the elements of the row independently, and this led to false query result.

What should I write in the Where clause to get Postgres to perform an IndexScan?

I would like to apply this to other datatypes also, not just ints.

Thanks in advance,
Otto

On Tue, 17 Feb 2009, Havasvölgyi Ottó wrote:
> I created a big enough table (131072 records, and it had also a 3rd
> field with about 120 character text data). But Postgres performs a
> SeqScan.

Firstly, you should always post EXPLAIN ANALYSE results when asking about
a planning problem.

Secondly, you can't "get" Postgres to choose a particular plan (without
disruptive fiddling with the planner). Postgres will try to choose the
plan that answers the query fastest, and this may be a sequential scan.

What happens if you use the following WHERE clause?

WHERE id1 > 12 AND id1 < 56

Does Postgres use a sequential scan then?

How many rows does your query return? If it's more than about 10% of the
total rows in the table, then a sequential scan is probably the fastest
method.

Matthew

--
 Matthew: That's one of things about Cambridge - all the roads keep changing
           names as you walk along them, like Hills Road in particular.
 Sagar:   Yes, Sidney Street is a bit like that too.
 Matthew: Sidney Street *is* Hills Road.

Havasvölgyi Ottó <havasvolgyi.otto@gmail.com> writes:

> I also tried Row constructors with a Between expression, but in this case
> Postgres handled the elements of the row independently, and this led to
> false query result.

What version of Postgres is this? row constructors were fixed a long time ago
to not do that and the main benefit of that was precisely that this type of
expression could use a multi-column index effectively.

--
  Gregory Stark
  EnterpriseDB          http://www.enterprisedb.com
  Ask me about EnterpriseDB's On-Demand Production Tuning

Gregory Stark <stark@enterprisedb.com> writes:
> Havasv�lgyi Ott� <havasvolgyi.otto@gmail.com> writes:
>> I also tried Row constructors with a Between expression, but in this case
>> Postgres handled the elements of the row independently, and this led to
>> false query result.

> What version of Postgres is this? row constructors were fixed a long time ago
> to not do that and the main benefit of that was precisely that this type of
> expression could use a multi-column index effectively.

That depends on whether you think 8.2 is "a long time ago" ;-).  But
yeah, row comparisons in a modern Postgres version are the way to handle
this.

            regards, tom lane

>>> Havasvölgyi Ottó <havasvolgyi.otto@gmail.com> wrote:

> WHERE (id1>12 or id1=12 and id2>=34)
>   and (id1<56 or id1=56 and id2<=78)

As others have pointed out, if you are using 8.2 or later, you should
write this as:

WHERE (id1, id2) >= (12, 34) and (id1, id2) <= (56, 78)

On earlier versions you might want to try the logically equivalent:

WHERE (id1 >= 12 and (id1 > 12 or id2 >= 34))
  and (id1 <= 56 and (id1 < 56 or id2 <= 78))

-Kevin

Thanks, it's a very good idea!
Otto