RES: Help to find out problem with joined tables

From: Rodrigo Moreno
Subject: RES: Help to find out problem with joined tables
Date: ,
(view: Whole thread, Raw)
In response to: Re: Help to find out problem with joined tables  (Michael Fuhr)
List: pgsql-performance


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)


-----Mensagem original-----
De: Michael Fuhr [mailto:]
Enviada em: quinta-feira, 17 de março de 2005 01:42
Para: Rodrigo Moreno
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

pgsql-performance by date:

From: Michael Fuhr
Subject: Re: cpu_tuple_cost
From: Hannu Krosing
Subject: Re: One tuple per transaction