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])
