From: Glenn Maynard <glenn@zewt.org>
To: Yeb Havinga <yebhavinga@gmail.com>
Cc: pgsql-performance@postgresql.org
Subject: Re: Slow query: table iteration (8.3)
Date: Thu, 4 Feb 2010 01:30:06 -0500
Message-ID: <bd36f99e1002032230g27c0aa2ckcec74c417e8a37e1@mail.gmail.com> (raw)
In-Reply-To: <4B67F908.1060507@gmail.com>
References: <bd36f99e1001291949r2e769e8i7050a832b8d5a3ef@mail.gmail.com>
<4B66B7E0.3020605@gmail.com>
<bd36f99e1002011852j19a34de7qfbd860f2a97584c@mail.gmail.com>
<4B67F908.1060507@gmail.com>
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, 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
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: pgsql-performance@postgresql.org
Cc: glenn@zewt.org, yebhavinga@gmail.com
Subject: Re: Slow query: table iteration (8.3)
In-Reply-To: <bd36f99e1002032230g27c0aa2ckcec74c417e8a37e1@mail.gmail.com>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox