Skip site navigation (1) Skip section navigation (2)

Re: Similar tables, different indexes performance

From: Alvaro Nunes Melo <al_nunes(at)atua(dot)com(dot)br>
To: Pgsql-Performance <pgsql-performance(at)postgresql(dot)org>
Subject: Re: Similar tables, different indexes performance
Date: 2004-12-13 19:32:02
Message-ID: 1102966322.6870.4.camel@localhost (view raw or flat)
Thread:
Lists: pgsql-performancepgsql-sql
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(at)atua(dot)com(dot)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(at)atua(dot)com(dot)br        www.atua.com.br       |
|    UIN - 42722678            (54) 327-1044        |
+---------------------------------------------------+


In response to

Responses

pgsql-performance by date

Next:From: Sven WillenbergerDate: 2004-12-13 22:06:40
Subject: Re: Using LIMIT changes index used by planner
Previous:From: Tom LaneDate: 2004-12-13 19:21:04
Subject: Re: pg_restore taking 4 hours!

pgsql-sql by date

Next:From: Bruno Wolff IIIDate: 2004-12-14 05:22:50
Subject: Re: Similar tables, different indexes performance
Previous:From: Bruno Wolff IIIDate: 2004-12-13 18:03:03
Subject: Re: Similar tables, different indexes performance

Privacy Policy | About PostgreSQL
Copyright © 1996-2014 The PostgreSQL Global Development Group