X-Original-To: pgsql-performance-postgresql.org@localhost.postgresql.org Received: from localhost (unknown [200.46.204.2]) by svr1.postgresql.org (Postfix) with ESMTP id 37B42D1B4DA for ; Fri, 30 Jan 2004 05:27:47 +0000 (GMT) Received: from svr1.postgresql.org ([200.46.204.71]) by localhost (neptune.hub.org [200.46.204.2]) (amavisd-new, port 10024) with ESMTP id 67160-07 for ; Fri, 30 Jan 2004 01:27:16 -0400 (AST) Received: from sss.pgh.pa.us (unknown [192.204.191.242]) by svr1.postgresql.org (Postfix) with ESMTP id 318D7D1C97F for ; Fri, 30 Jan 2004 01:27:15 -0400 (AST) Received: from sss2.sss.pgh.pa.us (tgl@localhost [127.0.0.1]) by sss.pgh.pa.us (8.12.11/8.12.11) with ESMTP id i0U5Qmkd004864; Fri, 30 Jan 2004 00:26:48 -0500 (EST) To: Stephan Szabo Cc: Reece Hart , pgsql-performance Subject: Re: query optimization differs between view and explicit In-reply-to: <20040129171201.I35951@megazone.bigpanda.com> References: <1075422699.4062.164.camel@tallac> <20040129171201.I35951@megazone.bigpanda.com> Comments: In-reply-to Stephan Szabo message dated "Thu, 29 Jan 2004 17:20:57 -0800" Date: Fri, 30 Jan 2004 00:26:47 -0500 Message-ID: <4862.1075440407@sss.pgh.pa.us> From: Tom Lane X-Virus-Scanned: by amavisd-new at postgresql.org X-Archive-Number: 200401/317 X-Sequence-Number: 5491 Stephan Szabo writes: > On Thu, 29 Jan 2004, Reece Hart wrote: >> I have a large query which I would like to place in a view. The explicit >> query is sufficiently fast, but the same query as a view is much slower >> and uses a different plan. I would appreciate an explanation of why this >> is, and, more importantly whether/how I might coax the view to use a >> different plan. > Well, in general [ they're not the same query ] Right. The reason the performance is so much worse is that the restriction pseq_id=76 cannot be "pushed down" into the view subquery; we have to form the entire logical output of the view and then filter on pseq_id=76. In your inline query you have done the pushing down anyway and so the restriction is applied much lower in the plan, resulting in lots less work. But the results might be different. The point that Stephan makes is explicitly understood by the planner as of PG 7.4: * 3. If the subquery uses DISTINCT ON, we must not push down any quals that * refer to non-DISTINCT output columns, because that could change the set * of rows returned. It's hard to give any advice on how to make a faster view without more context. What's the actual intention in all this? What's the semantics of pseq_id --- is it unique? It might be you could fix the problem by adding pseq_id to the DISTINCT ON list, but we don't have enough info to understand whether that would break the desired behavior. regards, tom lane