pg.ddx.io pgsql-sql@postgresql.org mailing list archive
help / color / mirror / Atom feedSimilar tables, different indexes performance
4+ messages / 2 participants
[nested] [flat]
* Similar tables, different indexes performance
@ 2004-12-13 17:17 Alvaro Nunes Melo <al_nunes@atua.com.br>
0 siblings, 1 reply; 4+ messages in thread
From: Alvaro Nunes Melo @ 2004-12-13 17:17 UTC (permalink / raw)
To: pgsql-sql
Hi,
I know that it's not very polite thing re-send a question, but I don't
have any idea about why can this be happening. I have two almost
identical tables, with equivalent indexes, but their performances are
very different. In this case, I'm sending the queries, explains,
tables'structures and record counts. I think this is the place where I
can most probably get help about performance issues.
Thanks in advance,
--
+---------------------------------------------------+
| Alvaro Nunes Melo Atua Sistemas de Informacao |
| al_nunes@atua.com.br www.atua.com.br |
| UIN - 42722678 (54) 327-1044 |
+---------------------------------------------------+
Attachments:
mail (text/plain, 5.1K) download
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
db=> EXPLAIN ANALYZE SELECT COUNT(*) FROM movimento WHERE cd_pessoa = 1;
QUERY PLAN
----------------------------------------------------------------------------------------------------------------------------------------------
Aggregate (cost=279.99..279.99 rows=1 width=0) (actual time=1.263..1.264 rows=1 loops=1)
-> Index Scan using idx_movimento_cd_pessoa on movimento (cost=0.00..279.80 rows=74 width=0) (actual time=0.043..0.984 rows=221 loops=1)
Index Cond: (cd_pessoa = 1)
Total runtime: 1.388 ms
(4 records)
Time: 6,279 ms
db=> EXPLAIN ANALYZE SELECT COUNT(*) FROM titulo WHERE cd_pessoa = 1;
QUERY PLAN
-----------------------------------------------------------------------------------------------------------------------------------------------------------
Aggregate (cost=6.02..6.02 rows=1 width=0) (actual time=1.105..1.105 rows=1 loops=1)
-> Index Scan using idx_titulo_cd_pessoa_id_tipo_dt_vencimento on titulo (cost=0.00..6.01 rows=1 width=0)
(actual time=0.040..0.828 rows=220 loops=1)
Index Cond: (cd_pessoa = 1)
Total runtime: 1.209 ms
(4 records)
Time: 6,993 ms
db=> SELECT COUNT(*) FROM movimento;
count
--------
347355
(1 record)
Time: 640,686 ms
db=> SELECT COUNT(*) FROM titulo;
count
--------
347354
(1 record)
Time: 3274,281 ms
db=> \d movimento
Table "public.movimento"
Column | Type | Modifiers
-------------------+---------------+---------------------------------------------------------------------
cd_movimento | integer | not null default nextval('public.movimento_cd_movimento_seq'::text)
cd_pessoa | integer | not null
cd_pessoa_usuario | integer | not null
cd_pessoa_matriz | integer | not null
cd_pessoa_filial | integer | not null
dt_movimento | date | not null
vl_movimento | numeric(15,2) | not null
id_origem | smallint | not null default 0
id_tipo | smallint | not null
nr_nota_fiscal | text |
Índices:
"pk_movimento"chave primária, btree (cd_movimento)
"idx_movimento_cd_pessoa" btree (cd_pessoa)
"idx_movimento_cd_pessoa_id_tipo" btree (cd_pessoa, id_tipo)
Restrições de checagem:
"ckc_id_tipo_moviment" CHECK (id_tipo = 1 OR id_tipo = 2)
"ckc_id_origem_moviment" CHECK (id_origem = 0 OR id_origem = 1 OR id_origem = 2)
Restrições de chave estrangeira:
"fk_movimento_filial" FOREIGN KEY (cd_pessoa_matriz, cd_pessoa_filial) REFERENCES filial(cd_pessoa, cd_pessoa_filial) ON UPDATE RESTRICT ON DELETE RESTRICT
"fk_movimento_usuario" FOREIGN KEY (cd_pessoa_usuario) REFERENCES usuario(cd_pessoa) ON UPDATE RESTRICT ON DELETE RESTRICT
"fk_movimento_pessoa" FOREIGN KEY (cd_pessoa) REFERENCES pessoa(cd_pessoa) ON UPDATE RESTRICT ON DELETE RESTRICT
db=> \d titulo
Table "public.titulo"
Column | Type | Modifiers
----------------+---------------+---------------------------------------------------------------
cd_titulo | integer | not null default nextval('public.titulo_cd_titulo_seq'::text)
cd_portador | integer |
cd_movimento | integer | not null
nr_lote | integer |
cd_lote_titulo | integer |
vl_titulo | numeric(15,2) | not null
vl_acrescimo | numeric(15,2) |
vl_desconto | numeric(15,2) |
vl_multa | numeric(15,2) |
vl_juro | numeric(15,2) |
vl_emolumento | numeric(15,2) |
vl_pago | numeric(15,2) |
dt_vencimento | date | not null
ds_observacao | text |
nr_banco | text |
dt_quitacao | date |
id_tipo | smallint |
cd_pessoa | integer |
Índices:
"pk_titulo"chave primária, btree (cd_titulo)
"idx_titulo_cd_movimento" btree (cd_movimento)
"idx_titulo_cd_pessoa_id_tipo_dt_vencimento" btree (cd_pessoa, id_tipo, dt_vencimento)
"idx_titulo_nr_lote" btree (nr_lote)
Restrições de chave estrangeira:
"fk_titulo_pessoa" FOREIGN KEY (cd_pessoa) REFERENCES pessoa(cd_pessoa)
"fk_titulo_lote" FOREIGN KEY (nr_lote) REFERENCES lote(nr_lote) ON UPDATE RESTRICT ON DELETE RESTRICT
"fk_titulo_portador" FOREIGN KEY (cd_portador) REFERENCES portador(cd_portador) ON UPDATE RESTRICT ON DELETE RESTRICT
"fk_titulo_lote_titulo" FOREIGN KEY (cd_lote_titulo) REFERENCES lote_titulo(cd_lote_titulo) ON UPDATE RESTRICT ON DELETE RESTRICT
* Re: Similar tables, different indexes performance
@ 2004-12-13 18:03 Bruno Wolff III <bruno@wolff.to>
parent: Alvaro Nunes Melo <al_nunes@atua.com.br>
0 siblings, 1 reply; 4+ messages in thread
From: Bruno Wolff III @ 2004-12-13 18:03 UTC (permalink / raw)
To: Alvaro Nunes Melo <al_nunes@atua.com.br>; +Cc: pgsql-sql
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.
^ permalink raw reply [nested|flat] 4+ messages in thread
* Re: Similar tables, different indexes performance
@ 2004-12-13 19:32 Alvaro Nunes Melo <al_nunes@atua.com.br>
parent: Bruno Wolff III <bruno@wolff.to>
0 siblings, 1 reply; 4+ messages in thread
From: Alvaro Nunes Melo @ 2004-12-13 19:32 UTC (permalink / raw)
To: Pgsql-Performance <pgsql-performance@postgresql.org>
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 |
+---------------------------------------------------+
^ permalink raw reply [nested|flat] 4+ messages in thread
* Re: Similar tables, different indexes performance
@ 2004-12-14 05:22 Bruno Wolff III <bruno@wolff.to>
parent: Alvaro Nunes Melo <al_nunes@atua.com.br>
0 siblings, 0 replies; 4+ messages in thread
From: Bruno Wolff III @ 2004-12-14 05:22 UTC (permalink / raw)
To: Alvaro Nunes Melo <al_nunes@atua.com.br>; +Cc: Pgsql-Performance <pgsql-performance@postgresql.org>
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.
^ permalink raw reply [nested|flat] 4+ messages in thread
end of thread, other threads:[~2004-12-14 05:22 UTC | newest]
Thread overview: 4+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2004-12-13 17:17 Similar tables, different indexes performance Alvaro Nunes Melo <al_nunes@atua.com.br>
2004-12-13 18:03 ` Bruno Wolff III <bruno@wolff.to>
2004-12-13 19:32 ` Alvaro Nunes Melo <al_nunes@atua.com.br>
2004-12-14 05:22 ` Bruno Wolff III <bruno@wolff.to>
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