Message-ID: <45C8D495.9050209@stccmop.org>
Date: Tue, 06 Feb 2007 11:18:45 -0800
From: Bill Howe <howew@stccmop.org>
User-Agent: Thunderbird 1.5.0.9 (X11/20061219)
MIME-Version: 1.0
To: Tom Lane <tgl@sss.pgh.pa.us>
Cc: pgsql-performance@postgresql.org
Subject: Re: index scan through a subquery
References: <45C29775.3020507@stccmop.org> <2139.1170744745@sss.pgh.pa.us>
In-Reply-To: <2139.1170744745@sss.pgh.pa.us>
Content-Type: text/plain; charset=ISO-8859-1
Content-Transfer-Encoding: 7bit

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