DomainKey-Signature: a=rsa-sha1; c=nofws; d=gmail.com; s=gamma;
	h=message-id:date:from:user-agent:mime-version:to:cc:subject
	:references:in-reply-to:content-type:content-transfer-encoding;
	b=MlN/aCDuYq8NZYou1bWs+Q0RQXwoDHvvIGkmk54BdLhTsYdXwgw8oyb4Nv1f5uqlOF
	qat8Y9//t6m9lF/OCi3hDOC0XcUjtvKgGEJgk3UmOdTTiZguYtdiblEBqluwAFpMCKI0
	TEhcsc2GSg91B6tA4WKyyKNJPSU7xIUqDK/bQ=
Message-ID: <4B6BFE33.5080809@gmail.com>
Date: Fri, 05 Feb 2010 12:17:07 +0100
From: Yeb Havinga <yebhavinga@gmail.com>
User-Agent: Thunderbird 2.0.0.23 (Windows/20090812)
MIME-Version: 1.0
To: Glenn Maynard <glenn@zewt.org>
CC: pgsql-performance@postgresql.org
Subject: Re: Slow query: table iteration (8.3)
References: <bd36f99e1001291949r2e769e8i7050a832b8d5a3ef@mail.gmail.com>	
	<603c8f071002031905w51dbea37y148284ea6546feda@mail.gmail.com>	
	<bd36f99e1002040024i24a2c78vfad45611946c2ae9@mail.gmail.com>	
	<2f4958ff1002040028j29e907c6l47e1f5efe83b667d@mail.gmail.com>	
	<bd36f99e1002040109w67aa1d71hc91261d183fad111@mail.gmail.com>
	<bd36f99e1002041904w37cac39jeb9d60156a06c151@mail.gmail.com>
In-Reply-To: <bd36f99e1002041904w37cac39jeb9d60156a06c151@mail.gmail.com>
Content-Type: text/plain; charset=UTF-8; format=flowed
Content-Transfer-Encoding: 7bit

Glenn Maynard wrote:
> The function version:
>
> CREATE FUNCTION high_score_for_user(user_id int) RETURNS SETOF INTEGER
> LANGUAGE SQL AS $$
>        SELECT t.id FROM test t
>        WHERE t.user_id = $1
>        ORDER BY t.score DESC LIMIT 1
> $$;
> SELECT high_score_for_user(u.id) FROM test_users u;
>
> runs in 100ms.
>   
Hi Glenn,

About cached plans of SQL functions: from the source of function.c

00067 /*
00068  * An SQLFunctionCache record is built during the first call,
00069  * and linked to from the fn_extra field of the FmgrInfo struct.
00070  *
00071  * Note that currently this has only the lifespan of the calling 
query.
00072  * Someday we might want to consider caching the parse/plan 
results longer
00073  * than that.
00074  */

So it is planned at every call of

SELECT high_score_for_user(u.id) FROM test_users u;

and the cache is used between each row of test_users. The plan is with a 
parameter, that means the optimizer could not make use of an actual 
value during planning. However, your test case is clever in the sense 
that there is an index on users and score and the sql function has an 
order by that matches the index, so the planner can avoid a sort by 
accessing the test table using the index. In this particular case, that 
means that the plan is optimal; no unneeded tuples are processed and the 
(function) plan complexity is logaritmic on the size of the test 
relation, you can't get it any better than that. In short: the lack of 
an actual parameter in the test case did not result in an inferior plan. 
So using a dynamic constructed query string in pl/pgsql to 'force' 
replanning during iteration cannot be faster than this sql function.

It is possible to make the performance if this function worse by 
disabling indexscans:

CREATE FUNCTION high_score_for_user(user_id int) RETURNS SETOF INTEGER
LANGUAGE SQL AS $$
       SELECT t.id FROM test t
       WHERE t.user_id = $1
       ORDER BY t.score DESC LIMIT 1
$$
SET enable_indexscan = off;

Now the query time with test_users is over a second. So maybe the 
converse could also be true in your production setup using the same 
technique.

regards,
Yeb Havinga