Ola
se vc nao utilizar a clausula where na sua querie, ela vai mesmo dar um full
table scan, ja que vc nao especificou quais registros vc quer
[]
Luiz
----- Original Message -----
From: Nei Rauni Santos
To: Comunidade PostgreSQL Brasileira
Sent: Wednesday, August 12, 2009 3:43 PM
Subject: [pgbr-geral] Interpretação do explain analyse
Dae pessoal, tenho uma consulta entre 2 tabelas que está demorando bastante
tempo, gostaria da ajuda de alguém para analisar o resultado do explain
analize.
minha query:
select d.description_type_id, t.lang, count( t.id ) from hotel_description
as d
inner join hotel_description_translation as t ON ( d.id = t.id )
group by d.description_type_id, t.lang
o pgadmin3 diz que demora 14714 ms para executar essa query, o resultado
dela é esse:
4;"pt_BR";12183
3;"pt";13326
8;"pt_BR";58957
5;"en";27687
7;"en";20041
11;"en";61
6;"pt";24534
2;"pt_BR";11559
1;"pt_BR";10024
3;"en";33032
6;"en";59706
7;"pt";10176
11;"pt";29
5;"pt";4061
4;"en";28612
8;"en";59726
2;"pt";11559
1;"pt";10024
5;"pt_BR";4061
7;"pt_BR";10176
11;"pt_BR";29
1;"en";23219
2;"en";26297
8;"pt";58957
3;"pt_BR";13326
4;"pt";12183
6;"pt_BR";24534
estrutura das minhas tabelas:
CREATE TABLE hotel_description_translation
(
id integer NOT NULL,
lang character varying(5) NOT NULL,
created_at timestamp without time zone,
updated_at timestamp without time zone,
description text NOT NULL,
CONSTRAINT pkhotel_description_i18n PRIMARY KEY (id, lang),
CONSTRAINT fk_hotel_description_i18n_hotel_description FOREIGN KEY (id)
REFERENCES hotel_description (id) MATCH SIMPLE
ON UPDATE NO ACTION ON DELETE NO ACTION
)
WITH (OIDS=FALSE);
CREATE TABLE hotel_description
(
description_type_id integer NOT NULL,
hotel_id integer NOT NULL,
id serial NOT NULL,
CONSTRAINT pkhotel_description PRIMARY KEY (id),
CONSTRAINT fk_hotel_description_description_type FOREIGN KEY
(description_type_id)
REFERENCES description_type (id) MATCH SIMPLE
ON UPDATE NO ACTION ON DELETE NO ACTION,
CONSTRAINT fk_hotel_description_hotel FOREIGN KEY (hotel_id)
REFERENCES hotel (id) MATCH SIMPLE
ON UPDATE NO ACTION ON DELETE NO ACTION
)
WITH (OIDS=FALSE);
quantidade de registros
select count(*) from hotel_description; --327819 rows
select count(*) from hotel_description_translation; --568079 rows
o explain analyse resultou nisso:
"HashAggregate (cost=88792.79..88793.09 rows=24 width=11)"
" -> Hash Join (cost=10263.93..84532.20 rows=568079 width=11)"
" Hash Cond: (t.id = d.id)"
" -> Seq Scan on hotel_description_translation t
(cost=0.00..57895.79 rows=568079 width=7)"
" -> Hash (cost=4885.19..4885.19 rows=327819 width=8)"
" -> Seq Scan on hotel_description d (cost=0.00..4885.19
rows=327819 width=8)"
se alguém puder ajudar, não sei se é possível otimizar um pouco mais isso.
Nei
--
Nei Rauni Santos
[email protected]
+55 41 85020985
_______________________________________________
pgbr-geral mailing list
[email protected]
https://listas.postgresql.org.br/cgi-bin/mailman/listinfo/pgbr-geral
_______________________________________________
pgbr-geral mailing list
[email protected]
https://listas.postgresql.org.br/cgi-bin/mailman/listinfo/pgbr-geral
