X-Original-To: pgsql-admin-postgresql.org@localhost.postgresql.org Received: from localhost (unknown [200.46.204.2]) by svr1.postgresql.org (Postfix) with ESMTP id BAFD8D1D2BA for ; Thu, 5 Feb 2004 00:37:54 +0000 (GMT) Received: from svr1.postgresql.org ([200.46.204.71]) by localhost (neptune.hub.org [200.46.204.2]) (amavisd-new, port 10024) with ESMTP id 26807-05 for ; Wed, 4 Feb 2004 20:37:54 -0400 (AST) Received: from mailr-1.tiscali.it (mailr-1.tiscali.it [212.123.84.81]) by svr1.postgresql.org (Postfix) with ESMTP id 6505DD1CCBE for ; Wed, 4 Feb 2004 20:37:52 -0400 (AST) Received: from ppp-82-84-56-186.cust-adsl.tiscali.it (HELO alphaville) (82.84.56.186) by mailr-1.tiscali.it with ESMTP; 05 Feb 2004 01:37:53 +0100 Message-Id: <2s28b9$kqihb@mailr-1.tiscali.it> X-BrightmailFiltered: true Reply-To: From: "Edoardo Ceccarelli" To: "'scott.marlowe'" Cc: Subject: R: slow seqscan after vacuum analize Date: Thu, 5 Feb 2004 01:35:12 +0100 MIME-Version: 1.0 Content-Type: text/plain; charset="iso-8859-1" Content-Transfer-Encoding: quoted-printable X-Mailer: Microsoft Office Outlook, Build 11.0.5510 Thread-Index: AcPraOBf9Ryscrf5TTqDVQJdSGxdOAAFcvHg In-Reply-To: X-MimeOLE: Produced By Microsoft MimeOLE V6.00.2800.1106 X-Virus-Scanned: by amavisd-new at postgresql.org X-Archive-Number: 200402/48 X-Sequence-Number: 12222 > -----Messaggio originale----- > Da: scott.marlowe [mailto:scott.marlowe@ihs.com]=20 > Inviato: mercoled=EC 4 febbraio 2004 22.45 > A: Edoardo Ceccarelli > Cc: pgsql-admin@postgresql.org > Oggetto: Re: [ADMIN] slow seqscan after vacuum analize >=20 > On Wed, 4 Feb 2004, Edoardo Ceccarelli wrote: >=20 > > I have a simple query that scans each record, like this:=20 > select * from=20 > > utente where luogorilasciodoc=3D'ciao' > > The execution time BEFORE vacuum is: 1203ms The execution=20 > time AFTER=20 > > vacuum is: 6656ms !!! > > What is going on? Thought that one vaccum the db to get=20 > better performance! > >=20 > > PostgreSQL Ver. 7.3.4 on linux >=20 > Can you show us the output of "explain analyze select * from=20 > ..." for both=20 > before and after a vacuum? i.e. with the difference in=20 > performance. If=20 > not, just an explain analyze of the way it runs now might be enough. >=20 BEFORE (copy of the db without having run the vacuum): explain analyze SELECT * FROM utente where luogorilasciodoc=3D'ciao'; QUERY PLAN ---------------------------------------------------------------------------= ----------- ---------------------- Seq Scan on utente (cost=3D0.00..40947.90 rows=3D1826 width=3D724) (actual time=3D131.17..856.96 rows=3D15 loops=3D1) Filter: (luogorilasciodoc =3D 'ciao'::bpchar) Total runtime: 857.06 msec (3 rows) AFTER (actual db) portaportese=3D# explain analyze SELECT * FROM utente where luogorilasciodo= c=3D'ciao'; QUERY PLAN ---------------------------------------------------------------------------= ----------- -------------------- Seq Scan on utente (cost=3D0.00..92174.50 rows=3D3 width=3D724) (actual time=3D705.41..6458.19 rows=3D15 loops=3D1) Filter: (luogorilasciodoc =3D 'ciao'::bpchar) Total runtime: 6458.29 msec (3 rows Things are worst only for seqscan, when it uses indexscan timing is good.