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

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