Postgresql: Query 10x slower in a different client

Question

Looking at the postgres server log, I see that the exact same query on the same postgres server takes much longer (about 10x longer) when invoked from a Linux client or from a Windows client.

The queries come from a Django application running on a Linux machine with 4GB RAM and on a Windows machine with 8GB RAM. Both pyhon environments have psycopg2 library version 2.4.4 to send requests to the same postgres server.

Below are the postgres server logs

The windows query (with time):

2013-06-11 12:12:19 EEST [unknown] 10.1.3.152(56895) mferreiraLOG:  duration: 3207.195 ms  statement: SELECT "autotests_tracerperformance"."id", "autotests_tracerperformance"."date", "autotests_tracerperformance"."video_id", "autotests_tracerperformance"."revision_id", "autotests_tracerperformance"."computer_id", "autotests_tracerperformance"."probe", "autotests_tracerperformance"."time_tostart", "autotests_tracerperformance"."hang_atstart", "autotests_tracerperformance"."time_tohang", "autotests_tracerperformance"."hang", "autotests_tracerperformance"."crash", "autotests_tracerperformance"."stacktrace", "autotests_tracerperformance"."framemax", "autotests_tracerperformance"."maxtime", "autotests_tracerperformance"."avgtime" FROM "autotests_tracerperformance" INNER JOIN "revisions" ON ("autotests_tracerperformance"."revision_id" = "revisions"."id") WHERE ("autotests_tracerperformance"."computer_id" = 61  AND "revisions"."repo" = 'Trunk' )

The linux query (much longer):

2013-06-11 12:12:56 EEST [unknown] 10.1.3.154(35325) mferreiraLOG:  duration: 22191.773 ms  statement: SELECT "autotests_tracerperformance"."id", "autotests_tracerperformance"."date", "autotests_tracerperformance"."video_id", "autotests_tracerperformance"."revision_id", "autotests_tracerperformance"."computer_id", "autotests_tracerperformance"."probe", "autotests_tracerperformance"."time_tostart", "autotests_tracerperformance"."hang_atstart", "autotests_tracerperformance"."time_tohang", "autotests_tracerperformance"."hang", "autotests_tracerperformance"."crash", "autotests_tracerperformance"."stacktrace", "autotests_tracerperformance"."framemax", "autotests_tracerperformance"."maxtime", "autotests_tracerperformance"."avgtime" FROM "autotests_tracerperformance" INNER JOIN "revisions" ON ("autotests_tracerperformance"."revision_id" = "revisions"."id") WHERE ("autotests_tracerperformance"."computer_id" = 61  AND "revisions"."repo" = 'Trunk' )

executing straight from psql (the fastest):

2013-06-11 12:19:06 EEST psql [local] mferreiraLOG:  duration: 1332.902 ms  statement: SELECT "autotests_tracerperformance"."id", "autotests_tracerperformance"."date", "autotests_tracerperformance"."video_id", "autotests_tracerperformance"."revision_id", "autotests_tracerperformance"."computer_id", "autotests_tracerperformance"."probe", "autotests_tracerperformance"."time_tostart", "autotests_tracerperformance"."hang_atstart", "autotests_tracerperformance"."time_tohang", "autotests_tracerperformance"."hang", "autotests_tracerperformance"."crash", "autotests_tracerperformance"."stacktrace", "autotests_tracerperformance"."framemax", "autotests_tracerperformance"."maxtime", "autotests_tracerperformance"."avgtime" FROM "autotests_tracerperformance" INNER JOIN "revisions" ON ("autotests_tracerperformance"."revision_id" = "revisions"."id") WHERE ("autotests_tracerperformance"."computer_id" = 61  AND "revisions"."repo" = 'Trunk' );

Other queries which do not need to load so many items from the database are performing almost the same.

Why so big time differences between clients for this query?

Note: Transmission times are not relevant, since all machines are in the same intranet. Also, the slower times are seen when the client request comes from the same Linux machine where the postgresql server is running.

Note2: Psycopg2 was installed differently in Windows and Linux. Whereas in Windows I installed it from a pre-packaged binary, in Linux I ran 'pip install psycopg2' which relies on a postgresql installation available on the system. Could this result in different values for parameters affecting performance on the client side (e.g. 'work_mem' parameter) ?

Answer 1

You may want to check if the slow client does SSL encryption or not. It happens by default when it's set up on the server and the client has been compiled with SSL support.

For queries that retrieve large amounts of data, the time difference is significant. Also some Linux distributions like Debian/Ubuntu have SSL on by default, even for TCP connections through localhost.

As an example, here's the time difference for a query retrieving 1,5M rows weighing a total of 64Mbytes, with a warm cache.

Without encryption:

$ psql "host=localhost dbname=mlists sslmode=disable"
Password: 
psql (9.1.7, server 9.1.9)
Type "help" for help.

mlists=> \timing
Timing is on.
mlists=> \o /dev/null
mlists=> select subject from mail;
Time: 1672.258 ms

With encryption:

$ psql "host=localhost dbname=mlists"
Password: 
psql (9.1.7, server 9.1.9)
SSL connection (cipher: DHE-RSA-AES256-SHA, bits: 256)
Type "help" for help.

mlists=> \o /dev/null
mlists=> \timing
Timing is on.
mlists=> select subject from mail;
Time: 7017.935 ms

To turn it off globally, one might set SSL=off in postgresql.conf.

To turn it off for specific ranges of client addresses, add entries in pg_hba.conf with hostnossl in the first field before the more generic host entries.

To turn if off client-side, it depends on how the driver exposes the sslmode connection parameter. If it doesn't, the PGSSLMODE environment variable may be used if the driver is implemented on top of libpq.

As for connections through Unix domain sockets (local), SSL is never used with them.