Re: index scan through a subquery

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