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

On Fri, Feb 24, 2006 at 09:44:37AM -0800, CG wrote:
>
>
> --- Martijn van Oosterhout <kleptog@svana.org> wrote:
>
> > That's very odd. Like the other person said, do you vacuum and analyse?
> > But my question is: is it using the index? What does EXPLAIN / EXPLAIN
> > ANALYZE tell you?
>
> data=# explain select * from search where search_vector ~ '*.o.r.l.*'::lquery;

<snip>

> data=# explain analyze select * from search where search_vector ~
> '*.o.r.l.*'::lquery;

<snip>
>  Total runtime: 197000.061 ms

Ouch! The index is obviously not very good in this case. Unfortunatly
it's not clear where the slowdown is. You'd probably need to recompile
postgresql with profiling to find exactly where it's going wrong.

Quick test though, if you disable the index (set enable_indexscan=off)
so it does a seqscan, is it faster or slower? By how much? i.e. is it
helping much.

Have a nice day,
--
Martijn van Oosterhout   <kleptog@svana.org>   http://svana.org/kleptog/
> Patent. n. Genius is 5% inspiration and 95% perspiration. A patent is a
> tool for doing 5% of the work and then sitting around waiting for someone
> else to do the other 95% so you can sue them.