RE: SELECT performance drop v 6.5 -> 7.0.3

[snip]

> We've reluctantly switched back to v6.5 in order to recover a workable db
> interface. We can just about still handle a db update over the week end...
>
> However with the current rate of data growth, this will very soon prove
> impossible. We also regret not being able to take advantage of the
> significant other improvements made in v7. Above all we're mystified by
> what has happended in v7 and how come we seem the only ones to notice the
> change. Maybe we're the only ones silly enough to think such a hefty db
> can
> live on a modest Linux box...
>
> Any suggestions? Anyone else noticed this dramatic change from v6.5 to
> v7.0.3?
>
A couple of thoughts / suggestions. First I assume that after you
imported the data to performed a vacuum analyze. If that is true it would
help us to have an explain of the queries from both 6.5 and 7.0, so please
post them (and include the data structure and any other relevant information
so we can help). There are some differences between 7.0 and 6.5 that can
cause the planner to make different decisions. I would imagine that the
speed issue can be resolved fairly easily. Also, you said you used 7.0.2,
you should use 7.0.3

As for the import process taking so long, you might want to try
turning off fsync during the import. 7.1 improves the fsync on performance
but it's still in beta. Dropping non-required indexes before doing the
import then re-creating them after import will also help speed it up.
Always make sure you vacuum analyze it after.

Matt