pg.ddx.io pgsql-performance@postgresql.org mailing list archive
help / color / mirror / Atom feedindex scan through a subquery
3+ messages / 2 participants
[nested] [flat]
* index scan through a subquery
@ 2007-02-02 01:44 Bill Howe <howew@stccmop.org>
0 siblings, 1 reply; 3+ messages in thread
From: Bill Howe @ 2007-02-02 01:44 UTC (permalink / raw)
To: pgsql-performance
Why should these queries have different plans?
create table foo (a int PRIMARY KEY);
Q1: explain select max(a) from foo
> Result (cost=0.04..0.05 rows=1 width=0)
> InitPlan
> -> Limit (cost=0.00..0.04 rows=1 width=4)
> -> Index Scan Backward using foo_pkey on foo
> (cost=0.00..76.10 rows=2140 width=4)
> Filter: (a IS NOT NULL)
Q2: explain select max(a) from (select * from foo) as f
> Aggregate (cost=36.75..36.76 rows=1 width=4)
> -> Seq Scan on foo (cost=0.00..31.40 rows=2140 width=4)
I need the lovely index scan, but my table is hidden behind a view, and
all I get is the ugly sequential scan. Any ideas on how to convince the
optimizer to unfold the subquery properly?
Bill
^ permalink raw reply [nested|flat] 3+ messages in thread
* Re: index scan through a subquery
@ 2007-02-06 06:52 Tom Lane <tgl@sss.pgh.pa.us>
parent: Bill Howe <howew@stccmop.org>
0 siblings, 1 reply; 3+ messages in thread
From: Tom Lane @ 2007-02-06 06:52 UTC (permalink / raw)
To: Bill Howe <howew@stccmop.org>; +Cc: pgsql-performance
Bill Howe <howew@stccmop.org> writes:
> I need the lovely index scan, but my table is hidden behind a view, and
> all I get is the ugly sequential scan. Any ideas on how to convince the
> optimizer to unfold the subquery properly?
You should provide some context in this sort of gripe, like which PG
version you're using. But I'm going to guess that it's 8.2.x, because
8.1.x gets it right :-(. Try the attached.
regards, tom lane
inline patch 1: planagg.c (+12/-6 lines) download inline-1.patch
Index: planagg.c
===================================================================
RCS file: /cvsroot/pgsql/src/backend/optimizer/plan/planagg.c,v
retrieving revision 1.25
diff -c -r1.25 planagg.c
*** planagg.c 9 Jan 2007 02:14:13 -0000 1.25
--- planagg.c 6 Feb 2007 06:30:23 -0000
***************
*** 70,75 ****
--- 70,76 ----
optimize_minmax_aggregates(PlannerInfo *root, List *tlist, Path *best_path)
{
Query *parse = root->parse;
+ FromExpr *jtnode;
RangeTblRef *rtr;
RangeTblEntry *rte;
RelOptInfo *rel;
***************
*** 102,115 ****
* We also restrict the query to reference exactly one table, since join
* conditions can't be handled reasonably. (We could perhaps handle a
* query containing cartesian-product joins, but it hardly seems worth the
! * trouble.)
*/
! Assert(parse->jointree != NULL && IsA(parse->jointree, FromExpr));
! if (list_length(parse->jointree->fromlist) != 1)
! return NULL;
! rtr = (RangeTblRef *) linitial(parse->jointree->fromlist);
! if (!IsA(rtr, RangeTblRef))
return NULL;
rte = rt_fetch(rtr->rtindex, parse->rtable);
if (rte->rtekind != RTE_RELATION || rte->inh)
return NULL;
--- 103,121 ----
* We also restrict the query to reference exactly one table, since join
* conditions can't be handled reasonably. (We could perhaps handle a
* query containing cartesian-product joins, but it hardly seems worth the
! * trouble.) However, the single real table could be buried in several
! * levels of FromExpr.
*/
! jtnode = parse->jointree;
! while (IsA(jtnode, FromExpr))
! {
! if (list_length(jtnode->fromlist) != 1)
! return NULL;
! jtnode = linitial(jtnode->fromlist);
! }
! if (!IsA(jtnode, RangeTblRef))
return NULL;
+ rtr = (RangeTblRef *) jtnode;
rte = rt_fetch(rtr->rtindex, parse->rtable);
if (rte->rtekind != RTE_RELATION || rte->inh)
return NULL;
* Re: index scan through a subquery
@ 2007-02-06 19:18 Bill Howe <howew@stccmop.org>
parent: Tom Lane <tgl@sss.pgh.pa.us>
0 siblings, 0 replies; 3+ messages in thread
From: Bill Howe @ 2007-02-06 19:18 UTC (permalink / raw)
To: Tom Lane <tgl@sss.pgh.pa.us>; +Cc: pgsql-performance
Tom Lane wrote:
>> I need the lovely index scan, but my table is hidden behind a view, and
>> all I get is the ugly sequential scan. Any ideas on how to convince the
>> optimizer to unfold the subquery properly?
>
> You should provide some context in this sort of gripe, like which PG
> version you're using. But I'm going to guess that it's 8.2.x, because
> 8.1.x gets it right :-(. Try the attached.
Good guess; I was indeed talking about the "current release" rather than
the "previous release."
Also, apologies for the tone of my post: I was attempting to be jovial,
but in retrospect, I see how it reads as a "gripe," which I guess
evoked your frowny-face emoticon.
Thanks for the quick response, elegant fix, and ongoing excellent work!
Cheers,
Bill
> Index: planagg.c
> ===================================================================
> RCS file: /cvsroot/pgsql/src/backend/optimizer/plan/planagg.c,v
> retrieving revision 1.25
> diff -c -r1.25 planagg.c
> *** planagg.c 9 Jan 2007 02:14:13 -0000 1.25
> --- planagg.c 6 Feb 2007 06:30:23 -0000
> ***************
> *** 70,75 ****
> --- 70,76 ----
> optimize_minmax_aggregates(PlannerInfo *root, List *tlist, Path *best_path)
> {
> Query *parse = root->parse;
> + FromExpr *jtnode;
> RangeTblRef *rtr;
> RangeTblEntry *rte;
> RelOptInfo *rel;
> ***************
> *** 102,115 ****
> * We also restrict the query to reference exactly one table, since join
> * conditions can't be handled reasonably. (We could perhaps handle a
> * query containing cartesian-product joins, but it hardly seems worth the
> ! * trouble.)
> */
> ! Assert(parse->jointree != NULL && IsA(parse->jointree, FromExpr));
> ! if (list_length(parse->jointree->fromlist) != 1)
> ! return NULL;
> ! rtr = (RangeTblRef *) linitial(parse->jointree->fromlist);
> ! if (!IsA(rtr, RangeTblRef))
> return NULL;
> rte = rt_fetch(rtr->rtindex, parse->rtable);
> if (rte->rtekind != RTE_RELATION || rte->inh)
> return NULL;
> --- 103,121 ----
> * We also restrict the query to reference exactly one table, since join
> * conditions can't be handled reasonably. (We could perhaps handle a
> * query containing cartesian-product joins, but it hardly seems worth the
> ! * trouble.) However, the single real table could be buried in several
> ! * levels of FromExpr.
> */
> ! jtnode = parse->jointree;
> ! while (IsA(jtnode, FromExpr))
> ! {
> ! if (list_length(jtnode->fromlist) != 1)
> ! return NULL;
> ! jtnode = linitial(jtnode->fromlist);
> ! }
> ! if (!IsA(jtnode, RangeTblRef))
> return NULL;
> + rtr = (RangeTblRef *) jtnode;
> rte = rt_fetch(rtr->rtindex, parse->rtable);
> if (rte->rtekind != RTE_RELATION || rte->inh)
> return NULL;
>
> ---------------------------(end of broadcast)---------------------------
> TIP 2: Don't 'kill -9' the postmaster
^ permalink raw reply [nested|flat] 3+ messages in thread
end of thread, other threads:[~2007-02-06 19:18 UTC | newest]
Thread overview: 3+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2007-02-02 01:44 index scan through a subquery Bill Howe <howew@stccmop.org>
2007-02-06 06:52 ` Tom Lane <tgl@sss.pgh.pa.us>
2007-02-06 19:18 ` Bill Howe <howew@stccmop.org>
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