X-Original-To: pgsql-performance-postgresql.org@localhost.postgresql.org Received: from localhost (unknown [200.46.204.144]) by svr1.postgresql.org (Postfix) with ESMTP id 073B73A6963 for ; Tue, 14 Dec 2004 05:11:56 +0000 (GMT) Received: from svr1.postgresql.org ([200.46.204.71]) by localhost (av.hub.org [200.46.204.144]) (amavisd-new, port 10024) with ESMTP id 94443-03 for ; Tue, 14 Dec 2004 05:11:33 +0000 (GMT) Received: from www.postgresql.com (unknown [200.46.204.209]) by svr1.postgresql.org (Postfix) with ESMTP id 323303A6A1A for ; Tue, 14 Dec 2004 05:11:30 +0000 (GMT) Received: from wolff.to (wolff.to [66.93.249.74]) by www.postgresql.com (Postfix) with SMTP id 49278738716 for ; Tue, 14 Dec 2004 05:11:25 +0000 (GMT) Received: (qmail 8272 invoked by uid 500); 14 Dec 2004 05:22:50 -0000 Date: Mon, 13 Dec 2004 23:22:50 -0600 From: Bruno Wolff III To: Alvaro Nunes Melo Cc: Pgsql-Performance Subject: Re: Similar tables, different indexes performance Message-ID: <20041214052250.GA8082@wolff.to> Mail-Followup-To: Alvaro Nunes Melo , Pgsql-Performance References: <1102958269.5855.29.camel@localhost> <20041213180303.GA8830@wolff.to> <1102966322.6870.4.camel@localhost> Mime-Version: 1.0 Content-Type: text/plain; charset=iso-8859-1 Content-Disposition: inline Content-Transfer-Encoding: 8bit In-Reply-To: <1102966322.6870.4.camel@localhost> User-Agent: Mutt/1.5.6i X-Virus-Scanned: by amavisd-new at hub.org X-Spam-Status: No, hits=0.005 tagged_above=0 required=5 tests=AWL X-Spam-Level: X-Archive-Number: 200412/175 X-Sequence-Number: 9550 On Mon, Dec 13, 2004 at 17:32:02 -0200, Alvaro Nunes Melo wrote: > Em Seg, 2004-12-13 ās 16:03, Bruno Wolff III escreveu: > > On Mon, Dec 13, 2004 at 15:17:49 -0200, > > Alvaro Nunes Melo wrote: > > > db=> SELECT COUNT(*) FROM titulo WHERE cd_pessoa = 1; > > > count > > > ------- > > > 220 > > > (1 record) > > > > > > Time: 48,762 ms > > > db=> SELECT COUNT(*) FROM movimento WHERE cd_pessoa = 1; > > > count > > > ------- > > > 221 > > > (1 record) > > > > > > Time: 1158,463 ms > > > > I suspect you have a lot of dead tuples in those tables. > > Have you vacuumed them recently? > > Was there enough FSM space when you did so? > > > > You might try doing VACUUM FULL on each table now and see if that > > fixes the problem. > The table had not too many tuples delete, but I runned a VACUUM FULL > VERBOSE ANALYZE and the query's cost and execution time are stil the > same. The output was: > INFO: vacuuming "public.movimento" > INFO: "movimento": found 13 removable, 347355 nonremovable row versions > in 3251 pages If the table really has 300K rows, then something else is wrong. One likely candidate is if cd_pessoa is int8 there is a quirk in postgres (which is fixed in 8.0) where comparing that column to an int4 constant won't use an index scan. This can be worked around by either casting the constant (e.g. 1::int8) or quoting it (e.g. '1') to delay fixing the type so that it will be taken to be an int8 constant.