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?
|
Список | 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
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 по дате отправления: