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