Received: from nexus.kearneydev.com (IDENT:root@nexus.kearneydev.com [208.136.214.2]) by hub.org (8.9.2/8.9.1) with ESMTP id NAA76563 for ; Tue, 9 Mar 1999 13:32:15 -0500 (EST) (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 NAA01058 for ; Tue, 9 Mar 1999 13:32:13 -0500 Message-ID: <36E56930.7F57299D@kearneydev.com> Date: Tue, 09 Mar 1999 13:32:16 -0500 From: secret Organization: Kearney Development Co., Inc. X-Mailer: Mozilla 4.5 [en] (WinNT; U) X-Accept-Language: en MIME-Version: 1.0 To: PG-SQL Subject: How to optimize a query... Content-Type: text/plain; charset=us-ascii Content-Transfer-Encoding: 7bit I originally had this query with an OR clause instead of the UNION, which made it amazingly impossibly slow, with the UNION it's far faster however it's still very slow,(10,000+ rows in both stables), is there a way to speed this up? I have BTREE indexes on all concerned fields of the tables, but it doesn't seem to use any of them... I have another query that joins the result of this with 5 other tables, unfortunately that one takes like 10 minutes... Any suggestions on optimizations would be very appreciated. ftc=> explain ftc-> select po_id,ticket_pk ftc-> from tickets,po ftc-> where po_id=material_po ftc-> union ftc-> select po_id,ticket_pk ftc-> from tickets,po ftc-> where po_id=trucking_po ftc-> ; NOTICE: QUERY PLAN: Unique (cost=4744.05 size=0 width=0) -> Sort (cost=4744.05 size=0 width=0) -> Append (cost=4744.05 size=0 width=0) -> Hash Join (cost=2372.03 size=11659303 width=12) -> Seq Scan on tickets (cost=849.03 size=19213 width=8) -> Hash (cost=0.00 size=0 width=0) -> Seq Scan on po (cost=528.98 size=10848 width=4) -> Hash Join (cost=2372.03 size=13838477 width=12) -> Seq Scan on tickets (cost=849.03 size=19213 width=8) -> Hash (cost=0.00 size=0 width=0) -> Seq Scan on po (cost=528.98 size=10848 width=4) EXPLAIN