X-Original-To: pgsql-performance@postgresql.org Received: from cat (adsl-66-123-169-52.dsl.sntc01.pacbell.net [66.123.169.52]) by postgresql.org (Postfix) with ESMTP id F0526476345 for ; Wed, 12 Mar 2003 17:38:10 -0500 (EST) Received: by cat (Postfix, from userid 501) id 4EF7FB6D69; Wed, 12 Mar 2003 14:38:11 -0800 (PST) Date: Wed, 12 Mar 2003 14:38:11 -0800 From: Max Baker To: PostgreSQL Performance Mailing List Subject: speeding up COUNT and DISTINCT queries Message-ID: <20030312223811.GM30411@warped.org> Mime-Version: 1.0 Content-Type: text/plain; charset=us-ascii Content-Disposition: inline User-Agent: Mutt/1.5.3i X-Archive-Number: 200303/84 X-Sequence-Number: 1382 I'm looking for a general method to speed up DISTINCT and COUNT queries. mydatabase=> EXPLAIN ANALYZE select distinct(mac) from node; NOTICE: QUERY PLAN: Unique (cost=110425.67..110514.57 rows=3556 width=6) (actual time=45289.78..45598.62 rows=25334 loops=1) -> Sort (cost=110425.67..110425.67 rows=35561 width=6) (actual time=45289.77..45411.53 rows=34597 loops=1) -> Seq Scan on node (cost=0.00..107737.61 rows=35561 width=6) (actual time=6.73..44383.57 rows=34597 loops=1) Total runtime: 45673.19 msec ouch. I run VACCUUM ANALYZE once a day. Thanks, max