thread-index: AchbcdXm9Tc+lA6rTZy+sD49NcRxdw==
Message-ID: <47935C0C.4090908@enterprisedb.com>
Content-Class: urn:content-classes:message
Importance: normal
Date: Sun, 20 Jan 2008 14:34:52 +0000
From: "Heikki Linnakangas" <heikki@enterprisedb.com>
User-Agent: Icedove 1.5.0.14pre (X11/20071020)
MIME-Version: 1.0
To: "Dean Rasheed" <dean_rasheed@hotmail.com>
Cc: <pgsql-performance@postgresql.org>
Subject: Re: Slow set-returning functions
References: <BAY113-W283034364555E644CD1765F23C0@phx.gbl>
In-Reply-To: <BAY113-W283034364555E644CD1765F23C0@phx.gbl>
Content-Type: text/plain;
	format=flowed;
	charset="ISO-8859-1"
Content-Transfer-Encoding: 7bit

Dean Rasheed wrote:
> I have been having difficulty with some functions which return sets of
> rows. The functions seem to run very slowly, even though the queries
> they run execute very quicky if I run them directly from psgl.
> Typically these queries are only returning a few hundred rows with my
> real data.
> 
> I have had difficulty coming up with a simple test case, but the code
> below usually shows the same problem. Sometimes I have to run the
> setup code a few times before it happens - not sure why (I would
> expect this to be deterministic), but perhaps there is some randomness
> introduced by the sampling done by the analyse.
> 
> The function foo() which has a hard-coded LIMIT always executes
> quickly (comparable to running the query directly).
> 
> However, the function foo(int) which is passed the same LIMIT as a
> parameter executes around 30 times slower. The only difference is that
> the LIMIT is a parameter to the function, although the LIMIT isn't
> reached anyway in this case. Sometimes running this same script
> generates data for which this function executes as fast as the other
> one (which is always fast).

This is clearly because the planner doesn't know what the value for the 
parameter will be at run time, so it chooses a plan that's not optimal 
for LIMIT 100.

> Is there any way that I can see what execution plan is being used
> internally by the functions?

Not directly, but you can do this:

postgres=# PREPARE p (int4) AS SELECT id FROM foo WHERE lower(name) LIKE 
'foo' ORDER BY id OFFSET 0 LIMIT $1;
PREPARE
postgres=# EXPLAIN EXECUTE p(100);                                 QUERY 
PLAN
-----------------------------------------------------------------------------
  Limit  (cost=0.00..49.18 rows=2 width=4)
    ->  Index Scan using foo_pkey on foo  (cost=0.00..614.77 rows=25 
width=4)
          Filter: (lower(name) ~~ 'foo'::text)
(3 rows)

You could work around that by using EXECUTE in the plpgsql function, to 
force the query to be planned on every execution with the actual value 
of the LIMIT.

-- 
   Heikki Linnakangas
   EnterpriseDB   http://www.enterprisedb.com