Re: Slow query: table iteration (8.3)

On Tue, Feb 2, 2010 at 5:06 AM, Yeb Havinga <yebhavinga(at)gmail(dot)com> wrote:
> 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, or just have the code generate a function on the fly, and then
delete it. For example:

CREATE FUNCTION tmp_highscores_for_steps_and_card_PID(steps_id int)
RETURNS SETOF INTEGER LANGUAGE SQL AS $$
SELECT r.id FROM stomp_round r
WHERE ($1 IS NULL OR r.steps_id = $1) AND r.user_card_id = 591
ORDER BY r.score DESC LIMIT 1
$$;
SELECT tmp_highscores_for_steps_and_card_PID(s.id) FROM stomp_steps s;
DROP FUNCTION tmp_highscores_for_steps_and_card_PID(int);

An ugly hack, but it'd unblock things, at least. (Or, I hope so. I
do have other variants of this, for things like "high scores in your
country", "your 5 most recent high scores", etc. That's why I'm doing
this dynamically like this, and not just caching high scores in
another table.)

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

Someone suggested window functions for this back when I was designing
it, and I looked at them. I recall it being very slow, always doing a
seq scan, and it seemed like this wasn't quite what windowing was
designed for...

--
Glenn Maynard