From: Josh Berkus <josh@agliodbs.com>
To: Merlin Moncure <merlin.moncure@rcsonline.com>
To: pgsql-performance@postgresql.org
Subject: Re: is it possible to for the planner to optimize this form?
Date: Mon, 7 Jun 2004 13:31:37 -0700
Message-ID: <200406071331.37140.josh@agliodbs.com> (raw)
In-Reply-To: <6EE64EF3AB31D5448D0007DD34EEB34101AE56@Herge.rcsinc.local>
References: <6EE64EF3AB31D5448D0007DD34EEB34101AE56@Herge.rcsinc.local>
Merlin,
> select t.key, t.field from t a
> where
> (
> select count(*) from t b
> where b.field > a.field
> ) = k
>
> The subplan (either index or seq. scan) executes once for each row in t,
> which of course takes forever.
>
> This query is a way of achieving LIMIT type results (substitute n-1
> desired rows for k) using standard SQL, which is desirable in some
> circumstances. Is it theoretically possible for this to be optimized?
I don't think so, no. PostgreSQL does have some issues using indexes for
count() queires which makes the situation worse. However, with the query
you presented, I don't see any way around the planner executing the subquery
once for every row in t.
Except, of course, for some kind of scheme involving materialized views, if
you don't need up-to-the minute data. In that case, you could store in a
table the count(*)s of t for each threshold value of b.field. But,
dynamically, that would be even slower.
--
Josh Berkus
Aglio Database Solutions
San Francisco
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: josh@agliodbs.com, merlin.moncure@rcsonline.com
Subject: Re: is it possible to for the planner to optimize this form?
In-Reply-To: <200406071331.37140.josh@agliodbs.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