Received: from localhost (maia-4.hub.org [200.46.204.183]) by postgresql.org (Postfix) with ESMTP id E9E789FB1C6 for ; Tue, 6 Feb 2007 02:52:28 -0400 (AST) Received: from postgresql.org ([200.46.204.71]) by localhost (mx1.hub.org [200.46.204.183]) (amavisd-new, port 10024) with ESMTP id 12812-09 for ; Tue, 6 Feb 2007 02:52:26 -0400 (AST) X-Greylist: from auto-whitelisted by SQLgrey-1.7.4 Received: from sss.pgh.pa.us (sss.pgh.pa.us [66.207.139.130]) by postgresql.org (Postfix) with ESMTP id 0DCA99FA19A for ; Tue, 6 Feb 2007 02:52:25 -0400 (AST) Received: from sss2.sss.pgh.pa.us (tgl@localhost [127.0.0.1]) by sss.pgh.pa.us (8.13.6/8.13.6) with ESMTP id l166qPGd002140; Tue, 6 Feb 2007 01:52:25 -0500 (EST) To: Bill Howe cc: pgsql-performance@postgresql.org Subject: Re: index scan through a subquery In-reply-to: <45C29775.3020507@stccmop.org> References: <45C29775.3020507@stccmop.org> Comments: In-reply-to Bill Howe message dated "Thu, 01 Feb 2007 17:44:21 -0800" Date: Tue, 06 Feb 2007 01:52:25 -0500 Message-ID: <2139.1170744745@sss.pgh.pa.us> From: Tom Lane X-Virus-Scanned: Maia Mailguard 1.0.1 X-Archive-Number: 200702/58 X-Sequence-Number: 23112 Bill Howe 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 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;