MIME-Version: 1.0
In-Reply-To: <4B67F908.1060507@gmail.com>
References: <bd36f99e1001291949r2e769e8i7050a832b8d5a3ef@mail.gmail.com>
	<4B66B7E0.3020605@gmail.com>
	<bd36f99e1002011852j19a34de7qfbd860f2a97584c@mail.gmail.com>
	<4B67F908.1060507@gmail.com>
Date: Thu, 4 Feb 2010 01:30:06 -0500
Message-ID: <bd36f99e1002032230g27c0aa2ckcec74c417e8a37e1@mail.gmail.com>
Subject: Re: Slow query: table iteration (8.3)
From: Glenn Maynard <glenn@zewt.org>
To: Yeb Havinga <yebhavinga@gmail.com>
Cc: pgsql-performance@postgresql.org
Content-Type: text/plain; charset=UTF-8
Content-Transfer-Encoding: quoted-printable

On Tue, Feb 2, 2010 at 5:06 AM, Yeb Havinga <yebhavinga@gmail.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, ther=
e
> might be a workaround where you could write a pl/pgsql function that make=
s 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 =3D $1) AND r.user_card_id =3D 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 =C2=A0steps_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...

--=20
Glenn Maynard