Re: PG 9.1 much slower than 8.2 ?

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 german environment (because of the "(2 Zeilen)" psql feedback). I wonder if different locales can make a difference - although I there is no string comparison involved in your query.
>
> Thomas
>
>
>
>
>