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 9579255720 for ; Wed, 16 Mar 2005 20:10:48 +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 88382-04 for ; Wed, 16 Mar 2005 20:10:29 +0000 (GMT) Received: from linares.terra.com.br (linares.terra.com.br [200.154.55.228]) by svr1.postgresql.org (Postfix) with ESMTP id 2688656561 for ; Wed, 16 Mar 2005 20:10:28 +0000 (GMT) Received: from estero.terra.com.br (estero.terra.com.br [200.154.55.138]) by linares.terra.com.br (Postfix) with ESMTP id C964FDDD0C4 for ; Wed, 16 Mar 2005 17:10:28 -0300 (BRT) X-Terra-Karma: -2% X-Terra-Hash: 8cbf0ac0ec4001ae18f3216749fabd71 Received-SPF: pass (estero.terra.com.br: domain of terra.com.br designates 200.154.55.138 as permitted sender) client-ip=200.154.55.138; envelope-from=rodrigo.miguel@terra.com.br; helo=rmsmnote; Received: from rmsmnote (unknown [200.207.51.149]) (authenticated user rodrmigu) by estero.terra.com.br (Postfix) with ESMTP id 757D5F4126 for ; Wed, 16 Mar 2005 17:10:27 -0300 (BRT) From: "Rodrigo Moreno" To: Subject: Help to find out problem with joined tables Date: Wed, 16 Mar 2005 17:10:17 -0300 MIME-Version: 1.0 Content-Type: text/plain; charset="us-ascii" Content-Transfer-Encoding: 7bit X-Mailer: Microsoft Office Outlook, Build 11.0.5510 X-MimeOLE: Produced By Microsoft MimeOLE V6.00.2600.0000 Thread-Index: AcUo2ejgW/rXrgWtRJCZrWmu7U1CHgBiUP2w In-Reply-To: <200503142105.j2EL5mkj026255@hosting.commandprompt.com> Message-Id: <20050316201027.757D5F4126@estero.terra.com.br> X-Virus-Scanned: by amavisd-new at hub.org X-Spam-Status: No, hits=1.376 tagged_above=0 required=5 tests=DNS_FROM_RFC_POST X-Spam-Level: * X-Archive-Number: 200503/341 X-Sequence-Number: 11046 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