Received: from morpheus.webprojkt.com (morpheus.webprojkt.com [209.61.155.148]) by mail.postgresql.org (8.11.1/8.11.1) with SMTP id f18HZQx09302 for ; Thu, 8 Feb 2001 12:35:26 -0500 (EST) (envelope-from brice@webprojkt.com) Received: (qmail 2508 invoked by uid 604); 8 Feb 2001 17:35:25 -0000 Received: from unknown (HELO webprojkt.com) (216.170.131.123) by morpheus.webprojkt.com with SMTP; 8 Feb 2001 17:35:25 -0000 Message-ID: <3A82D8DC.9CC273D5@webprojkt.com> Date: Thu, 08 Feb 2001 11:35:24 -0600 From: Brice Ruth X-Mailer: Mozilla 4.76 (Macintosh; U; PPC) X-Accept-Language: en MIME-Version: 1.0 To: "Ross J. Reedstrom" CC: pgsql-sql@postgresql.org Subject: Re: Re: Query never returns ... References: <3A82C15D.9375591A@webprojkt.com> <3A82C720.F1E3B8FF@webprojkt.com> <20010208112045.B5409@rice.edu> Content-Type: text/plain; charset=us-ascii Content-Transfer-Encoding: 7bit X-Archive-Number: 200102/164 X-Sequence-Number: 1350 Ross, Thanx to Stephan's help, I found out that after loading the tables w/ data, I had to run vacuum analyze to inform the optimizer of the amount of data in the table (amongst other things, I imagine). After running that on all the tables, the query performs fine. -Brice "Ross J. Reedstrom" wrote: > > Brice - > What does EXPLAIN say for that query? With empty tables, I get two index scans, > a merge join, and two sorts. I'm guessing wildly that you've got a non-optimal > sort strategy happening somehow, given the four fold ORDER BY clause. > > Ross > > Here's the empty version: > > NOTICE: QUERY PLAN: > > Sort (cost=84.25..84.25 rows=100 width=64) > -> Merge Join (cost=8.30..80.93 rows=100 width=64) > -> Index Scan using pk_tblmedcond on tblmedcond (cost=0.00..60.00 rows=1000 width=36) > -> Sort (cost=8.30..8.30 rows=10 width=28) > -> Index Scan using pk_tblsidedruglink on tblsidedruglink (cost=0.00..8.14 rows=10 width=28) > > EXPLAIN > > On Thu, Feb 08, 2001 at 10:19:43AM -0600, Brice Ruth wrote: > > FYI - I let the query run for 11.5 minutes before killing it off. It > > had consumed 11 minutes, 18 seconds of CPU time (reported by ps). The > > following messages are from the server log, I'm pasting in all messages > > directly following the query: > > -- Brice Ruth WebProjkt, Inc. VP, Director of Internet Technology http://www.webprojkt.com/