X-Original-To: pgsql-performance-postgresql.org@localhost.postgresql.org Received: from localhost (unknown [200.46.204.144]) by svr1.postgresql.org (Postfix) with ESMTP id 263D23A6352 for ; Sat, 11 Dec 2004 17:01:40 +0000 (GMT) Received: from svr1.postgresql.org ([200.46.204.71]) by localhost (av.hub.org [200.46.204.144]) (amavisd-new, port 10024) with ESMTP id 82087-05 for ; Sat, 11 Dec 2004 17:01:28 +0000 (GMT) Received: from mail.atua.com.br (unknown [200.248.138.61]) by svr1.postgresql.org (Postfix) with ESMTP id C08F03A6344 for ; Sat, 11 Dec 2004 17:01:26 +0000 (GMT) Received: from [10.0.0.171] (ns2.atua.com.br [200.248.138.60]) by mail.atua.com.br (Postfix) with ESMTP id 8AED91EC2F8 for ; Sat, 11 Dec 2004 15:01:24 -0200 (BRST) Subject: Very different index usage on similar tables From: Alvaro Nunes Melo To: Pgsql-Performance Content-Type: multipart/mixed; boundary="=-E++lZY0ZEY9O788aYkTo" Message-Id: <1102784484.4166.8.camel@localhost> Mime-Version: 1.0 X-Mailer: Ximian Evolution 1.4.6 Date: Sat, 11 Dec 2004 15:01:24 -0200 X-Virus-Scanned: by amavisd-new at hub.org X-Spam-Status: No, hits=0.041 tagged_above=0 required=5 tests=AWL X-Spam-Level: X-Archive-Number: 200412/148 X-Sequence-Number: 9523 --=-E++lZY0ZEY9O788aYkTo Content-Type: text/plain Content-Transfer-Encoding: 7bit Hi, I have two similar tables in a database, one stores persons and the other stores telephones. They have a similar number of records (around 70.000), but a indexed search on the persons' table is way faster than in the telephones' table. I'm sending the explains atacched, and I believe that the problem can be in the fact the the explain extimates a worng number of rows in the telefones' explain. I'm sending the explains atacched, and the table and columns' names are in Portuguese, but if it makes easier for you guys I can translate them in my next posts. The in dex in the telephone table is multicolumn, I'd tried to drop it and create a single-column index, but the results were quite the same. Thanks, -- +---------------------------------------------------+ | Alvaro Nunes Melo Atua Sistemas de Informacao | | al_nunes@atua.com.br www.atua.com.br | | UIN - 42722678 (54) 327-1044 | +---------------------------------------------------+ --=-E++lZY0ZEY9O788aYkTo Content-Disposition: attachment; filename=explains Content-Type: text/plain; name=explains; charset=ISO-8859-1 Content-Transfer-Encoding: 8bit db=> EXPLAIN ANALYZE SELECT * FROM telefone WHERE cd_pessoa = 1; QUERY PLAN ------------------------------------------------------------------------------------------------------------------------------------------------------- Index Scan using idx_telefone_cd_pessoa_id_principal on telefone (cost=0.00..1057.21 rows=354 width=101) (actual time=25.650..25.655 rows=1 loops=1) Index Cond: (cd_pessoa = 1) Total runtime: 25.731 ms (3 registros) Tempo: 26,972 ms db=> EXPLAIN ANALYZE SELECT * FROM pessoa WHERE cd_pessoa = 1; QUERY PLAN ------------------------------------------------------------------------------------------------------------------- Index Scan using pk_pessoa on pessoa (cost=0.00..6.01 rows=1 width=48) (actual time=0.123..0.126 rows=1 loops=1) Index Cond: (cd_pessoa = 1) Total runtime: 0.189 ms (3 registros) Tempo: 1,233 ms db=> \d pessoa Tabela "public.pessoa" Coluna | Tipo | Modificadores ---------------+-----------------------------+--------------------------------------------------------------- cd_pessoa | integer | not null default nextval('public.pessoa_cd_pessoa_seq'::text) nm_pessoa | text | not null dt_nascimento | date | dt_importacao | timestamp without time zone | Índices: "pk_pessoa"chave primária, btree (cd_pessoa) "idx_pessoa_cliente_liberado" btree (cliente_liberado(cd_pessoa)) "idx_pessoa_obtem_cd_cidade_comercial" btree (obtem_cd_cidade_comercial(cd_pessoa)) db=> \d telefone Tabela "public.telefone" Coluna | Tipo | Modificadores --------------+------------------------+------------------------------------------------------------------- cd_telefone | integer | not null default nextval('public.telefone_cd_telefone_seq'::text) cd_pessoa | integer | not null nr_telefone | character varying(15) | not null id_tipo | smallint | not null default 1 id_principal | smallint | not null nr_ramal | smallint | ds_contato | character varying(100) | Índices: "pk_telefone"chave primária, btree (cd_telefone) "idx_telefone_cd_pessoa_id_principal" btree (cd_pessoa, id_principal) Restrições de checagem: "ckc_id_tipo_telefone" CHECK (id_tipo = 1 OR id_tipo = 2 OR id_tipo = 3 OR id_tipo = 4) "ckc_id_principal_telefone" CHECK (id_principal = 0 OR id_principal = 1) Restrições de chave estrangeira: "fk_telefone_pessoa" FOREIGN KEY (cd_pessoa) REFERENCES pessoa(cd_pessoa) ON UPDATE RESTRICT ON DELETE RESTRICT --=-E++lZY0ZEY9O788aYkTo--