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 KAA13110 for ; Wed, 10 Mar 1999 10:38:01 -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 KAA12324 for ; Wed, 10 Mar 1999 10:38:00 -0500 Message-ID: <36E691DC.3EA90B99@kearneydev.com> Date: Wed, 10 Mar 1999 10:38:04 -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: Re: [SQL] How to optimize a query... References: <36E56930.7F57299D@kearneydev.com> Content-Type: text/plain; charset=us-ascii Content-Transfer-Encoding: 7bit secret wrote: > 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 I'm having a lot of problems with performance under PostgreSQL, it seems most of my major queries arn't using indexes(as above)... tickets has 15k tables, 10k tables... The above query takes about 4 minutes... Unfortunately I need to add in joins to about 5 tables.... If I add one more table it's 10 minutes... 2? Who knows, I know by 5 it's unacceptable, can anyone please give me some advise on what my problems might be with queries? I've done VACUUM ANALYZE ... I didn't used to do that, could it be missing data from way back? Should I reload the entire database? --David