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

Tsearch2 searches for whole words, and is designed with language in mind, yes?

I'm looking for consecutive characters in words or serial numbers, etc.

As for support, the same guys who wrote Tsearch2 wrote ltree. Can't go wrong
there!

Here's the solution to this problem: As usual, operator error. :(

For some reason I thought it would be a good idea to cluster the table on the
item_id index... What in the world was I thinking? When I clustered the search
table on the search_vector index (which makes the most sense, yes?) it seemed
to bring actual performance in-line with the type of performance I imagined
that I would receive.

I could probably get even better performance out of the table, at the cost of a
significant increase in table and index size, by chopping up the columns into
smaller chunks.

"Hello World" would yield

'h.e.l.l.o.w.o.r.l.d'
'e.l.l.o.w.o.r.l.d'
'l.l.o.w.o.r.l.d'
'l.o.w.o.r.l.d'
'o.w.o.r.l.d'
'w.o.r.l.d'
'o.r.l.d'
'r.l.d'

and using a wildcard search "search_vector ~ 'o.r.l.*'" would jump right to the
vectors which start with "o.r.l" ...

Thanks for all the responses! They did get my head pointed in the right
direction.

CG

--- "Jim C. Nasby" <jnasby(at)pervasive(dot)com> wrote:

> On Fri, Feb 24, 2006 at 09:02:04AM -0800, CG wrote:
> > I have a search table which I use for partial-match text searches:
> <snip>
> > For a string "Hello World" the ltree is created like 'h.e.l.l.o.w.o.r.l.d'
> ...
> > If I wanted to find all rows with "orl" in them i would construct an lquery
> > like '*.o.r.l.*' and use the "~" operator in the where clause. I would link
> to
> > the table "items" by the item_id ...
>
> Is there some reason you can't use tsearch2? I suspect it would probably
> work better; if nothing else you'd probably get better support since a
> lot more people use it.
> --
> 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
>

__________________________________________________
Do You Yahoo!?
Tired of spam? Yahoo! Mail has the best spam protection around
http://mail.yahoo.com