X-Original-To: pgsql-performance-postgresql.org@localhost.postgresql.org Received: from localhost (unknown [200.46.204.144]) by svr1.postgresql.org (Postfix) with ESMTP id 7B949569BF for ; Thu, 17 Mar 2005 04:42:32 +0000 (GMT) Received: from svr1.postgresql.org ([200.46.204.71]) by localhost (av.hub.org [200.46.204.144]) (amavisd-new, port 10024) with ESMTP id 74793-02 for ; Thu, 17 Mar 2005 04:42:23 +0000 (GMT) Received: from tigger.fuhr.org (tigger.fuhr.org [63.214.45.158]) by svr1.postgresql.org (Postfix) with ESMTP id E89CA569BB for ; Thu, 17 Mar 2005 04:42:22 +0000 (GMT) Received: from winnie.fuhr.org (winnie.fuhr.org [10.1.0.1]) by tigger.fuhr.org (8.13.3/8.13.3) with ESMTP id j2H4gJaf023626 (version=TLSv1/SSLv3 cipher=DHE-RSA-AES256-SHA bits=256 verify=OK); Wed, 16 Mar 2005 21:42:21 -0700 (MST) (envelope-from mfuhr@winnie.fuhr.org) Received: from winnie.fuhr.org (localhost [127.0.0.1]) by winnie.fuhr.org (8.13.3/8.13.3) with ESMTP id j2H4gIDi055917; Wed, 16 Mar 2005 21:42:18 -0700 (MST) (envelope-from mfuhr@winnie.fuhr.org) Received: (from mfuhr@localhost) by winnie.fuhr.org (8.13.3/8.13.3/Submit) id j2H4gING055916; Wed, 16 Mar 2005 21:42:18 -0700 (MST) (envelope-from mfuhr) Date: Wed, 16 Mar 2005 21:42:18 -0700 From: Michael Fuhr To: Rodrigo Moreno Cc: pgsql-performance@postgresql.org Subject: Re: Help to find out problem with joined tables Message-ID: <20050317044217.GA23968@winnie.fuhr.org> References: <200503142105.j2EL5mkj026255@hosting.commandprompt.com> <20050316201027.757D5F4126@estero.terra.com.br> Mime-Version: 1.0 Content-Type: text/plain; charset=us-ascii Content-Disposition: inline In-Reply-To: <20050316201027.757D5F4126@estero.terra.com.br> X-Virus-Scanned: by amavisd-new at hub.org X-Spam-Status: No, hits=0.004 tagged_above=0 required=5 tests=AWL X-Spam-Level: X-Archive-Number: 200503/347 X-Sequence-Number: 11052 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/