Postgres Pro
Downloads
Home   >   mailing lists

Thread: ltree and ordering - what index?

ltree and ordering - what index?

From
hubert depesz lubaczewski
Date:
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

Re: ltree and ordering - what index?

From
"Jim C. Nasby"
Date:
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@pervasive.com
Pervasive Software      http://pervasive.com    work: 512-231-6117
vcard: http://jim.nasby.net/pervasive.vcf       cell: 512-569-9461

Re: ltree and ordering - what index?

From
hubert depesz lubaczewski
Date:
On 9/18/05, Jim C. Nasby <jnasby@pervasive.com> wrote:
ltree is part of contrib, right?


yes.
 

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)?

i didn't thought about functional indices, but this might be a solution. have to think about it for a while
searching through ltree's is done using specific operators (>@).

depesz

Re: ltree and ordering - what index?

From
"Jim C. Nasby"
Date:
On Mon, Sep 19, 2005 at 07:23:54AM +0200, hubert depesz lubaczewski wrote:
> On 9/18/05, Jim C. Nasby <jnasby@pervasive.com> wrote:
> >
> > ltree is part of contrib, right?
>
>
>
> yes.
>
> 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)?
> >
>
> i didn't thought about functional indices, but this might be a solution.
> have to think about it for a while
> searching through ltree's is done using specific operators (>@).

Yes, which is something I don't think the indexing code can deal with.

IIRC you're looking for a way to index something that's in a specific
place in the ltree, so you'd want to index that actual function
expression.
--
Jim C. Nasby, Sr. Engineering Consultant      jnasby@pervasive.com
Pervasive Software      http://pervasive.com    work: 512-231-6117
vcard: http://jim.nasby.net/pervasive.vcf       cell: 512-569-9461