Received: from localhost.localdomain (postgresql.org [64.49.215.8]) by localhost (Postfix) with ESMTP id BEC05476C90 for ; Mon, 24 Jun 2002 17:59:05 -0400 (EDT) Mailbox-Line: From marcos-p-garcia@ptinovacao.pt Mon Jun 24 17:59:05 2002 Received: from dmz2.unibox.ptinovacao.pt (unknown [194.65.138.76]) by postgresql.org (Postfix) with ESMTP id D91E047683E for ; Mon, 24 Jun 2002 16:47:14 -0400 (EDT) Received: from uniboxow.ptinovacao.pt ([10.80.50.202]) by dmz2.unibox.ptinovacao.pt (8.9.3/8.9.3) with SMTP id VAA19632 for ; Mon, 24 Jun 2002 21:17:19 +0100 Received: (qmail 4665 invoked by uid 0); 24 Jun 2002 20:45:28 -0000 Received: from unknown (HELO sargao) ([unknown]) (envelope-sender ) by unknown (qmail-ldap-1.03) with SMTP for ; 24 Jun 2002 20:45:28 -0000 Subject: Re: Slow SELECT with distinct, in a TIMESTAMP type column From: Marcos Garcia To: Stephan Szabo Cc: pgsql-sql In-Reply-To: <20020624113546.R56100-100000@megazone23.bigpanda.com> References: <20020624113546.R56100-100000@megazone23.bigpanda.com> Content-Type: multipart/signed; micalg=pgp-sha1; protocol="application/pgp-signature"; boundary="=-8anGEOd/ChFBDfty6D7+" X-Mailer: Ximian Evolution 1.0.7 Date: 24 Jun 2002 20:46:15 +0100 Message-Id: <1024947975.1035.648.camel@sargao> Mime-Version: 1.0 X-Spam-Status: No, hits=-9.6 required=5.0 tests=IN_REP_TO,PGP_SIGNATURE_2,PGP_SIGNATURE version=2.30 X-Spam-Level: X-Archive-Number: 200206/329 X-Sequence-Number: 8294 --=-8anGEOd/ChFBDfty6D7+ Content-Type: text/plain; charset=ISO-8859-1 Content-Transfer-Encoding: quoted-printable When i make the set enable_seqscan =3D off the result is: dbname=3D# set enable_seqscan =3D off; SET VARIABLE dbname=3D# explain analyze SELECT distinct(smsdate) FROM send_total; NOTICE: QUERY PLAN: Unique (cost=3D0.00..31084.39 rows=3D1232 width=3D8) (actual time=3D108.60..195210.91 rows=3D6676 loops=3D1) -> Index Scan using send_total_smsdate_idx on send_total=20 (cost=3D0.00..31053.60 rows=3D12315 width=3D8) (actual time=3D108.59..19514= 5.43 rows=3D12087 loops=3D1) Total runtime: 195226.24 msec On Mon, 2002-06-24 at 19:36, Stephan Szabo wrote: >=20 > On 24 Jun 2002, Marcos Garcia wrote: >=20 > > The problem is that my query is too slow when i use distinct: > > > > pgsql> explain analyze SELECT distinct(smsdate) FROM send_total; > > > > NOTICE: QUERY PLAN: > > > > Unique (cost=3D15840.31..15870.81 rows=3D1220 width=3D8) (actual > > time=3D56358.93..56452.78 rows=3D6670 loops=3D1) > > -> Sort (cost=3D15840.31..15840.31 rows=3D12203 width=3D8) (actual > > time=3D56358.91..56436.95 rows=3D12062 loops=3D1) > > -> Seq Scan on send_total (cost=3D0.00..15012.03 rows=3D12203 > > width=3D8) (actual time=3D0.55..56205.25 rows=3D12062 loops=3D1) > > Total runtime: 56518.26 msec >=20 > As a question, what does explain analyze show if you > set enable_seqscan=3Doff; first? >=20 --=20 M.P.Garcia PT Inova=E7=E3o, SA Servi=E7os e Redes M=F3veis=20 Rua Jos=E9 Ferreira Pinto Basto - 3810 Aveiro Tel: 234 403 253 - Fax: 234 424 160 E-mail: marcos-p-garcia@ptinovacao.pt --=-8anGEOd/ChFBDfty6D7+ Content-Type: application/pgp-signature; name=signature.asc Content-Description: This is a digitally signed message part -----BEGIN PGP SIGNATURE----- Version: GnuPG v1.0.6 (GNU/Linux) Comment: For info see http://www.gnupg.org iD8DBQA9F3cHGRIrLqlAVg0RApfSAJ9SXqqmqC4gSqzGkB+MNPTmbqbVsgCfb+WM UHFczZwwtkMC8suu7IchNA4= =BvXq -----END PGP SIGNATURE----- --=-8anGEOd/ChFBDfty6D7+--