Re: ltree and ordering - what index?

ltree is part of contrib, right?

You probably need to define a functional index of some kind. How are you
querying now? IIRC you'll be doing something like region IN (ltree)?

On Sat, Sep 17, 2005 at 01:31:21PM +0200, hubert depesz lubaczewski wrote:
> 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

--
Jim C. Nasby, Sr. Engineering Consultant jnasby(at)pervasive(dot)com
Pervasive Software http://pervasive.com work: 512-231-6117
vcard: http://jim.nasby.net/pervasive.vcf cell: 512-569-9461