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=n5+0/aQ6PzZ5cR3A+0h2hyKWJXvAEEtdCNC2u0o/Hy1ZXGeLW38HYX4H5IODw7F1XE
	LkEtfOYo9K9p8av8vivn4KstqTlGEAvPDeegGVpdrQ9ba2254EbCk9t9L7h2s/9TGn8p
	lENtAg3HmBBqMALyvwNmIA7J0iDjz9xu+MGlU=
Message-ID: <4B67F908.1060507@gmail.com>
Date: Tue, 02 Feb 2010 11:06:00 +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>	
	<4B66B7E0.3020605@gmail.com>
	<bd36f99e1002011852j19a34de7qfbd860f2a97584c@mail.gmail.com>
In-Reply-To: <bd36f99e1002011852j19a34de7qfbd860f2a97584c@mail.gmail.com>
Content-Type: text/plain; charset=UTF-8; format=flowed
Content-Transfer-Encoding: 7bit

Glenn Maynard wrote:
> On Mon, Feb 1, 2010 at 6:15 AM, Yeb Havinga <yhavinga@gmail.com> wrote:
>   
>> Stomp_steps is analyzed to 2902 rows but when you run the query the actual
>> rows are 0. This means that the highscore function is not called or the
>> number 0 is incorrect.
>>     
> This SELECT returns 0 rows: it calls the function 1500 times, and each
> time it returns no data, because there simply aren't any results for
> these parameters.
>   
Hmm.. first posting on a pg mailing list and I make this mistake.. What 
an introduction :-[
Checked the source and indeed for every plan node the number of tuples 
that result from it are counted. In most cases this is the number of 
records that match the qualifiers (where clause/join conditions) so that 
was in my head: actual rows = rows that match where, and without where 
I'd expected the actual rows to reflect the total number of rows in the 
table. But with a set returning functions this number is something 
completely different.
>> below. The truth might be that you probably got that result by explaining
>> the query in the function with actual parameter values. This plan differs
>> from the one that is made when the function is called from sql and is
>> planned (once) without parameters, and in that case the plan is probably
>> different.
>>     
>
> Yeah.  It would help a lot if EXPLAIN could show query plans of
> functions used by the statement and not just the top-level query.
>   
Like subplans are, yes. Sounds like a great future.
> Squinting at the output, it definitely looks like a less optimized
> plan; it's using a SEQSCAN instead of BITMAPHEAPSCAN.  (I've attached
> the output.)
>
> Does the planner not optimize functions based on context?
I believe it does for (re) binding of parameter values to prepared 
statements, but not in the case of an sql function. To test an idea, 
there might be a workaround where you could write a pl/pgsql function 
that makes a string with the query and actual parameter values and 
executes that new query everytime. It's not as pretty as a sql function, 
but would give an idea of how fast things would run with each loop 
replanned. Another idea is that maybe you could 'hint' the planner at 
planning time of the sql function by giving it some extra set commands 
(like set search_path but then set enable_seqscan = off) - I don't know 
if planning of the sql function occurs in the environment given by it's 
set commands, but its worth a try. Again, certainly not pretty.
> I can't see how to apply WITH to this.  Non-recursive WITH seems like
> syntax sugar that doesn't do anything a plain SELECT can't do, and I
> don't think what I'm doing here can be done with a regular SELECT.
>   
With indeed is not a solution because the with query is executed once, 
so it cannot take a parameter. What about a window function on a join of 
stomp_steps and stomp_round with partition by on  steps_id and user_card 
is and order by score and with row_number() < your third parameter. From 
the docs I read that window functions cannot be part of the where 
clause: an extra subselect leven is needed then to filter the correct 
row numbers.

Regards,
Yeb Havinga