Received: from localhost (unknown [200.46.204.183]) by developer.postgresql.org (Postfix) with ESMTP id 5C6AC2E0092 for ; Wed, 16 Apr 2008 15:48:35 -0300 (ADT) Received: from developer.postgresql.org ([200.46.204.71]) by localhost (mx1.hub.org [200.46.204.183]) (amavisd-maia, port 10024) with ESMTP id 30561-01-3 for ; Wed, 16 Apr 2008 15:48:25 -0300 (ADT) X-Greylist: from auto-whitelisted by SQLgrey-1.7.5 Received: from fg-out-1718.google.com (fg-out-1718.google.com [72.14.220.153]) by developer.postgresql.org (Postfix) with ESMTP id 349412E00A0 for ; Wed, 16 Apr 2008 15:44:41 -0300 (ADT) Received: by fg-out-1718.google.com with SMTP id 22so2468824fge.23 for ; Wed, 16 Apr 2008 11:44:40 -0700 (PDT) Received: by 10.86.99.9 with SMTP id w9mr667868fgb.44.1208371480612; Wed, 16 Apr 2008 11:44:40 -0700 (PDT) Received: by 10.86.78.7 with HTTP; Wed, 16 Apr 2008 11:44:40 -0700 (PDT) Message-ID: Date: Wed, 16 Apr 2008 14:44:40 -0400 From: "Gavin M. Roy" To: "Tom Lane" Subject: Re: SQL Function Slowness, 8.3.0 Cc: pgsql-performance@postgresql.org In-Reply-To: <8328.1208358587@sss.pgh.pa.us> MIME-Version: 1.0 Content-Type: multipart/alternative; boundary="----=_Part_20860_23503872.1208371480588" References: <8328.1208358587@sss.pgh.pa.us> X-Virus-Scanned: Maia Mailguard 1.0.1 X-Archive-Number: 200804/207 X-Sequence-Number: 29700 ------=_Part_20860_23503872.1208371480588 Content-Type: text/plain; charset=ISO-8859-1 Content-Transfer-Encoding: 7bit Content-Disposition: inline After detailed examination of pg_stat_user_indexes usage, it's clear that the functions don't use the same indexes. I've casted everything to match the indexes in the SQL function, to no success. Any suggestions on next steps? Maybe for 8.4 we could find a way to explain analyze function internals ;-) Gavin On Wed, Apr 16, 2008 at 11:09 AM, Tom Lane wrote: > "Gavin M. Roy" writes: > > In 8.3.0, I'm seeing some oddities with SQL functions which I thought > were > > immune to the planner data restrictions of plpgsql functions and the > sort. > > Without a specific example this discussion is pretty content-free, but > in general SQL functions face the same hazards of bad parameterized > plans as plpgsql functions do. > > regards, tom lane > ------=_Part_20860_23503872.1208371480588 Content-Type: text/html; charset=ISO-8859-1 Content-Transfer-Encoding: 7bit Content-Disposition: inline After detailed examination of pg_stat_user_indexes usage, it's clear that the functions don't use the same indexes.  I've casted everything to match the indexes in the SQL function, to no success.  Any suggestions on next steps?  Maybe for 8.4 we could find a way to explain analyze function internals ;-)

Gavin

On Wed, Apr 16, 2008 at 11:09 AM, Tom Lane <tgl@sss.pgh.pa.us> wrote:
"Gavin M. Roy" <gmr@myyearbook.com> writes:
> In 8.3.0, I'm seeing some oddities with SQL functions which I thought were
> immune to the planner data restrictions of plpgsql functions and the sort.

Without a specific example this discussion is pretty content-free, but
in general SQL functions face the same hazards of bad parameterized
plans as plpgsql functions do.

                       regards, tom lane

------=_Part_20860_23503872.1208371480588--