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 59ADA5298F for ; Tue, 19 Jul 2005 18:41:33 -0300 (ADT) 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 73838-02 for ; Tue, 19 Jul 2005 21:41:23 +0000 (GMT) Received: from netbox.unitech.com.ar (unknown [200.32.92.34]) by svr1.postgresql.org (Postfix) with ESMTP id 3418752989 for ; Tue, 19 Jul 2005 18:41:18 -0300 (ADT) Received: from dariop (dariop.unitech.com.ar [192.168.1.237]) by netbox.unitech.com.ar (8.11.6/8.11.6) with ESMTP id j6JMfZE00423 for ; Tue, 19 Jul 2005 18:41:36 -0400 Received: from 127.0.0.1 (AVG SMTP 7.0.323 [267.9.1]); Tue, 19 Jul 2005 18:41:18 -0300 From: "Dario" To: Subject: Re: join and query planner Date: Tue, 19 Jul 2005 18:41:18 -0300 Message-ID: MIME-Version: 1.0 Content-Type: text/plain; charset="US-ASCII" Content-Transfer-Encoding: 7bit X-Priority: 3 (Normal) X-MSMail-Priority: Normal X-Mailer: Microsoft Outlook IMO, Build 9.0.2416 (9.0.2911.0) X-MimeOLE: Produced By Microsoft MimeOLE V5.50.4952.2800 Importance: Normal In-Reply-To: X-Virus-Scanned: by amavisd-new at hub.org X-Spam-Status: No, hits=0.092 tagged_above=0 required=5 tests=AWL X-Spam-Level: X-Archive-Number: 200507/330 X-Sequence-Number: 13571 I'll try that. Let you know as soon as I can take a look. Thank you- -----Mensaje original----- De: pgsql-performance-owner@postgresql.org [mailto:pgsql-performance-owner@postgresql.org]En nombre de Kevin Grittner Enviado el: lunes, 18 de julio de 2005 17:48 Para: pgsql-performance@postgresql.org; dario_d_s@unitech.com.ar Asunto: Re: [PERFORM] join and query planner You might want to set join_collapse_limit high, and use the JOIN operators rather than the comma-separated lists. We generate the WHERE clause on the fly, based on user input, and this has worked well for us. -Kevin >>> "Dario" 07/18/05 2:24 PM >>> Hi. > Just out of curiosity, does it do any better with the following? > > SELECT ... Yes, it does. But my query could also be SELECT ... FROM a JOIN b ON (a.key = b.key) LEFT JOIN c ON (c.key = a.key) LEFT JOIN d ON (d.key=a.key) /*new*/ , e WHERE (b.column <= 100) /*new*/ and (e.key = a.key) and (e.field = 'filter') because it's constructed by an application. I needed to know if, somehow, someway, I can "unforce" join order. The only way to solve it so far is changing application. It must build something like SELECT ... FROM b JOIN (a JOIN e ON (e.key = a.key)) ON (a.key = b.key) LEFT JOIN c ON (c.key = a.key) LEFT JOIN d ON (d.key=a.key) WHERE (b.column <= 100) and (e.field = 'filter') Supossed that e.field has (should have) better selectivity. But now this problem belongs to programmer's group :-) The query, in fact, has more tables to join. I wonder if lowering geqo threshold could do the work... Thank you. Greetings. Long life, little spam and prosperity! -----Mensaje original----- De: pgsql-performance-owner@postgresql.org [mailto:pgsql-performance-owner@postgresql.org]En nombre de Kevin Grittner Enviado el: lunes, 18 de julio de 2005 14:58 Para: pgsql-performance@postgresql.org; dariop@unitech.com.ar Asunto: Re: [PERFORM] join and query planner Just out of curiosity, does it do any better with the following? SELECT ... FROM a JOIN b ON (a.key = b.key) LEFT JOIN c ON (c.key = a.key) LEFT JOIN d ON (d.key=a.key) WHERE (b.column <= 100) >>> snipp ---------------------------(end of broadcast)--------------------------- TIP 5: don't forget to increase your free space map settings ---------------------------(end of broadcast)--------------------------- TIP 9: In versions below 8.0, the planner will ignore your desire to choose an index scan if your joining column's datatypes do not match