Thread: Help to find out problem with joined tables
Hi all,
Could someone explain me when I joined tree tables the querys that took
about 1sec to finish, takes 17secs to complete when I put tree tables joined
?
If I join movest/natope, it's fast, if I join movest/produt, it's fast too,
but when I put a third joined table, forget, it's very slow.
All tables are vacuumed by vacummdb --full --analyze, every night
All Indexes are reindexed every night
TABLES:
-------
Movest: +- 2 milions rows, indexed
Natope: 30 rows PK(natope_id)
Produt: +- 1400 Rows PK(codpro)
EXPLAINS:
---------
explain analyze
select a.codpro, a.datmov, a.vlrtot
from movest a, natope b
where a.tipmov = 'S'
and a.codpro = 629001
and a.datmov between '2005-03-01' and '2005-03-31'
and a.natope = b.natope_id
"Merge Join (cost=35.68..36.23 rows=1 width=25) (actual time=2.613..2.840
rows=6 loops=1)"
" Merge Cond: ("outer".natope = "inner"."?column2?")"
" -> Sort (cost=32.02..32.04 rows=7 width=35) (actual time=1.296..1.314
rows=10 loops=1)"
" Sort Key: a.natope"
" -> Index Scan using ix_movest_03 on movest a (cost=0.00..31.92
rows=7 width=35) (actual time=0.507..1.215 rows=10 loops=1)"
" Index Cond: ((codpro = 629001::numeric) AND (datmov >=
'2005-03-01'::date) AND (datmov <= '2005-03-31'::date))"
" Filter: (tipmov = 'S'::bpchar)"
" -> Sort (cost=3.65..3.82 rows=66 width=4) (actual time=1.132..1.203
rows=49 loops=1)"
" Sort Key: (b.natope_id)::numeric"
" -> Seq Scan on natope b (cost=0.00..1.66 rows=66 width=4) (actual
time=0.117..0.500 rows=66 loops=1)"
"Total runtime: 3.077 ms"
---------------
explain analyze
select a.codpro, a.datmov, a.vlrtot
from movest a, natope b, produt c
where a.tipmov = 'S'
and a.codpro = 629001
and a.datmov between '2005-03-01' and '2005-03-31'
and a.natope = b.natope_id
and a.codpro = c.codpro
"Nested Loop (cost=35.68..144.57 rows=2 width=25) (actual
time=2838.121..17257.168 rows=6 loops=1)"
" -> Merge Join (cost=35.68..36.23 rows=1 width=25) (actual
time=1.808..2.280 rows=6 loops=1)"
" Merge Cond: ("outer".natope = "inner"."?column2?")"
" -> Sort (cost=32.02..32.04 rows=7 width=35) (actual
time=0.485..0.504 rows=10 loops=1)"
" Sort Key: a.natope"
" -> Index Scan using ix_movest_03 on movest a
(cost=0.00..31.92 rows=7 width=35) (actual time=0.135..0.390 rows=10
loops=1)"
" Index Cond: ((codpro = 629001::numeric) AND (datmov >=
'2005-03-01'::date) AND (datmov <= '2005-03-31'::date))"
" Filter: (tipmov = 'S'::bpchar)"
" -> Sort (cost=3.65..3.82 rows=66 width=4) (actual
time=1.114..1.209 rows=49 loops=1)"
" Sort Key: (b.natope_id)::numeric"
" -> Seq Scan on natope b (cost=0.00..1.66 rows=66 width=4)
(actual time=0.058..0.485 rows=66 loops=1)"
" -> Seq Scan on produt c (cost=0.00..108.26 rows=8 width=4) (actual
time=2688.356..2875.743 rows=1 loops=6)"
" Filter: ((codpro)::numeric = 629001::numeric)"
"Total runtime: 17257.865 ms"
Best Regards
Rodrigo Moreno
On Wed, Mar 16, 2005 at 05:10:17PM -0300, Rodrigo Moreno wrote: > If I join movest/natope, it's fast, if I join movest/produt, it's fast too, > but when I put a third joined table, forget, it's very slow. What version of PostgreSQL are you using? > All tables are vacuumed by vacummdb --full --analyze, every night > All Indexes are reindexed every night How many updates/deletes do the tables see between vacuums? > Movest: +- 2 milions rows, indexed > Natope: 30 rows PK(natope_id) > Produt: +- 1400 Rows PK(codpro) Could you show the table definitions, or at least the definitions for the relevant columns and indexes? > -> Seq Scan on produt c (cost=0.00..108.26 rows=8 width=4) (actual > time=2688.356..2875.743 rows=1 loops=6) > Filter: ((codpro)::numeric = 629001::numeric) What type is produt.codpro? You might be missing a potential index scan here due to mismatched types. The times (2688.356..2875.743) here look odd, although I might be overlooking or misinterpreting something. I don't know what else might cause that, but one thing that can is a lot of dead tuples in the table, hence my question about how much activity the tables see between vacuums. Maybe somebody else can provide a better explanation. -- Michael Fuhr http://www.fuhr.org/~mfuhr/
Hi, Thanks for your reply. I have made this test without any user connect and after vacuum and all index recteated and tables analyzed. Well, produt.codpro is SERIAL And movest.codpro is NUMBER(8) Thanks Rodrigo -----Mensagem original----- De: Michael Fuhr [mailto:mike@fuhr.org] Enviada em: quinta-feira, 17 de março de 2005 01:42 Para: Rodrigo Moreno Cc: pgsql-performance@postgresql.org Assunto: Re: [PERFORM] Help to find out problem with joined tables On Wed, Mar 16, 2005 at 05:10:17PM -0300, Rodrigo Moreno wrote: > If I join movest/natope, it's fast, if I join movest/produt, it's fast > too, but when I put a third joined table, forget, it's very slow. What version of PostgreSQL are you using? > All tables are vacuumed by vacummdb --full --analyze, every night All > Indexes are reindexed every night How many updates/deletes do the tables see between vacuums? > Movest: +- 2 milions rows, indexed > Natope: 30 rows PK(natope_id) > Produt: +- 1400 Rows PK(codpro) Could you show the table definitions, or at least the definitions for the relevant columns and indexes? > -> Seq Scan on produt c (cost=0.00..108.26 rows=8 width=4) (actual > time=2688.356..2875.743 rows=1 loops=6) > Filter: ((codpro)::numeric = 629001::numeric) What type is produt.codpro? You might be missing a potential index scan here due to mismatched types. The times (2688.356..2875.743) here look odd, although I might be overlooking or misinterpreting something. I don't know what else might cause that, but one thing that can is a lot of dead tuples in the table, hence my question about how much activity the tables see between vacuums. Maybe somebody else can provide a better explanation. -- Michael Fuhr http://www.fuhr.org/~mfuhr/