Re: Slow query: table iteration (8.3)

Glenn Maynard wrote:
> On Mon, Feb 1, 2010 at 6:15 AM, Yeb Havinga <yhavinga(at)gmail(dot)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