Re: PG 9.1 much slower than 8.2 ?

On Tue, Aug 26, 2014 at 10:10 AM, Marc Richter <mail@marc-richter.info> wrote:
> Hi everyone,
>
> I'm in the process of migrating a really old PostgreSQL DB from 8.2.5 to a
> (more) recent PostgreSQL 9.1. I know that 9.1 is somewhat old already, too,
> but since we are stuck to Debian stable and don't want to start using
> self-compiled software and this is the version which is included in Debian
> stable currently, this is the version of choice.
>
> 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.
>
> I know, this seems like near to nothing, but the hardware of the 9.1.13 host
> is way more recent than the one of the 8.2.5 PostgreSQL, too:
>
> PG Version 8.2.5:
> * CPU:  Intel Xeon CPU E5506 (4-Core 2,13 GHz)
> * RAM:  4 GB (2x2GB) DDR3 1066
> * Storage:
> System, SWAP und PostgreSQL Data:       RAID1 - ST3500320NS
>
> PG Version 9.1.13:
> * CPU:  AMD Opteron 4334 (6 Core 3,1 GHz)
> * RAM:  32 GB (4x8GB) DDR3 1600
> * Storage:
> System + SWAP:          RAID1 - ST1000DM003-1CH1
> PostgreSQL Data:        RAID1 - SD6SB1M2 (SSD)
>
> I know that PostgreSQL has little chance to optimize a query like this, when
> no logic and no index is used to lookup a result, but taking this into
> account, we would have expected that issuing the same, bad query on old
> hardware and newer hardware once, should deliver results on the better/newer
> hardware a lot faster than on the older one. Instead, we experience the
> opposite.

very possibly you are measuring hardware differences or something else
not related to the database itself.  do isolate that, fire up 8.2.5 on
the same server and run queries side by side.  Also on both sides be
sure to run the test several times (say, 10) and take the median
speed.  Better yet, use pgbench; feel free to supplement the stock
tpc-b with custom test of your choosing (even if select * from table).

merlin