Subject: Re: Similar tables, different indexes performance
From: Alvaro Nunes Melo <al_nunes@atua.com.br>
To: Pgsql-Performance <pgsql-performance@postgresql.org>
In-Reply-To: <20041213180303.GA8830@wolff.to>
References: <1102958269.5855.29.camel@localhost>
	<20041213180303.GA8830@wolff.to>
Content-Type: text/plain; charset=ISO-8859-1
Message-Id: <1102966322.6870.4.camel@localhost>
Mime-Version: 1.0
Date: Mon, 13 Dec 2004 17:32:02 -0200
Content-Transfer-Encoding: 8bit

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        |
+---------------------------------------------------+