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 | | [EMAIL PROTECTED] www.atua.com.br | | UIN - 42722678 (54) 327-1044 | +---------------------------------------------------+
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
---------------------------(end of broadcast)--------------------------- TIP 2: you can get off all lists at once with the unregister command (send "unregister YourEmailAddressHere" to [EMAIL PROTECTED])