--- Bernhard Weisshuhn <[EMAIL PROTECTED]> wrote:
> On Mon, Feb 27, 2006 at 10:27:20AM -0800, CG <[EMAIL PROTECTED]> wrote: > > > [...] I'd need to see if the space required for the varchar+btree tables > are > > comparible, better, or worse than the ltree+gist tables with regards to > size. > > Please test this, I'm guessing (hoping actually) that having bazillions of > combinations of 26 (or so) characters (ltree labels) might be consuming > less space than having bazillions of substings in the database. > > Or maybe some clever combination of both approaches? > > If you find out something interesting, please let me know. Performance using varchar+btree, breaking up the string into distinct letter groups >= 3 chars is slightly better. Size of the varchar search vector table table is much bigger.. Most of my fields are about 15-25 characters in length. Expect even bigger tables for longer fields. The size of the btree index is less. The time to bootstrap the data into the tables was significantly longer. I used two triggers, one that normalized the search field before insert, and another that inserted a breakdown row after the insert row. There's a recursive effect built-in to get down to the smallest unique element. I'm sticking with ltree and setting up a vacuum analyze on a cron to keep the searches snappy. Hope that helps you with your project! CG __________________________________________________ Do You Yahoo!? Tired of spam? Yahoo! Mail has the best spam protection around http://mail.yahoo.com ---------------------------(end of broadcast)--------------------------- TIP 3: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
