Обсуждение: PG 9.1 much slower than 8.2 ?

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

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

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

Hey Keith,

thanks for pointing me to this. I have removed the Debian postgres 9.1
packages, inserted the repo of PGDG and installed PostgreSQL 9.3
packages from there.

Now, what I get is this:

root@prod-cl4:/etc/postgresql/9.3/main# /etc/init.d/postgresql start
[....] Starting PostgreSQL 9.3 database server: main[....] Error: could
not exec /usr/lib/postgresql/9.3/bin/pg_ctl
/usr/lib/postgresql/9.3/bin/pg_ctl start -D /var/lib/postgresql/9.3/main
-s -o -c config_fi[FAILetc/postgresql/9.3/main/postgresql.conf" : ...
failed!
  failed!
root@prod-cl4:/etc/postgresql/9.3/main# /usr/lib/postgresql/9.3/bin/pg_ctl
/usr/lib/postgresql/9.3/bin/pg_ctl: symbol lookup error:
/usr/lib/postgresql/9.3/bin/pg_ctl: undefined symbol: PQping
root@prod-cl4:/etc/postgresql/9.3/main#

I cannot find something what seems related using Google ... what am I
doing wrong here?

Best regards,
Marc

Am 26.08.2014 17:42, schrieb Keith:
>
>
>
> On Tue, Aug 26, 2014 at 11:10 AM, Marc Richter <mail@marc-richter.info
> <mailto: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.
>
>     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
>     <mailto:pgsql-novice@postgresql.org>)
>     To make changes to your subscription:
>     http://www.postgresql.org/__mailpref/pgsql-novice
>     <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

This is not what I'm doing here: I stoped PostgreSQL 9.1, uninstalled it
and removed it's data folder at /var/lib/postgresql/9.1 completely.
Now it is completely empty.

Also, just trying to run "/usr/lib/postgresql/9.3/bin/pg_ctl" without
any parameters normally prints a usage overview like the following:

root@prod-cl3:/etc/postgresql# /usr/lib/postgresql/9.1/bin/pg_ctl
pg_ctl: cannot be run as root
Please log in (using, e.g., "su") as the (unprivileged) user that will
own the server process.
root@prod-cl3:/etc/postgresql#

The data stor isn't involved in this yet. instead I get this "symbol
lookup error: undefined symbol: PQping".

I tried to purge all packages from the PGDG Repo and tried to reinstall
9.1 from Debian repo. Now I get the same issue with these Packages, too.

*sigh* ... I'm taking the "Restart from scratch" - road now, focusing in
the initial performance-issue this time :/

Am 27.08.2014 17:24, schrieb Keith:
> You cannot use your old 9.1 cluster with 9.3. You either have to redo
> your dump and restore using a newly initialized cluster or perform a
> pg_upgrade on the 9.1 cluster. I'd recommend going with the dump and
> restore since it's much easier for those new to Postgres.
>
>
> On Wed, Aug 27, 2014 at 11:18 AM, Marc Richter <mail@marc-richter.info
> <mailto:mail@marc-richter.info>> wrote:
>
>     Hey Keith,
>
>     thanks for pointing me to this. I have removed the Debian postgres
>     9.1 packages, inserted the repo of PGDG and installed PostgreSQL 9.3
>     packages from there.
>
>     Now, what I get is this:
>
>     root@prod-cl4:/etc/postgresql/9.3/main# /etc/init.d/postgresql start
>     [....] Starting PostgreSQL 9.3 database server: main[....] Error:
>     could not exec /usr/lib/postgresql/9.3/bin/pg_ctl
>     /usr/lib/postgresql/9.3/bin/pg_ctl start -D
>     /var/lib/postgresql/9.3/main -s -o -c
>     config_fi[FAILetc/postgresql/9.3/main/postgresql.conf" : ... failed!
>       failed!
>     root@prod-cl4:/etc/postgresql/9.3/main#
>     /usr/lib/postgresql/9.3/bin/pg_ctl
>     /usr/lib/postgresql/9.3/bin/pg_ctl: symbol lookup error:
>     /usr/lib/postgresql/9.3/bin/pg_ctl: undefined symbol: PQping
>     root@prod-cl4:/etc/postgresql/9.3/main#
>
>     I cannot find something what seems related using Google ... what am
>     I doing wrong here?
>
>     Best regards,
>     Marc
>
>     Am 26.08.2014 17 <tel:26.08.2014%2017>:42, schrieb Keith:
>
>
>
>
>         On Tue, Aug 26, 2014 at 11:10 AM, Marc Richter
>         <mail@marc-richter.info <mailto:mail@marc-richter.info>
>         <mailto:mail@marc-richter.info
>         <mailto: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.
>
>              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 <mailto:pgsql-novice@postgresql.org>
>              <mailto:pgsql-novice@__postgresql.org
>         <mailto:pgsql-novice@postgresql.org>>)
>
>              To make changes to your subscription:
>         http://www.postgresql.org/____mailpref/pgsql-novice
>         <http://www.postgresql.org/__mailpref/pgsql-novice>
>
>              <http://www.postgresql.org/__mailpref/pgsql-novice
>         <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
>         <https://wiki.postgresql.org/wiki/Apt>
>
>
>
>     --
>     Sent via pgsql-novice mailing list (pgsql-novice@postgresql.org
>     <mailto:pgsql-novice@postgresql.org>)
>     To make changes to your subscription:
>     http://www.postgresql.org/__mailpref/pgsql-novice
>     <http://www.postgresql.org/mailpref/pgsql-novice>
>
>

Hi everyone,

as Merlin suggested, I've spend the last days with bringing PostgreSQL
versions 8.2.5 and 9.3.5 on the same hardware and re-ran my tests with this.

The Hardware-Specs are already told in the initial mail, quoted below
(under "PG Version 9.1.13"). Both are using the SSD-Raid for storing
their data.
Version 8.2.5 was compiled by hand (using "./configure
--prefix=/usr/local --with-openssl --enable-thread-safety"), version
9.3.5 was taken from PGDG Repo (https://wiki.postgresql.org/wiki/Apt).
PostgreSQL 8.2.5 binds to Port 5433, 9.3.5 binds to the default of 5432.

I've used a dump of one of our production sites, which results in ~12 GB
of storage used in PostgreSQL Data Directory.

Here's where the first sign of low speed of 9.3.5 shows up:

These are the lines I used to insert my Dump into both Postgres DBs,
using the "matching" version of psql of the two versions. I ran these
one after another to not interfere each other in performance. This is
dedicated, yet unproductive labor-hardware, so there is nothing else
that may interfere:

sync ; sleep 10 ; time zcat /usr/src/db.gz | \
/usr/local/bin/psql -U postgres -h 127.0.0.1 -p 5433
[...]
real    37m57.023s
user    7m9.683s
sys     1m15.613s

sync ; sleep 10 ; time zcat /usr/src/db.gz | \
/usr/bin/psql -U postgres -h 127.0.0.1 -p 5432
[...]
real    80m57.667s
user    89m45.597s
sys     1m57.479s

So, inserting the Dump takes more than twice the time in 9.3 than it
takes to do the same in 8.2 .

After this, I issued "SELECT *" on a table, containing 360881 rows in
both versions and took the time for this. I did this with the following
"for", three times each:

for x in 1 2 3 ; do
   sync
   sleep 10
   time /usr/local/bin/psql -U postgres -h 127.0.0.1 -p 5433 -c \
   "SELECT * FROM billing_events;" db | wc -l
done

This is the "for" for 8.2; in 9.3 I used Port 5432 instead of 5433 and
/usr/bin/psql instead of /usr/local/bin/psql.

These are the results for 8.2:
360881
real    0m6.044s
user    0m4.428s
sys     0m0.692s

360881
real    0m5.916s
user    0m4.476s
sys     0m0.596s

360881
real    0m6.023s
user    0m4.520s
sys     0m0.656s

These are the results for 9.3:
360881
real    0m12.885s
user    0m9.741s
sys     0m0.652s

360881
real    0m12.679s
user    0m9.613s
sys     0m0.724s

360881
real    0m12.717s
user    0m9.749s
sys     0m0.616s

As you can see: PostgreSQL 9.3 takes round about the double amount of
time to return these results than 8.2 needs to; quite the same
dimensions like the initial Insert does.

The config is like the following:

PG 8.2.5:
listen_addresses = '*'
port = 5433
max_connections = 512
shared_buffers = 400MB
temp_buffers = 8MB
work_mem = 256MB
maintenance_work_mem = 256MB
max_fsm_pages = 204800
checkpoint_segments = 16
effective_cache_size = 6GB
log_destination = 'stderr'
redirect_stderr = on
log_directory = 'pg_log'
log_filename = 'postgresql-%Y-%m-%d_%H%M%S.log'
log_rotation_age = 1d
log_rotation_size = 100MB
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'
stats_start_collector = on
stats_row_level = on
autovacuum = on
autovacuum_naptime = 1min
autovacuum_vacuum_threshold = 500
autovacuum_analyze_threshold = 250
autovacuum_vacuum_scale_factor = 0.2
autovacuum_analyze_scale_factor = 0.1
autovacuum_freeze_max_age = 200000000
autovacuum_vacuum_cost_delay = -1
autovacuum_vacuum_cost_limit = -1
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'
regex_flavor = advanced

PG 9.3.5
data_directory = '/var/lib/postgresql/9.3/main'
hba_file = '/etc/postgresql/9.3/main/pg_hba.conf'
ident_file = '/etc/postgresql/9.3/main/pg_ident.conf'
external_pid_file = '/var/run/postgresql/9.3-main.pid'
listen_addresses = '*'
port = 5432
max_connections = 512
unix_socket_directories = '/var/run/postgresql'
ssl = true
ssl_cert_file = '/etc/ssl/certs/ssl-cert-snakeoil.pem'
ssl_key_file = '/etc/ssl/private/ssl-cert-snakeoil.key'
shared_buffers = 4GB
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'
log_timezone = 'localtime'
datestyle = 'iso, dmy'
timezone = 'localtime'
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.german'

I hope I measured valid things and delivered all information properly
for everyone to comprehend.

Can anybody explain what else to try or why recent Postgres is slower
than quite exactly 7 years old 8.2.5 Release (Release Date: 2007-09-17)
this noticeable?

Best regards,
Marc

Am 26.08.2014 21:57, schrieb Merlin Moncure:
> 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
>
>

Hi Thomas and Keith,

After I have re-tested the performance after a plain insert of the dump
without any vacuuming or reindexing, I just have executed the following
on both PostgreSQL versions. I know, REINDEXING after a VACUUM FULL is
only suggested with pre-9.0 versions, but I haven't heard of any harm
doing it with >=9.0 versions, either, so I executed them there as well:

a) VACUUM FULL:
for table in $(/usr/local/bin/psql -U postgres -h 127.0.0.1 -p 5433 db
-c "\dt" | tail -n +4 | head -n -2 | cut -d '|' -f 2 | sed 's#^ ##g' |
expand | sed 's# \+##g'); do
   /usr/local/bin/psql -U postgres -h 127.0.0.1 -p 5433 -c "VACUUM FULL
VERBOSE ${table};" db
done

b) REINDEX TABLE:
for table in $(/usr/local/bin/psql -U postgres -h 127.0.0.1 -p 5433 db
-c "\dt" | tail -n +4 | head -n -2 | cut -d '|' -f 2 | sed 's#^ ##g' |
expand | sed 's# \+##g'); do
   /usr/local/bin/psql -U postgres -h 127.0.0.1 -p 5433 -c "REINDEX
TABLE ${table};" db
done

c) REINDEX DATABASE:
/usr/local/bin/psql -U postgres -h 127.0.0.1 -p 5433 -c "REINDEX
DATABASE db;" db

I did all these commands on the 9.3 Postgres as well by replacing Port
5433 with 5432 and /usr/local/bin/psql with /usr/bin/psql .

After this, I re-issued the test to selecting a 360881 rowed table again
without getting different results:

for x in 1 2 3 ; do
   sync
   sleep 10
   time /usr/local/bin/psql -U postgres -h 127.0.0.1 -p 5433 -c \
   "SELECT * FROM billing_events;" db | wc -l
done

Again, I used Port 5432 instead of 5433 and /usr/bin/psql instead of
/usr/local/bin/psql for testing the 9.3 Postgres:

8.2:
360881
real    0m5.996s
user    0m4.448s
sys     0m0.724s

360881
real    0m6.023s
user    0m4.520s
sys     0m0.664s

360881
real    0m6.077s
user    0m4.580s
sys     0m0.664s


9.3:
360881
real    0m12.835s
user    0m9.737s
sys     0m0.708s

360881
real    0m12.689s
user    0m9.685s
sys     0m0.652s

360881
real    0m12.700s
user    0m9.649s
sys     0m0.700s

After this, I ran "ANALYSE;" while connected to the DB "db" without any
further arguments, as Keith suggested. It echoed nothing but "ANALYZE"
after a few seconds on both psql shells.
After this, I ran the "SELECT *" again, identically with to what is
described above.
The result is still the same: 9.3 needs twice the time of 8.2 to return
the results.

As you asked for, here comes the "EXPLAIN ANALYSE" output for 8.2:

             QUERY PLAN

-------------------------------------------------------------------------------------------------------------------------
  Seq Scan on billing_events  (cost=0.00..16098.40 rows=360940
width=316) (actual time=0.015..84.507 rows=360877 loops=1)
  Total runtime: 114.922 ms
(2 rows)


... followed by 9.3 output for "EXPLAIN ANALYSE":

             QUERY PLAN

-------------------------------------------------------------------------------------------------------------------------
  Seq Scan on billing_events  (cost=0.00..15409.77 rows=360877
width=302) (actual time=0.035..97.698 rows=360877 loops=1)
  Total runtime: 128.252 ms
(2 Zeilen)

... followed by 9.3 output for "EXPLAIN (analyze true, verbose true,
buffers true) select ...":

             QUERY PLAN

--------------------------------------------------------------------------------------------------------------------------------------------------------------------
  Seq Scan on public.billing_events  (cost=0.00..15409.77 rows=360877
width=302) (actual time=0.033..96.809 rows=360877 loops=1)
    Output: id, callid, name, type, callingnumber, callednumber,
translatednumber, inserted, eventstart, duration, freeofchargeflag,
eventdata, envoxid, cpc, taskid
    Buffers: shared hit=11801
  Total runtime: 130.506 ms
(4 Zeilen)

These don't me tell anything. Do they help you understanding this issue?

Best regards,
Marc

Am 27.08.2014 17:00, schrieb Keith:
>
>
>
> On Wed, Aug 27, 2014 at 2:55 AM, Thomas Kellerer <spam_eater@gmx.net
> <mailto:spam_eater@gmx.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@postgresql.org
>     <mailto:pgsql-novice@postgresql.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.

> After this, I ran "ANALYSE;" while connected to the DB "db" without any further arguments, as Keith suggested.
> It echoed nothing but "ANALYZE" after a few seconds on both psql shells.
> After this, I ran the "SELECT *" again, identically with to what is described above.

This statement:

> The result is still the same: 9.3 needs twice the time of 8.2 to return the results.

doesn't match the results of explain analyze:

> "EXPLAIN ANALYSE" output for 8.2:
>  Total runtime: 114.922 ms

vs.

> ... followed by 9.3 output for "EXPLAIN ANALYSE":
>  Total runtime: 128.252 ms

So it took 114ms on 8.2 and 128ms on 9.3.  That's hardly "twice as long".

My naive interpretation of that (not really knowing Linux) would be that the "time" command adds additional overhead
that. 

One thing I also noticed:

the 8.2 psql seems to be in an english environment (because of the "(2 rows)" feedback), whereas 9.3 seems to be a
germanenvironment (because of the "(2 Zeilen)" psql feedback). I wonder if different locales can make a difference -
althoughI there is no string comparison involved in your query. 

Thomas

Hey Thomas,

thank you for answering.

You are right: Comparing values of what Postgres tells about it's
performance isn't worth noticing really.
But the Linux command "time" does nothing else than measuring how long
the command executed takes to complete; it is quite the same as hiting
ENTER and a stop watche's button at the same time, just more accurate
since human reaction time isn't sophisticating results.

Also, since "time" is used on both, PostgreSQL 8.2 and 9.3 commands, it
is hardly the reason for the longer execution times.
To further prove that, I just ran tests for both PostgreSQL versions
using "time" and the same psql - client (the one of 9.3), to make sure
minimalistic differences in the output of the two client versions
doesn't confuse time measurement. Also, I remove "wc -l" from command
pipe, which just count the lines from the output of the psql client and
redirect the output to /dev/null to make sure this program doesn't
affect the measurement:

for x in 1 2 3 ; do
   sync
   sleep 2
   time /usr/bin/psql -U postgres -h 127.0.0.1 -p 5433 -c \
     "SELECT * FROM billing_events;" db >/dev/null
done

Postgres 8.2:

real    0m10.086s
user    0m8.601s
sys     0m0.388s

real    0m10.116s
user    0m8.625s
sys     0m0.388s


real    0m10.030s
user    0m8.513s
sys     0m0.416s

Postgres 9.3:

real    0m12.600s
user    0m9.549s
sys     0m0.428s


real    0m12.552s
user    0m9.569s
sys     0m0.380s

real    0m12.614s
user    0m9.601s
sys     0m0.392s

Well, this alone is quite odd: It seems as if the psql client shiped
with Postgres 9.3 is slowing down the response of Postgres 8.2 server by
4 seconds; which is 66,67 percent (!) slower than 6 seconds.

But to remove the possible slowdown "time" might bring in, I remove this
command as well and run "date" right before and after the psql command
instead, which doesn't affect psql at all, but only prints the current
date and time:

for x in 1 2 3 ; do
   sync
   sleep 2
   date
   /usr/bin/psql -U postgres -h 127.0.0.1 -p 5433 -c \
     "SELECT * FROM billing_events;" db >/dev/null
   date
done

Postgres 8.2:

Do 4. Sep 15:03:40 CEST 2014
Do 4. Sep 15:03:50 CEST 2014

Do 4. Sep 15:03:52 CEST 2014
Do 4. Sep 15:04:02 CEST 2014

Do 4. Sep 15:04:04 CEST 2014
Do 4. Sep 15:04:14 CEST 2014

Postgres 9.3:

Do 4. Sep 15:05:37 CEST 2014
Do 4. Sep 15:05:49 CEST 2014

Do 4. Sep 15:05:51 CEST 2014
Do 4. Sep 15:06:04 CEST 2014

Do 4. Sep 15:06:06 CEST 2014
Do 4. Sep 15:06:18 CEST 2014

As you can see from this measure, without "time" it takes psql 10
seconds to read and print the values, too for Postgres 8.2 and 12
seconds with Postgres 9.3.

So, I come to the result:

1) The majority of the issue may consist in psql client and not in
PostgreSQL Server, since the newer psql client delivers the results of a
PostgreSQL 8.2 server a lot slower than the 8.2 client.

2) There is still a difference of ~2 seconds between the different
server versions, which is 20 percent slower than older PostgreSQL.

Thus, the issue remains, but involves the psql client, additionally.

You also mentioned the german locale in PostgreSQL 9.3 response.
I looked at the result, psql 9.3 prints from both server versions and
both are german. So, not the Server seems to print the result in german,
but the client does.
Nevertheless, I changed the following settings:

In PostgreSQL 9.3's postgresql.conf:

from:
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.german'

to:
lc_messages = 'en_US.UTF-8'
lc_monetary = 'en_US.UTF-8'
lc_numeric = 'en_US.UTF-8'
lc_time = 'en_US.UTF-8'
default_text_search_config = 'pg_catalog.english'

After I have restarted PostgreSQL 9.3, the output of psql was still
german for both servers. To have psql print it's output in english, I
had to export the environment variable LANG to en_US.UTF-8 .
Having these steps done, I re-run my test and it doesn't change anything
to the time required.

Best regards,
Marc

Am 04.09.2014 13:35, schrieb Thomas Kellerer:
>> After this, I ran "ANALYSE;" while connected to the DB "db" without any further arguments, as Keith suggested.
>> It echoed nothing but "ANALYZE" after a few seconds on both psql shells.
>> After this, I ran the "SELECT *" again, identically with to what is described above.
>
> This statement:
>
>> The result is still the same: 9.3 needs twice the time of 8.2 to return the results.
>
> doesn't match the results of explain analyze:
>
>> "EXPLAIN ANALYSE" output for 8.2:
>>   Total runtime: 114.922 ms
>
> vs.
>
>> ... followed by 9.3 output for "EXPLAIN ANALYSE":
>>   Total runtime: 128.252 ms
>
> So it took 114ms on 8.2 and 128ms on 9.3.  That's hardly "twice as long".
>
> My naive interpretation of that (not really knowing Linux) would be that the "time" command adds additional overhead
that. 
>
> One thing I also noticed:
>
> the 8.2 psql seems to be in an english environment (because of the "(2 rows)" feedback), whereas 9.3 seems to be a
germanenvironment (because of the "(2 Zeilen)" psql feedback). I wonder if different locales can make a difference -
althoughI there is no string comparison involved in your query. 
>
> Thomas
>
>
>
>
>

Marc Richter schrieb am 04.09.2014 um 15:31:
> Well, this alone is quite odd: It seems as if the psql client shiped with Postgres 9.3 is slowing down the response
of 
> Postgres 8.2 server by 4 seconds; which is 66,67 percent (!) slower than 6 seconds.

This could also mean opening the connection takes longer in 9.3 than in 8.2

> But to remove the possible slowdown "time" might bring in, I remove this command as well
> and run "date" right before and after the psql command instead, which doesn't affect psql at all, but only prints the
currentdate and time: 
>
> 1) The majority of the issue may consist in psql client and not in PostgreSQL Server,
>    since the newer psql client delivers the results of a PostgreSQL 8.2 server a lot slower than the 8.2 client.
>
> 2) There is still a difference of ~2 seconds between the different server versions, which is 20 percent slower than
olderPostgreSQL. 

I don't think it's the "server versions" that are different. It's the _invocation_ of the psql client that is
different, 
and that is hardly a realistic performance test.

In reality any large scale application will use a connection pool which would make your test unrealistic as well (at
leastin my eyes) 

Hi Gilles,

somehow your answer missed the list; I hope it is OK that I'm inserting
it in my reply.

Indeed, setting "ssl = false" in postgresql.conf of PostgreSQL 9.3 leads
to an improvement. I have retried switching SSL on and off several times
and it leads to psql delivering the result 3 seconds faster (9.5 seconds
instead of 12.5).
This is a way better result, and it even beats the speed of PostgreSQL
8.2 when queried with the psql client of PostgreSQL 9.3 by 0.5 seconds.

Thank you for pointing me to this!

But there is still something I do not understand or would like to track,
at least:

I just tried the connection vice versa: I issued the test, using psql
client of PostgreSQL 8.2 to access PostgreSQL server 9.3. And these
results also beats PostgreSQL 8.2 server:
While PostgreSQL 8.2 needs 5.8 to 6 seconds to answer the query,
PostgreSQL 9.3 server needs 5.3 to 5.5 seconds.

So this is great!
What makes me still wonder and ask myself, if there is an error in the
binary (bug?) or another configuration-issue is the slower delivering of
results of both server versions, when the psql 9.3 client is used:

While both server versions need less than 6 seconds to get the results
using psql 8.2, they both need somewhat around 10 seconds to get the
results when psql 9.3 client is used.

Has anybody an idea to this?

Best regards,
Marc

Am 04.09.2014 13:59, schrieb gparc@free.fr:
>
> Marc,
>
> just a shot in the dark..
> Can you repeat the test with ssl off in your 9.3 conf ?
>
> Gilles
> -
> Regards
>
> Selon Marc Richter <mail@marc-richter.info>:
>
>   Hi Thomas and Keith,
>
>   After I have re-tested the performance after a plain insert of the dump
>   without any vacuuming or reindexing, I just have executed the following
>   on both PostgreSQL versions. I know, REINDEXING after a VACUUM FULL is
>   only suggested with pre-9.0 versions, but I haven't heard of any harm
>   doing it with >=9.0 versions, either, so I executed them there as well:
>
>   a) VACUUM FULL:
>   for table in $(/usr/local/bin/psql -U postgres -h 127.0.0.1 -p 5433 db
>   -c "\dt" | tail -n +4 | head -n -2 | cut -d '|' -f 2 | sed 's#^ ##g' |
>   expand | sed 's# \+##g'); do
>      /usr/local/bin/psql -U postgres -h 127.0.0.1 -p 5433 -c "VACUUM FULL
>   VERBOSE ${table};" db
>   done
>
>   b) REINDEX TABLE:
>   for table in $(/usr/local/bin/psql -U postgres -h 127.0.0.1 -p 5433 db
>   -c "\dt" | tail -n +4 | head -n -2 | cut -d '|' -f 2 | sed 's#^ ##g' |
>   expand | sed 's# \+##g'); do
>      /usr/local/bin/psql -U postgres -h 127.0.0.1 -p 5433 -c "REINDEX
>   TABLE ${table};" db
>   done
>
>   c) REINDEX DATABASE:
>   /usr/local/bin/psql -U postgres -h 127.0.0.1 -p 5433 -c "REINDEX
>   DATABASE db;" db
>
>   I did all these commands on the 9.3 Postgres as well by replacing Port
>   5433 with 5432 and /usr/local/bin/psql with /usr/bin/psql .
>
>   After this, I re-issued the test to selecting a 360881 rowed table again
>   without getting different results:
>
>   for x in 1 2 3 ; do
>      sync
>      sleep 10
>      time /usr/local/bin/psql -U postgres -h 127.0.0.1 -p 5433 -c \
>      "SELECT * FROM billing_events;" db | wc -l
>   done
>
>   Again, I used Port 5432 instead of 5433 and /usr/bin/psql instead of
>   /usr/local/bin/psql for testing the 9.3 Postgres:
>
>   8.2:
>   360881
>   real    0m5.996s
>   user    0m4.448s
>   sys     0m0.724s
>
>   360881
>   real    0m6.023s
>   user    0m4.520s
>   sys     0m0.664s
>
>   360881
>   real    0m6.077s
>   user    0m4.580s
>   sys     0m0.664s
>
>
>   9.3:
>   360881
>   real    0m12.835s
>   user    0m9.737s
>   sys     0m0.708s
>
>   360881
>   real    0m12.689s
>   user    0m9.685s
>   sys     0m0.652s
>
>   360881
>   real    0m12.700s
>   user    0m9.649s
>   sys     0m0.700s
>
>   After this, I ran "ANALYSE;" while connected to the DB "db" without any
>   further arguments, as Keith suggested. It echoed nothing but "ANALYZE"
>   after a few seconds on both psql shells.
>   After this, I ran the "SELECT *" again, identically with to what is
>   described above.
>   The result is still the same: 9.3 needs twice the time of 8.2 to return
>   the results.
>
>   As you asked for, here comes the "EXPLAIN ANALYSE" output for 8.2:
>
>                QUERY PLAN
>
-------------------------------------------------------------------------------------------------------------------------
>     Seq Scan on billing_events  (cost=0.00..16098.40 rows=360940
>   width=316) (actual time=0.015..84.507 rows=360877 loops=1)
>     Total runtime: 114.922 ms
>   (2 rows)
>
>
>   ... followed by 9.3 output for "EXPLAIN ANALYSE":
>
>                QUERY PLAN
>
-------------------------------------------------------------------------------------------------------------------------
>     Seq Scan on billing_events  (cost=0.00..15409.77 rows=360877
>   width=302) (actual time=0.035..97.698 rows=360877 loops=1)
>     Total runtime: 128.252 ms
>   (2 Zeilen)
>
>   ... followed by 9.3 output for "EXPLAIN (analyze true, verbose true,
>   buffers true) select ...":
>
>                QUERY PLAN
>
--------------------------------------------------------------------------------------------------------------------------------------------------------------------
>     Seq Scan on public.billing_events  (cost=0.00..15409.77 rows=360877
>   width=302) (actual time=0.033..96.809 rows=360877 loops=1)
>       Output: id, callid, name, type, callingnumber, callednumber,
>   translatednumber, inserted, eventstart, duration, freeofchargeflag,
>   eventdata, envoxid, cpc, taskid
>       Buffers: shared hit=11801
>     Total runtime: 130.506 ms
>   (4 Zeilen)
>
>   These don't me tell anything. Do they help you understanding this issue?
>
>   Best regards,
>   Marc
>
>   Am 27.08.2014 17:00, schrieb Keith:
>   >
>   >
>   >
>   > On Wed, Aug 27, 2014 at 2:55 AM, Thomas Kellerer <spam_eater@gmx.net
>   > <mailto:spam_eater@gmx.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@postgresql.org
>   >     <mailto:pgsql-novice@postgresql.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.
>
>
>   --
>   Sent via pgsql-novice mailing list (pgsql-novice@postgresql.org)
>   To make changes to your subscription:
>   http://www.postgresql.org/mailpref/pgsql-novice
>
>
>