pg.ddx.io  pgsql-performance@postgresql.org mailing list archive  
help / color / mirror / Atom feed
From: Michael Fuhr <mike@fuhr.org>
To: Rodrigo Moreno <rodrigo.miguel@terra.com.br>
Cc: pgsql-performance@postgresql.org
Subject: Re: Help to find out problem with joined tables
Date: Wed, 16 Mar 2005 21:42:18 -0700
Message-ID: <20050317044217.GA23968@winnie.fuhr.org> (raw)
In-Reply-To: <20050316201027.757D5F4126@estero.terra.com.br>
References: <200503142105.j2EL5mkj026255@hosting.commandprompt.com>
	<20050316201027.757D5F4126@estero.terra.com.br>

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/



view thread (2+ messages)

Message-ID: <20050317044217.GA23968@winnie.fuhr.org>
Permalink:  ../20050317044217.GA23968@winnie.fuhr.org/
Also on:    postgresql.org/message-id/20050317044217.GA23968@winnie.fuhr.org

 · 

reply

Reply instructions:

You may reply publicly to this message via plain-text email
using any one of the following methods:

* Reply to all the recipients using the --to and --cc options:
  reply via email

  To: pgsql-performance@postgresql.org
  Cc: mike@fuhr.org, rodrigo.miguel@terra.com.br
  Subject: Re: Help to find out problem with joined tables
  In-Reply-To: <20050317044217.GA23968@winnie.fuhr.org>

* Save the following mbox file, import it into your mail client,
  and reply-to-all from there: mbox

This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox