Re: Slow query: table iteration (8.3)

On Mon, Feb 1, 2010 at 6:15 AM, Yeb Havinga <yhavinga@gmail.com> wrote:
> Glenn Maynard wrote:
>> SELECT highscores_for_steps_and_card(s.id, 591, 1) FROM stomp_steps s;
>> Seq Scan on stomp_steps s  (cost=0.00..793.52 rows=2902 width=4)
>> (actual time=26509.919..26509.919 rows=0 loops=1)
>> Total runtime: 26509.972 ms

> 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.

> 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.

> A way to check the plan of that query is to turn on
> debug_print_plan and watch the server log. It takes a bit getting used. The
> plan starts with CONTEXT:  SQL function "functionname" during startup and is
> also recognized because in the opexpr (operator expression) one of the
> operands is a parameter. Important is the total cost of the top plan node
> (the limit).

Thanks.

"SELECT highscores_for_steps_and_card(s.id, 591, 1) FROM stomp_steps s":

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?  That seems
like a huge class of optimizations.  The first NULLTEST can be
optimized away, since that parameter comes from a NOT NULL source (a
PK).  The second NULLTEST can also be optimized away, since it's a
constant value (591).  The search could be a BITMAPHEAPSCAN,
substituting the s.id value for each call, instead of a SEQSCAN.  (Not
that I'm concerned about a few cheap NULLTESTs, I'm just surprised at
it using such a generic plan.)

If I create a new function with the constant parameters hard-coded,
it's back to BITMAPHEAPSCAN: 175ms.  This suggests a horrible
workaround: creating temporary functions every time I make this type
of query, with the fixed values substituted textually.  I'd really
love to know a less awful fix.

> I know 8.3 is mentioned in the subject, but I think that a WITH query
> (http://www.postgresql.org/docs/8.4/interactive/queries-with.html) could be
> a good solution to your problem and may be worth trying out, if you have the
> possibility to try out 8.4.

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.

--
Glenn Maynard