Received: from wallace.ece.rice.edu (wallace.ece.rice.edu [128.42.12.154]) by mail.postgresql.org (8.11.1/8.11.1) with ESMTP id f18HKox05203 for ; Thu, 8 Feb 2001 12:20:51 -0500 (EST) (envelope-from reedstrm@rice.edu) Received: by rice.edu via sendmail from stdin id (Debian Smail3.2.0.102) for pgsql-sql@postgresql.org; Thu, 8 Feb 2001 11:20:45 -0600 (CST) Date: Thu, 8 Feb 2001 11:20:45 -0600 From: "Ross J. Reedstrom" To: Brice Ruth Cc: pgsql-sql@postgresql.org Subject: Re: Re: Query never returns ... Message-ID: <20010208112045.B5409@rice.edu> Mail-Followup-To: Brice Ruth , pgsql-sql@postgresql.org References: <3A82C15D.9375591A@webprojkt.com> <3A82C720.F1E3B8FF@webprojkt.com> Mime-Version: 1.0 Content-Type: text/plain; charset=us-ascii User-Agent: Mutt/1.0i In-Reply-To: <3A82C720.F1E3B8FF@webprojkt.com>; from brice@webprojkt.com on Thu, Feb 08, 2001 at 10:19:43AM -0600 X-Archive-Number: 200102/162 X-Sequence-Number: 1347 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: >