Received: from localhost (maia-1.hub.org [200.46.204.191]) by postgresql.org (Postfix) with ESMTP id 74BEB9FB866 for ; Tue, 13 Feb 2007 16:51:58 -0400 (AST) Received: from postgresql.org ([200.46.204.71]) by localhost (mx1.hub.org [200.46.204.191]) (amavisd-new, port 10024) with ESMTP id 72567-01 for ; Tue, 13 Feb 2007 16:51:54 -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 9D3089FB88B for ; Tue, 13 Feb 2007 16:51:54 -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 l1DKpmta011208; Tue, 13 Feb 2007 15:51:48 -0500 (EST) To: pgsql-performance@nullmx.com cc: pgsql-performance@postgresql.org Subject: Re: JOIN to a VIEW makes a real slow query In-reply-to: <200702131417.32154.pgsql-performance@nullmx.com> References: <200702131253.55486.pgsql-performance@nullmx.com> <200702131417.32154.pgsql-performance@nullmx.com> Comments: In-reply-to "Chuck D." message dated "Tue, 13 Feb 2007 14:17:31 -0600" Date: Tue, 13 Feb 2007 15:51:48 -0500 Message-ID: <11207.1171399908@sss.pgh.pa.us> From: Tom Lane X-Virus-Scanned: Maia Mailguard 1.0.1 X-Spam-Status: No, hits=0.065 tagged_above=0 required=5 tests=AWL, BAYES_50, SPF_HELO_PASS, SPF_PASS X-Spam-Level: X-Archive-Number: 200702/137 X-Sequence-Number: 23191 "Chuck D." writes: > It is still using that sequence scan on the view after the APPEND for the > us_city and world_city table. Any reason why the view won't use the indexes > when it is JOINed to another table but it will when the view is queried > without a JOIN? I should have mentioned this is v8.1.4. 8.1 isn't bright enough for that. Should work in 8.2 though. > Also, does anyone know why this line: > Join Filter: ("outer".city_id = "inner"."?column1?") > ... contains "?column1?" instead of the actual column name? EXPLAIN can't conveniently get access to the column name. That could probably be improved if someone wanted to put enough effort into it. regards, tom lane