Query composite index range in an efficient way

Поиск
Список
Период
Сортировка
От Havasvölgyi Ottó
Тема Query composite index range in an efficient way
Дата
Msg-id 34608c0c0902170444v7ac09d97h5ad971baa1dee673@mail.gmail.com
обсуждение исходный текст
Ответы Re: Query composite index range in an efficient way  (Matthew Wakeling <matthew@flymine.org>)
Re: Query composite index range in an efficient way  (Gregory Stark <stark@enterprisedb.com>)
Re: Query composite index range in an efficient way  ("Kevin Grittner" <Kevin.Grittner@wicourts.gov>)
Список 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

В списке pgsql-performance по дате отправления:

Предыдущее
От: Matthew Wakeling
Дата:
Сообщение: Re: suggestions for postgresql setup on Dell 2950 , PERC6i controller
Следующее
От: Matthew Wakeling
Дата:
Сообщение: Re: Query composite index range in an efficient way