From: Bruno Wolff III <bruno@wolff.to>
To: Alvaro Nunes Melo <al_nunes@atua.com.br>
Cc: Pgsql-Performance <pgsql-performance@postgresql.org>
Subject: Re: Similar tables, different indexes performance
Date: Mon, 13 Dec 2004 23:22:50 -0600
Message-ID: <20041214052250.GA8082@wolff.to> (raw)
In-Reply-To: <1102966322.6870.4.camel@localhost>
References: <1102958269.5855.29.camel@localhost>
<20041213180303.GA8830@wolff.to>
<1102966322.6870.4.camel@localhost>
On Mon, Dec 13, 2004 at 17:32:02 -0200,
Alvaro Nunes Melo <al_nunes@atua.com.br> 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 <al_nunes@atua.com.br> 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.
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: pgsql-sql@postgresql.org
Cc: bruno@wolff.to, al_nunes@atua.com.br, pgsql-performance@postgresql.org
Subject: Re: Similar tables, different indexes performance
In-Reply-To: <20041214052250.GA8082@wolff.to>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox