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

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
>
>
>