X-Original-To: pgsql-performance@postgresql.org Received: from jester.inquent.com (CPE00508b028d7d-CM00803785c5e0.cpe.net.cable.rogers.com [24.103.51.175]) by postgresql.org (Postfix) with ESMTP id B24D047634E for ; Wed, 12 Mar 2003 17:52:17 -0500 (EST) Received: from [127.0.0.1] (localhost [127.0.0.1]) by jester.inquent.com (8.12.6/8.12.6) with ESMTP id h2CMqJ9n057811; Wed, 12 Mar 2003 17:52:20 -0500 (EST) (envelope-from rbt@rbt.ca) Subject: Re: speeding up COUNT and DISTINCT queries From: Rod Taylor To: Max Baker Cc: PostgreSQL Performance Mailing List In-Reply-To: <20030312223811.GM30411@warped.org> References: <20030312223811.GM30411@warped.org> Content-Type: multipart/signed; micalg=pgp-sha1; protocol="application/pgp-signature"; boundary="=-sjRzIqqzFPTrNPe6cg49" Organization: Message-Id: <1047509538.57729.4.camel@jester> Mime-Version: 1.0 X-Mailer: Ximian Evolution 1.2.2 Date: 12 Mar 2003 17:52:19 -0500 X-Archive-Number: 200303/85 X-Sequence-Number: 1383 --=-sjRzIqqzFPTrNPe6cg49 Content-Type: text/plain Content-Transfer-Encoding: quoted-printable On Wed, 2003-03-12 at 17:38, Max Baker wrote: > I'm looking for a general method to=20 > speed up DISTINCT and COUNT queries.=20 >=20 >=20 > mydatabase=3D> EXPLAIN ANALYZE select distinct(mac) from node; > NOTICE: QUERY PLAN: >=20 > Unique (cost=3D110425.67..110514.57 rows=3D3556 width=3D6) (actual > time=3D45289.78..45598.62 rows=3D25334 loops=3D1) > -> Sort (cost=3D110425.67..110425.67 rows=3D35561 width=3D6) (actual > time=3D45289.77..45411.53 rows=3D34597 loops=3D1) > -> Seq Scan on node (cost=3D0.00..107737.61 rows=3D35561 > width=3D6) (actual time=3D6.73..44383.57 rows=3D34597 loops=3D1) >=20 > Total runtime: 45673.19 msec > ouch.=20 >=20 > I run VACCUUM ANALYZE once a day.=20=20 Thats not going to do anything for that query, as there only is one possible plan at the moment :) I don't think you can do much about that query, other than buy a faster harddisk or more ram. Nearly all the time seems to be used pulling the data off the disk (in the Seq Scan). --=20 Rod Taylor PGP Key: http://www.rbt.ca/rbtpub.asc --=-sjRzIqqzFPTrNPe6cg49 Content-Type: application/pgp-signature; name=signature.asc Content-Description: This is a digitally signed message part -----BEGIN PGP SIGNATURE----- Version: GnuPG v1.2.1 (FreeBSD) iD8DBQA+b7oi6DETLow6vwwRAsYHAJ9/PCLkwUwutS0ubA2l5dP/UzJmfACggv7P hIWtgjnKP1BDYSw2S4h/bCc= =bBdq -----END PGP SIGNATURE----- --=-sjRzIqqzFPTrNPe6cg49--