From: | Havasvölgyi Ottó <havasvolgyi(dot)otto(at)gmail(dot)com> |
---|---|
To: | pgsql-performance(at)postgresql(dot)org |
Subject: | Query composite index range in an efficient way |
Date: | 2009-02-17 12:44:48 |
Message-ID: | 34608c0c0902170444v7ac09d97h5ad971baa1dee673@mail.gmail.com |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-performance |
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
From | Date | Subject | |
---|---|---|---|
Next Message | Matthew Wakeling | 2009-02-17 12:56:54 | Re: Query composite index range in an efficient way |
Previous Message | Matthew Wakeling | 2009-02-17 11:45:43 | Re: suggestions for postgresql setup on Dell 2950 , PERC6i controller |