X-Original-To: pgsql-performance-postgresql.org@localhost.postgresql.org Received: from localhost (developer.postgresql.org [64.117.224.193]) by svr1.postgresql.org (Postfix) with ESMTP id 567A7B439CF for ; Thu, 10 Jul 2003 01:47:18 +0000 (GMT) Received: from svr1.postgresql.org ([64.117.224.193]) by localhost (svr1.postgresql.org [64.117.224.193]) (amavisd-new, port 10024) with ESMTP id 59000-01 for ; Wed, 9 Jul 2003 22:46:46 -0300 (ADT) Received: from server1.techplant.com (unknown [209.61.173.33]) by svr1.postgresql.org (Postfix) with ESMTP id B0641B43A01 for ; Wed, 9 Jul 2003 22:46:44 -0300 (ADT) Received: from localhost (michael@localhost) by server1.techplant.com (8.11.6/8.11.6) with ESMTP id h6A1maV09949 for ; Wed, 9 Jul 2003 21:48:36 -0400 Date: Wed, 9 Jul 2003 21:48:36 -0400 (EDT) From: Michael Pohl X-X-Sender: michael@server1.techplant.com To: pgsql-performance@postgresql.org Subject: plpgsql vs. SQL performance (again) Message-ID: X-Mailkey: j9dfjk23j4dkq MIME-Version: 1.0 Content-Type: TEXT/PLAIN; charset=US-ASCII X-Virus-Scanned: by amavisd-new at postgresql.org X-Archive-Number: 200307/161 X-Sequence-Number: 2454 About a month ago I asked the general list about plpgsql functions that occasionally significantly underperform their straight SQL equivalents. Tom noted that a different query plan was almost certainly being chosen by the plpgsql function: http://archives.postgresql.org/pgsql-general/2003-05/msg00966.php http://archives.postgresql.org/pgsql-general/2003-05/msg00998.php Tom suggested checking for sloppy datatype declarations in the plpgsql functions. Double-checked, a-ok. Tom also suggested that indexscans might not get picked by the plpgsql function if I have some very skewed statistics. Is there a way to verify the plpgsql function's planner choices? My casual observations are that this problem occurs with aggregates, and that the big performance hit is not consistent. I'd like advice on more formal troubleshooting. I can provide examples (my latest problem function is currently taking over 4 seconds vs. .04 seconds for its straight SQL equivalent), table schema, explain output for the straight SQL, etc., if anyone cares to work through this with me. thanks, michael