dblink performance regression

-----BEGIN PGP SIGNED MESSAGE-----
Hash: SHA1

I was recently approached by someone with a dblink performance
regression, going back to somewhere between 8.3 (good) and 8.4 (bad).
They were measuring ~8% degradation in dblink connection speed. I was
able to replicate on my own hardware with the following script:

8<------------------------------
create or replace function test_dblink(loops int)
returns text as $$
declare
i int;
ret int;
begin
for i in 1..loops loop
IF i % 100 = 0 THEN
RAISE NOTICE 'connect attempt %', i;
END IF;
PERFORM dblink_connect('me','dbname=postgres host=w.x.y.z');
SELECT x into ret FROM dblink('me','SELECT 1', true) as x(x int);
PERFORM dblink_disconnect('me');
end loop;
return 'done';
end;
$$ language plpgsql;
\timing
select test_dblink(1000);
8<------------------------------

In my testing I saw a very consistent 4-5% degradation. I then did a
git bisect and traced the performance degradation to this commit:

- ------------------
http://git.postgresql.org/gitweb/?p=postgresql.git;a=commit;h=e5de601267d98c5d60df6de8d436685c7105d149

committer Joe Conway <mail(at)joeconway(dot)com>
Tue, 9 Jun 2009 16:35:36 +0000 (16:35 +0000)
commit e5de601267d98c5d60df6de8d436685c7105d149

"Default client encoding to server encoding for dblink connections.
Addresses issue raised by Ruzsinszky Attila and confirmed by others."

- ------------------
with this diff:
- ------------------
http://git.postgresql.org/gitweb/?p=postgresql.git;a=blobdiff;f=contrib/dblink/dblink.c;h=e709ae9cc3b9f7177eaae08d11540a8590e7dc63;hp=283b4d0b25e9924dfc2d16075e7cfc1277ce5956;hb=e5de601267d98c5d60df6de8d436685c7105d149;hpb=adaf60131f81394939b5b55f74130343d520cd2d
- ------------------

Apparently setting client encoding is an expensive operation.

My proposed fix (diff attached) is to simply skip setting client
encoding in the case where it already matches server side encoding.
With the patch applied, and client encoding matching server (both
UTF8), the performance regression is completely gone.

Possibly additional work could go into determining if
PQsetClientEncoding() can be made more efficient, but I believe it
still makes sense in the case of dblink to do nothing when nothing is
required.

If there are no objections I'll commit this to all branches this weekend.

Thanks,

Joe

- --
Joe Conway
credativ LLC: http://www.credativ.us
Linux, PostgreSQL, and general Open Source
Training, Service, Consulting, & 24x7 Support
-----BEGIN PGP SIGNATURE-----
Version: GnuPG v1.4.12 (GNU/Linux)
Comment: Using GnuPG with Thunderbird - http://www.enigmail.net/

iQIcBAEBAgAGBQJSoTZ9AAoJEDfy90M199hlVVsP/iG6XbcXYR2G2TC9I6FIaYzQ
c9503QcewPW+tY/0TAhzOYbXfdkkmRDf+4d9HYiCnYANhJW86dxmqtFKMyASbT74
BYjrZUHhafhSobDZBtM0t/6J1CMhLB/Gy66dziqkw0/hx0NeSuQqkluDazcIh/83
34hCRB2LNE7CXx88HvIc0hd6ACk7Ecrw7W6xyoznhmajHnq2G4Mp1AKXSkOTsYcJ
GkKy1G+u42M6pcBaNp6hnPuRj3HGECz6NdeuzWvb2IKLL6cY7C0fvCccqvkhcUpl
SPfIzlzxxtavzkkkxjOQUWrUFVWulZjeTFsQz3AWPQZoLtY+YDJ513R16Jh8s4RI
XoUWXRQVcMS2kWAIck6Nt/2zpIN9Rr4MlUgqh4mXlAZ59ErigVAqbhg9SAE0N4/h
Fa31OlTousTT4Wph34n/2nZK3uF46SiOHAoFipjRtGavbFW4lXKFryz6AEJ6e1Xy
fNpfNrXbKFkmRYdcafjZ7k6+NW70iCcH98A78wgyRLy59/b/M3u/K9TH5YN5tKLH
O+fK+S+s6eA9+omeu2hLl+6CkwDvEfBvzKkLu+9+sdV0s0b7VDt2vnMx/hg6E7EG
wCKB5X451lUz2MhXqX8vKiSGLk2ShmV8o8u0ovh4kFRV4YmjPK7LyDenIdmsoYuQ
NHiORCMc3V9USObJJ8so
=E/Hx
-----END PGP SIGNATURE-----