Help to find out problem with joined tables - Mailing list pgsql-performance

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