Re: ltree + gist index performance degrades significantly over a night

--- Bernhard Weisshuhn <bkw(at)weisshuhn(dot)de> wrote:

> On Mon, Feb 27, 2006 at 10:27:20AM -0800, CG <cgg007(at)yahoo(dot)com> 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