Re: PG 9.1 much slower than 8.2 ?

On Tue, Aug 26, 2014 at 11:10 AM, Marc Richter <mail(at)marc-richter(dot)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.
>
> Are we missing a "OMG - how can you even start a postgres without doing
> .... first???" step here? What else can be the reason for this?
>
> These are the postgres.conf - files in use:
>
> >>>>>>> PostgreSQL 8.2.5:
>
> listen_addresses = '*'
> max_connections = 100
> shared_buffers = 24MB
> max_fsm_pages = 153600
> datestyle = 'iso, dmy'
> lc_messages = 'de_DE.UTF-8'
> lc_monetary = 'de_DE.UTF-8'
> lc_numeric = 'de_DE.UTF-8'
> lc_time = 'de_DE.UTF-8'
>
> >>>>>>> PostgreSQL 9.1.13:
>
> data_directory = '/var/lib/postgresql/9.1/main'
> hba_file = '/etc/postgresql/9.1/main/pg_hba.conf'
> ident_file = '/etc/postgresql/9.1/main/pg_ident.conf'
> external_pid_file = '/var/run/postgresql/9.1-main.pid'
> listen_addresses = '*'
> port = 5432
> max_connections = 512
> unix_socket_directory = '/var/run/postgresql'
> ssl = true
> shared_buffers = 2048MB
> temp_buffers = 8MB
> work_mem = 256MB
> maintenance_work_mem = 1GB
> checkpoint_segments = 16
> effective_cache_size = 24GB
> log_destination = 'syslog'
> syslog_facility = 'LOCAL0'
> syslog_ident = 'postgres'
> client_min_messages = warning
> log_min_messages = notice
> log_min_error_statement = info
> log_line_prefix = '%m %r %u '
> log_statement = 'mod'
> datestyle = 'iso, mdy'
> lc_messages = 'de_DE.UTF-8'
> lc_monetary = 'de_DE.UTF-8'
> lc_numeric = 'de_DE.UTF-8'
> lc_time = 'de_DE.UTF-8'
> default_text_search_config = 'pg_catalog.english'
>
> Thanks for reading and your help in advance.
>
> Best regards,
> Marc
>
>
> --
> 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
>

I don't have a comment on the query performance at this time, but I just
wanted to point out that there is an apt repository maintained by the
PostgreSQL Global Development Group for debian based distros that contains
more recent packages of postgres

https://wiki.postgresql.org/wiki/Apt