X-Original-To: pgsql-sql-postgresql.org@localhost.postgresql.org Received: from localhost (unknown [200.46.204.144]) by svr1.postgresql.org (Postfix) with ESMTP id EDC6D533DF for ; Tue, 29 Mar 2005 16:54:13 +0100 (BST) Received: from svr1.postgresql.org ([200.46.204.71]) by localhost (av.hub.org [200.46.204.144]) (amavisd-new, port 10024) with ESMTP id 94938-04 for ; Tue, 29 Mar 2005 15:54:07 +0000 (GMT) Received: from sss.pgh.pa.us (sss.pgh.pa.us [66.207.139.130]) by svr1.postgresql.org (Postfix) with ESMTP id 5392D52951 for ; Tue, 29 Mar 2005 16:54:06 +0100 (BST) Received: from sss2.sss.pgh.pa.us (tgl@localhost [127.0.0.1]) by sss.pgh.pa.us (8.13.1/8.13.1) with ESMTP id j2TFs8xP022695; Tue, 29 Mar 2005 10:54:08 -0500 (EST) To: mailreg@numerixtechnology.de Cc: Bruno Wolff III , Scott Marlowe , pgsql-sql@postgresql.org Subject: Re: cost of CREATE VIEW ... AS SELECT DISTINCT In-reply-to: <4249564B.1000807@numerixtechnology.de> References: <42487A6D.6020506@numerixtechnology.de> <1112047463.22988.25.camel@state.g2switchworks.com> <424928D8.7060705@numerixtechnology.de> <20050329132941.GA27251@wolff.to> <4249564B.1000807@numerixtechnology.de> Comments: In-reply-to T E Schmitz message dated "Tue, 29 Mar 2005 14:21:15 +0100" Date: Tue, 29 Mar 2005 10:54:08 -0500 Message-ID: <22694.1112111648@sss.pgh.pa.us> From: Tom Lane X-Virus-Scanned: by amavisd-new at hub.org X-Spam-Status: No, hits=0.006 tagged_above=0 required=5 tests=AWL X-Spam-Level: X-Archive-Number: 200503/381 X-Sequence-Number: 20860 T E Schmitz writes: > As far as I can see (via EXPLAIN), both DISTINCT and GROUP BY will lead > to a sequentail scan. Is that correct? I'm not sure why you expect something different. The query requires visiting every row of the table in any case --- else it might miss values that occur only once --- therefore a seqscan is most efficient. The planner will consider plans that involve an indexscan, but only as a substitute for doing an explicit sort before a uniq-style grouping step. Generally the explicit sort will win that comparison. And a hash grouping step probably dominates them both. regards, tom lane