From: Tom Lane <tgl@sss.pgh.pa.us>
To: Bill Howe <howew@stccmop.org>
Cc: pgsql-performance@postgresql.org
Subject: Re: index scan through a subquery
Date: Tue, 06 Feb 2007 01:52:25 -0500
Message-ID: <2139.1170744745@sss.pgh.pa.us> (raw)
In-Reply-To: <45C29775.3020507@stccmop.org>
References: <45C29775.3020507@stccmop.org>
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.patchIndex: 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;
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: tgl@sss.pgh.pa.us, howew@stccmop.org
Subject: Re: index scan through a subquery
In-Reply-To: <2139.1170744745@sss.pgh.pa.us>
* 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