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@xxxxxxxxxxxxx Pervasive Software http://pervasive.com work: 512-231-6117 vcard: http://jim.nasby.net/pervasive.vcf cell: 512-569-9461 ---------------------------(end of broadcast)--------------------------- TIP 6: explain analyze is your friend
