Received: from nexus.kearneydev.com (IDENT:root@nexus.kearneydev.com [208.136.214.2]) by hub.org (8.9.3/8.9.3) with ESMTP id KAA40378 for ; Wed, 7 Jul 1999 10:56:39 -0400 (EDT) (envelope-from secret@kearneydev.com) Received: from kearneydev.com (atu.kearneydev.com [208.136.214.93]) by nexus.kearneydev.com (8.8.5/8.8.5) with ESMTP id KAA23304; Wed, 7 Jul 1999 10:56:26 -0400 Message-ID: <37836A99.6989795E@kearneydev.com> Date: Wed, 07 Jul 1999 10:56:26 -0400 From: secret Organization: Kearney Development Co., Inc. X-Mailer: Mozilla 4.51 [en] (WinNT; U) X-Accept-Language: en MIME-Version: 1.0 To: PG-SQL , John Ridout Subject: Good Optimization Content-Type: text/plain; charset=us-ascii Content-Transfer-Encoding: 7bit There is a simple way to optimize SQL queries involving joins to PostgreSQL that I think should be handled by Postgre? If one is joining a tables a,b on attribute "x" and if one has something like x=3 then it helps A LOT to say: a.x=3 and b.x=3 in addition to saying a.x=b.x ... The example below shoulds the radical speed gain of doing this, and I think it isn't something real obvious to most people... Of course it could just be a common thing to do in SQL, anyway, just thought I'd let you all know what I discovered. Here is an example: ftc=> explain select * from po,tickets where po_id=material_po and po_id=8888 ; NOTICE: QUERY PLAN: Nested Loop (cost=401.34 size=6146 width=158) -> Index Scan using ipo_po_id_units on po (cost=2.05 size=2 width=94) -> Index Scan using itickets_mpou on tickets (cost=199.64 size=70650 width=6 4) EXPLAIN ftc=> ftc=> explain select * from po,tickets where po_id=material_po and po_id=8888 an d material_po=8888; NOTICE: QUERY PLAN: Nested Loop (cost=21.42 size=268 width=158) -> Index Scan using ipo_po_id_units on po (cost=2.05 size=2 width=94) -> Index Scan using itickets_material_po on tickets (cost=9.68 size=3073 wid th=64) EXPLAIN ftc=>