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 AC12BD1CCDE for ; Fri, 30 Jan 2004 01:21:26 +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 01921-03 for ; Thu, 29 Jan 2004 21:20:58 -0400 (AST) Received: from megazone.bigpanda.com (megazone.bigpanda.com [64.147.171.210]) by svr1.postgresql.org (Postfix) with ESMTP id A746BD1D0B8 for ; Thu, 29 Jan 2004 21:20:54 -0400 (AST) Received: by megazone.bigpanda.com (Postfix, from userid 1001) id 63A7D3547C; Thu, 29 Jan 2004 17:20:57 -0800 (PST) Received: from localhost (localhost [127.0.0.1]) by megazone.bigpanda.com (Postfix) with ESMTP id 623C435413; Thu, 29 Jan 2004 17:20:57 -0800 (PST) Date: Thu, 29 Jan 2004 17:20:57 -0800 (PST) From: Stephan Szabo To: Reece Hart Cc: pgsql-performance Subject: Re: query optimization differs between view and explicit In-Reply-To: <1075422699.4062.164.camel@tallac> Message-ID: <20040129171201.I35951@megazone.bigpanda.com> References: <1075422699.4062.164.camel@tallac> MIME-Version: 1.0 Content-Type: TEXT/PLAIN; charset=US-ASCII X-Virus-Scanned: by amavisd-new at postgresql.org X-Archive-Number: 200401/316 X-Sequence-Number: 5490 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 select distinct on (A) A, B from table where B=10 order by A,B; is not always the same as select * from (select distinct on (A) A, B from table order by A,B) foo where B=10; If A is not unique, then given two rows of the same A value one with B=10 and one with another B value less than 10, the former is guaranteed to give you an A,10 row, the latter will give no such row AFAICS. If A is unique then the two queries are equivalent, but then distinct on (A) isn't terribly meaningful.