X-Original-To: pgsql-performance@postgresql.org Received: from beamish.nsd.ca (beamish.nsd.ca [205.150.156.194]) by postgresql.org (Postfix) with ESMTP id 7247E4758E6 for ; Wed, 12 Mar 2003 18:39:31 -0500 (EST) Received: (from smap@localhost) by beamish.nsd.ca (8.9.3/8.9.3) id SAA07170; Wed, 12 Mar 2003 18:39:28 -0500 X-Authentication-Warning: beamish.nsd.ca: smap set sender to using -f Received: from reddog.nsd.ca(192.168.101.30) by beamish.nsd.ca via smap (V2.1/2.1+anti-relay+anti-spam) id xma007168; Wed, 12 Mar 03 18:39:17 -0500 Received: from nsd.ca (jllachan-linux.nsd.ca [192.168.101.148]) by reddog.nsd.ca (8.8.7/8.8.7) with ESMTP id SAA23885; Wed, 12 Mar 2003 18:35:05 -0500 Message-ID: <3E6FC574.F15D9930@nsd.ca> Date: Wed, 12 Mar 2003 18:40:36 -0500 From: Jean-Luc Lachance X-Mailer: Mozilla 4.77 [en] (X11; U; Linux 2.4.18-24.7.x i686) X-Accept-Language: en MIME-Version: 1.0 To: Max Baker Cc: PostgreSQL Performance Mailing List Subject: Re: speeding up COUNT and DISTINCT queries References: <20030312223811.GM30411@warped.org> Content-Type: text/plain; charset=us-ascii Content-Transfer-Encoding: 7bit X-Archive-Number: 200303/87 X-Sequence-Number: 1385 Do you have an index on mac? Max Baker wrote: > > 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 > > ---------------------------(end of broadcast)--------------------------- > TIP 3: if posting/reading through Usenet, please send an appropriate > subscribe-nomail command to majordomo@postgresql.org so that your > message can get through to the mailing list cleanly