X-Original-To: pgsql-performance-postgresql.org@localhost.postgresql.org Received: from localhost (unknown [200.46.204.144]) by svr1.postgresql.org (Postfix) with ESMTP id 426713A4302 for ; Sun, 7 Nov 2004 11:04:20 +0000 (GMT) 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 58868-04 for ; Sun, 7 Nov 2004 11:04:16 +0000 (GMT) Received: from rproxy.gmail.com (rproxy.gmail.com [64.233.170.203]) by svr1.postgresql.org (Postfix) with ESMTP id 8A7443A42FE for ; Sun, 7 Nov 2004 11:04:17 +0000 (GMT) Received: by rproxy.gmail.com with SMTP id a41so231731rng for ; Sun, 07 Nov 2004 03:04:17 -0800 (PST) DomainKey-Signature: a=rsa-sha1; q=dns; c=nofws; s=beta; d=gmail.com; h=received:message-id:date:from:reply-to:to:subject:mime-version:content-type:content-transfer-encoding; b=sswucMpS0F9FYMa0VH8qxKmYNgEnTeByEMwSF3xHLinoJ1wTMdPep9i14GURS6vZ00XnZD6Du/k8ELzT0dilZjle+worR0h+JrJDAt/7jWJi7TjPOS8shKPPkVyWbgcLCba7xTnfXOrvZmzL/cvJdHgkM/eam5Jwe+P6xXCRgK4= Received: by 10.38.149.79 with SMTP id w79mr947235rnd; Sun, 07 Nov 2004 03:04:16 -0800 (PST) Received: by 10.38.59.73 with HTTP; Sun, 7 Nov 2004 03:04:16 -0800 (PST) Message-ID: <8c88543604110703044cf70e15@mail.gmail.com> Date: Sun, 7 Nov 2004 19:04:16 +0800 From: Ang Chin Han Reply-To: Ang Chin Han To: pgsql-performance@postgresql.org Subject: DISTINCT and GROUP BY: possible performance enhancement? Mime-Version: 1.0 Content-Type: text/plain; charset=US-ASCII Content-Transfer-Encoding: 7bit X-Virus-Scanned: by amavisd-new at hub.org X-Spam-Status: No, hits=0.0 tagged_above=0.0 required=5.0 tests= X-Spam-Level: X-Archive-Number: 200411/129 X-Sequence-Number: 9083 Is SELECT DISTINCT foo, bar FROM baz; equivalent to SELECT foo, bar from baz GROUP BY foo, bar; ? In the former case, pgsql >= 7.4 does not use HashAgg, but uses it for the latter case. In many circumstances, esp. for large amount of data in the table baz, the second case is an order of a magnitude faster. For example (pgsql8b4): regress=# explain analyze select distinct four from tenk1; QUERY PLAN ---------------------------------------------------------------------------------------------------------------------- Unique (cost=1109.39..1159.39 rows=4 width=4) (actual time=90.017..106.936 rows=4 loops=1) -> Sort (cost=1109.39..1134.39 rows=10000 width=4) (actual time=90.008..95.589 rows=10000 loops=1) Sort Key: four -> Seq Scan on tenk1 (cost=0.00..445.00 rows=10000 width=4) (actual time=0.027..45.454 rows=10000 loops=1) Total runtime: 110.927 ms (5 rows) regress=# explain analyze select distinct four from tenk1 group by four; QUERY PLAN ---------------------------------------------------------------------------------------------------------------------------- Unique (cost=470.04..470.06 rows=4 width=4) (actual time=47.487..47.498 rows=4 loops=1) -> Sort (cost=470.04..470.05 rows=4 width=4) (actual time=47.484..47.486 rows=4 loops=1) Sort Key: four -> HashAggregate (cost=470.00..470.00 rows=4 width=4) (actual time=47.444..47.451 rows=4 loops=1) -> Seq Scan on tenk1 (cost=0.00..445.00 rows=10000 width=4) (actual time=0.013..31.068 rows=10000 loops=1) Total runtime: 47.822 ms (6 rows) If they're equivalent, can we have pgsql use HashAgg for DISTINCTs? Yes, I've read planner.c's comments on "Executor doesn't support hashed aggregation with DISTINCT aggregates.", but I believe using HashAgg is better if the product of the columns' n_distinct statistic is way less than the number of expected rows.