Re: PG 9.1 much slower than 8.2 ? - Mailing list pgsql-novice

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@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-novice