From: Alvaro Nunes Melo <al_nunes@atua.com.br>
To: Pgsql-Performance <pgsql-performance@postgresql.org>
Subject: Re: Similar tables, different indexes performance
Date: Mon, 13 Dec 2004 17:32:02 -0200
Message-ID: <1102966322.6870.4.camel@localhost> (raw)
In-Reply-To: <20041213180303.GA8830@wolff.to>
References: <1102958269.5855.29.camel@localhost>
<20041213180303.GA8830@wolff.to>
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
DETAIL: 0 dead row versions cannot be removed yet.
Nonremovable row versions range from 68 to 74 bytes long.
There were 0 unused item pointers.
Total free space (including removable row versions) is 131440 bytes.
0 pages are or will become empty, including 0 at the end of the table.
90 pages containing 14824 free bytes are potential move destinations.
CPU 0.06s/0.03u sec elapsed 0.81 sec.
INFO: index "idx_movimento_cd_pessoa" now contains 347355 row versions
in 764 pages
DETAIL: 13 index row versions were removed.
0 index pages have been deleted, 0 are currently reusable.
CPU 0.01s/0.02u sec elapsed 0.18 sec.
INFO: index "pk_movimento" now contains 347355 row versions in 764
pages
DETAIL: 13 index row versions were removed.
0 index pages have been deleted, 0 are currently reusable.
CPU 0.02s/0.02u sec elapsed 0.39 sec.
INFO: index "idx_movimento_cd_pessoa_id_tipo" now contains 347355 row
versions in 956 pages
DETAIL: 0 index row versions were removed.
0 index pages have been deleted, 0 are currently reusable.
CPU 0.02s/0.03u sec elapsed 0.27 sec.
INFO: "movimento": moved 9 row versions, truncated 3251 to 3250 pages
DETAIL: CPU 0.00s/0.00u sec elapsed 0.37 sec.
INFO: index "idx_movimento_cd_pessoa" now contains 347355 row versions
in 764 pages
DETAIL: 9 index row versions were removed.
0 index pages have been deleted, 0 are currently reusable.
CPU 0.02s/0.02u sec elapsed 0.08 sec.
INFO: index "pk_movimento" now contains 347355 row versions in 764
pages
DETAIL: 9 index row versions were removed.
0 index pages have been deleted, 0 are currently reusable.
CPU 0.02s/0.02u sec elapsed 0.04 sec.
INFO: index "idx_movimento_cd_pessoa_id_tipo" now contains 347355 row
versions in 956 pages
DETAIL: 9 index row versions were removed.
0 index pages have been deleted, 0 are currently reusable.
CPU 0.02s/0.02u sec elapsed 0.07 sec.
INFO: vacuuming "pg_toast.pg_toast_31462037"
INFO: "pg_toast_31462037": found 0 removable, 0 nonremovable row
versions in 0 pages
DETAIL: 0 dead row versions cannot be removed yet.
Nonremovable row versions range from 0 to 0 bytes long.
There were 0 unused item pointers.
Total free space (including removable row versions) is 0 bytes.
0 pages are or will become empty, including 0 at the end of the table.
0 pages containing 0 free bytes are potential move destinations.
CPU 0.00s/0.00u sec elapsed 0.00 sec.
INFO: index "pg_toast_31462037_index" now contains 0 row versions in 1
pages
DETAIL: 0 index pages have been deleted, 0 are currently reusable.
CPU 0.00s/0.00u sec elapsed 0.01 sec.
INFO: analyzing "public.movimento"
INFO: "movimento": 3250 pages, 3000 rows sampled, 347170 estimated
total rows
--
+---------------------------------------------------+
| Alvaro Nunes Melo Atua Sistemas de Informacao |
| al_nunes@atua.com.br www.atua.com.br |
| UIN - 42722678 (54) 327-1044 |
+---------------------------------------------------+
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: al_nunes@atua.com.br, pgsql-performance@postgresql.org
Subject: Re: Similar tables, different indexes performance
In-Reply-To: <1102966322.6870.4.camel@localhost>
* 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