pg.ddx.io  pgsql-performance@postgresql.org mailing list archive  
help / color / mirror / Atom feed
From: Alvaro Nunes Melo <al_nunes@atua.com.br>
To: Pgsql-Performance <pgsql-performance@postgresql.org>
Subject: Very different index usage on similar tables
Date: Sat, 11 Dec 2004 15:01:24 -0200
Message-ID: <1102784484.4166.8.camel@localhost> (raw)

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

Attachments:

explains (text/plain, 3.0K) download 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

Message-ID: <1102784484.4166.8.camel@localhost>
Permalink:  ../1102784484.4166.8.camel@localhost/
Also on:    postgresql.org/message-id/1102784484.4166.8.camel@localhost

 · 

reply

Reply instructions:

You may reply publicly to this message via plain-text email
using any one of the following methods:

* Reply to all the recipients using the --to and --cc options:
  reply via email

  To: pgsql-performance@postgresql.org
  Cc: al_nunes@atua.com.br
  Subject: Re: Very different index usage on similar tables
  In-Reply-To: <1102784484.4166.8.camel@localhost>

* Save the following mbox file, import it into your mail client,
  and reply-to-all from there: mbox

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