ltree and ordering - what index?

From: hubert depesz lubaczewski <depesz(at)gmail(dot)com>
To: PostgreSQL General <pgsql-general(at)postgresql(dot)org>
Subject: ltree and ordering - what index?
Date: 2005-09-17 11:31:21
Message-ID: 9e4684ce0509170431357c99f0@mail.gmail.com
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: 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

Responses

Browse pgsql-general by date

  From Date Subject
Next Message Tom Lane 2005-09-17 14:22:42 Re: Duplicate Values or Not?!
Previous Message David Goodenough 2005-09-17 08:36:30 Re: Anyone use Eclipse?