Received: from localhost.localdomain (postgresql.org [64.49.215.8]) by localhost (Postfix) with ESMTP id D336D4761BB for ; Mon, 24 Jun 2002 12:12:13 -0400 (EDT) Mailbox-Line: From marcos-p-garcia@ptinovacao.pt Mon Jun 24 12:12:13 2002 Received: from dmz2.unibox.ptinovacao.pt (unknown [194.65.138.76]) by postgresql.org (Postfix) with ESMTP id 15BC0476BDE for ; Mon, 24 Jun 2002 10:59:36 -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 PAA19165 for ; Mon, 24 Jun 2002 15:29:45 +0100 Received: (qmail 4303 invoked by uid 0); 24 Jun 2002 14:57:59 -0000 Received: from unknown (HELO sargao) ([unknown]) (envelope-sender ) by unknown (qmail-ldap-1.03) with SMTP for ; 24 Jun 2002 14:57:59 -0000 Subject: Slow SELECT with distinct, in a TIMESTAMP type column From: Marcos Garcia To: pgsql-sql Content-Type: multipart/signed; micalg=pgp-sha1; protocol="application/pgp-signature"; boundary="=-yYSHEcV30bsEntXkBs4O" X-Mailer: Ximian Evolution 1.0.7 Date: 24 Jun 2002 14:58:41 +0100 Message-Id: <1024927121.1062.297.camel@sargao> Mime-Version: 1.0 X-Spam-Status: No, hits=-5.7 required=5.0 tests=PGP_SIGNATURE_2,TO_LOCALPART_EQ_REAL,PGP_SIGNATURE version=2.30 X-Spam-Level: X-Archive-Number: 200206/322 X-Sequence-Number: 8287 --=-yYSHEcV30bsEntXkBs4O Content-Type: text/plain; charset=ISO-8859-1 Content-Transfer-Encoding: quoted-printable Hi, I've a table "send_total" like this: Table "send_total" Column | Type |=20=20=20=20=20=20=20=20=20=20=20=20= =20=20=20=20=20=20=20=20=20=20 Modifiers=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20= =20 -----------+--------------------------+------------------------------------= ------------------- id | integer | not null default nextval('"send_total_id_seq"'::text) smsdate | timestamp with time zone |=20 idclient | integer | not null recv | integer | default 0 send | integer | default 0 recv_conf | integer | default 0 send_conf | integer | default 0 conf0 | integer | default 0 conf1 | integer | default 0 conf2 | integer | default 0 conf3 | integer | default 0 conf4 | integer | default 0 conf5 | integer | default 0 conf6 | integer | default 0 conf7 | integer | default 0 Indexes: send_total_idclient_idx, send_total_smsdate_idx Primary key: send_total_pkey Unique keys: send_total_idclismsdate (idclient and smsdate must be unique) 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 This query is becoming slower and slower, day by day. What i have to do to optimize my query or database??? I've this database working since January 2002. I've tried to make the following command, but without success: $ /usr/bin/vacuumdb -z -d dbname -t send_total Thanks in advance, M.P.Garcia --=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 --=-yYSHEcV30bsEntXkBs4O 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 iD8DBQA9FyWRGRIrLqlAVg0RAkWhAKChn0BXLax6XzD6v8AmaytcoyPYdgCfS4w0 b1N5uTJAfoLhtracR8Fz8V4= =Y2d5 -----END PGP SIGNATURE----- --=-yYSHEcV30bsEntXkBs4O--