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

On Fri, Feb 24, 2006 at 09:02:04AM -0800, CG wrote:
> PostgreSQL 8.1.3
>
> I'm trying to collect some hard numbers to show just how much it degrades and
> over how long a time interval.
>
> All I have now is anecdotal evidence, and I was hoping to save myself some
> downtime by seeking advice early.

<snip>

> I implimented this yesterday, and the immediate effect was a fantastic return
> time for partial text searches in the sub-second range. By today, these queries
> take 10 minutes sometimes... There are about 134000 rows in the table.
>
> The table gets analyzed nightly. Should the frequency be more? There are about
> 1000 rows added a day, only about 30 or so rows removed, and nothing is ever
> updated. There's not that much turnover.

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?

Have a nice day,
--
Martijn van Oosterhout <kleptog(at)svana(dot)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.