Re: PG 9.1 much slower than 8.2 ?

On Wed, Aug 27, 2014 at 2:55 AM, Thomas Kellerer <spam_eater(at)gmx(dot)net> wrote:

> Marc Richter schrieb am 26.08.2014 um 17:10:
> > I've managed to create a dump of the database from 8.2.5 and
> > inserting it into 9.1.13 successfully, thanks to the help of this
> > list ("Upgrading from PG 8.2.5 to 9.1.13" - Thread). So I gave the
> > result to another department to make their compatibility- and
> > overall-tests on it. They did not come up with incompatibilities, but
> > with a performance-related issue:
> >
> > When we do a "SELECT *" on a table with 355332 rows in it without
> > using an index or limit or such, this takes round about 10.5 seconds
> > on the PostgreSQL 8.2.5 host and 12.2 seconds on the PostgreSQL
> > 9.1.13 host. Both servers are using the same database.
>
> Can you share the output of explain analyze for both servers?
>
> (for 9.1 maybe even "explain (analyze true, verbose true, buffers true)
> select ...")
>
> Also: try to run a "vacuum full" on the 9.1 database - just to make sure
>
> Thomas
>
>
>
> --
> Sent via pgsql-novice mailing list (pgsql-novice(at)postgresql(dot)org)
> To make changes to your subscription:
> http://www.postgresql.org/mailpref/pgsql-novice
>

Actually, what may be more important than a vacuum full would be a full
database analyze.
Just run "ANALYZE" while logged into your database via psql. With no tables
given to the command, it should just analyze the whole thing. This should
update the planner statistics which are probably empty after a full
dump/restore.