pg.ddx.io pgsql-performance@postgresql.org mailing list archive
help / color / mirror / Atom feedis it possible to for the planner to optimize this form?
3+ messages / 3 participants
[nested] [flat]
* is it possible to for the planner to optimize this form?
@ 2004-06-07 17:30 Merlin Moncure <merlin.moncure@rcsonline.com>
2004-06-07 20:31 ` Re: is it possible to for the planner to optimize this form? Josh Berkus <josh@agliodbs.com>
0 siblings, 1 reply; 3+ messages in thread
From: Merlin Moncure @ 2004-06-07 17:30 UTC (permalink / raw)
To: pgsql-performance
Right now, I am having trouble getting the planner to optimize queries
in the form of
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?
Merlin
^ permalink raw reply [nested|flat] 3+ messages in thread
* Re: is it possible to for the planner to optimize this form?
2004-06-07 17:30 is it possible to for the planner to optimize this form? Merlin Moncure <merlin.moncure@rcsonline.com>
@ 2004-06-07 20:31 ` Josh Berkus <josh@agliodbs.com>
0 siblings, 0 replies; 3+ messages in thread
From: Josh Berkus @ 2004-06-07 20:31 UTC (permalink / raw)
To: Merlin Moncure <merlin.moncure@rcsonline.com>; pgsql-performance
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
^ permalink raw reply [nested|flat] 3+ messages in thread
* Re: is it possible to for the planner to optimize this
@ 2004-06-07 23:31 Duane Lee - EGOVX <DLee@mail.maricopa.gov>
0 siblings, 0 replies; 3+ messages in thread
From: Duane Lee - EGOVX @ 2004-06-07 23:31 UTC (permalink / raw)
To: 'Josh Berkus' <josh@agliodbs.com>; Merlin Moncure <merlin.moncure@rcsonline.com>; pgsql-performance
I didn't really look that closely at the problem but have you thought of
trying:
select t.key, t.field from t a
, (select count(*) as cntb from t b
where b.field > a.field) as dmytbl
where
cntb = k
This is called an inline view or sometimes a nested table. You would be
joining table t to this inline view with the join criteria being "cntb = k"
where k is in t.
-----Original Message-----
From: Josh Berkus [mailto:josh@agliodbs.com]
Sent: Monday, June 07, 2004 1:32 PM
To: Merlin Moncure; pgsql-performance@postgresql.org
Subject: Re: [PERFORM] is it possible to for the planner to optimize
this form?
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
---------------------------(end of broadcast)---------------------------
TIP 3: if posting/reading through Usenet, please send an appropriate
subscribe-nomail command to majordomo@postgresql.org so that your
message can get through to the mailing list cleanly
^ permalink raw reply [nested|flat] 3+ messages in thread
end of thread, other threads:[~2004-06-07 23:31 UTC | newest]
Thread overview: 3+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2004-06-07 17:30 is it possible to for the planner to optimize this form? Merlin Moncure <merlin.moncure@rcsonline.com>
2004-06-07 20:31 ` Josh Berkus <josh@agliodbs.com>
2004-06-07 23:31 Re: is it possible to for the planner to optimize this Duane Lee - EGOVX <DLee@mail.maricopa.gov>
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