ltree and ordering - what index?

Поиск
Список
Период
Сортировка
От hubert depesz lubaczewski
Тема ltree and ordering - what index?
Дата
Msg-id 9e4684ce0509170431357c99f0@mail.gmail.com
обсуждение исходный текст
Ответы Re: ltree and ordering - what index?  ("Jim C. Nasby" <jnasby@pervasive.com>)
Список pgsql-general
hi
i have a table with more or less this structure:


id (serial)
category (ltree)
region (ltree[])
price (integer)
title (text)
entered (timestamptz)

now.
i would like to be able to search by region and category, but order using price, title or entered.

previous design was quite simple:

id (serial)
category (integer)
region (integer)
price (integer)
title (text)
entered (timestamptz)


and then having index on category, region, price allowed me to do:
select * from table where category = xxx and region = yyy order by category, region, price limit 10;
which worked blazingly fast.

but what do i do when i store category and region information as ltrees?
what indices to use? how to build a query?

any help?
any more information i should give?
we're yusing postgresql 8.0.3 (and thinking about thinking about testing 8.1beta).

depesz

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

Предыдущее
От: David Goodenough
Дата:
Сообщение: Re: Anyone use Eclipse?
Следующее
От: cbeahm@gmail.com
Дата:
Сообщение: Contraint Trigger Permissions