Received: from localhost (maia-2.hub.org [200.46.204.187]) by postgresql.org (Postfix) with ESMTP id DC7709FB4E7 for ; Thu, 2 Aug 2007 18:32:20 -0300 (ADT) Received: from postgresql.org ([200.46.204.71]) by localhost (mx1.hub.org [200.46.204.187]) (amavisd-maia, port 10024) with ESMTP id 87100-03 for ; Thu, 2 Aug 2007 18:32:12 -0300 (ADT) X-Greylist: from auto-whitelisted by SQLgrey-1.7.5 Received: from floppy.pyrenet.fr (floppy.pyrenet.fr [194.116.145.2]) by postgresql.org (Postfix) with ESMTP id D4CE09FB4E4 for ; Thu, 2 Aug 2007 18:32:15 -0300 (ADT) Received: by floppy.pyrenet.fr (Postfix, from userid 106) id 7283C1C441; Thu, 2 Aug 2007 23:32:14 +0200 (MET DST) From: Ron Mayer X-Newsgroups: pgsql.performance Subject: Why are distinct and group by choosing different plans? Date: Thu, 02 Aug 2007 14:32:14 -0700 Organization: Hub.Org Networking Services Lines: 34 Message-ID: Mime-Version: 1.0 Content-Type: text/plain; charset=ISO-8859-1 Content-Transfer-Encoding: 7bit X-Complaints-To: usenet@news.hub.org User-Agent: Icedove 1.5.0.10 (X11/20070329) To: pgsql-performance@postgresql.org X-Virus-Scanned: Maia Mailguard 1.0.1 X-Spam-Status: No, hits=0.023 tagged_above=0 required=5 tests=AWL=0.022, BAYES_50=0.001 X-Spam-Level: X-Archive-Number: 200708/13 X-Sequence-Number: 26143 I notice that I get different plans when I run the following two queries that I thought would be identical. select distinct test_col from mytable; select test_col from mytable group by test_col; Any reason why it favors one in one case but not the other? d=# explain analyze select distinct test_col from mytable; QUERY PLAN ------------------------------------------------------------------------------------------------------------------------------------------------------------ Unique (cost=0.00..14927.69 rows=27731 width=4) (actual time=0.144..915.214 rows=208701 loops=1) -> Index Scan using "mytable(test_col)" on mytable (cost=0.00..14160.38 rows=306925 width=4) (actual time=0.140..575.580 rows=306925 loops=1) Total runtime: 1013.657 ms (3 rows) d=# explain analyze select test_col from mytable group by test_col; QUERY PLAN ------------------------------------------------------------------------------------------------------------------------ HashAggregate (cost=7241.56..7518.87 rows=27731 width=4) (actual time=609.058..745.295 rows=208701 loops=1) -> Seq Scan on mytable (cost=0.00..6474.25 rows=306925 width=4) (actual time=0.063..280.000 rows=306925 loops=1) Total runtime: 840.321 ms (3 rows) d=# select version(); version ---------------------------------------------------------------------------------------------------------------- PostgreSQL 8.2.1 on i686-pc-linux-gnu, compiled by GCC gcc (GCC) 4.1.2 20061115 (prerelease) (Debian 4.1.1-21) (1 row)