X-Original-To: pgsql-performance-postgresql.org@localhost.postgresql.org Received: from localhost (unknown [200.46.204.2]) by svr1.postgresql.org (Postfix) with ESMTP id B5887D1B559 for ; Thu, 30 Oct 2003 15:16:11 +0000 (GMT) Received: from svr1.postgresql.org ([200.46.204.71]) by localhost (neptune.hub.org [200.46.204.2]) (amavisd-new, port 10024) with ESMTP id 73990-01 for ; Thu, 30 Oct 2003 11:15:44 -0400 (AST) Received: from news.hub.org (news.hub.org [200.46.204.72]) by svr1.postgresql.org (Postfix) with ESMTP id 53E68D1B54C for ; Thu, 30 Oct 2003 11:15:40 -0400 (AST) Received: from news.hub.org (news.hub.org [200.46.204.72]) by news.hub.org (8.12.9/8.12.9) with ESMTP id h9UFFdNw064312 for ; Thu, 30 Oct 2003 15:15:39 GMT (envelope-from news@news.hub.org) Received: (from news@localhost) by news.hub.org (8.12.9/8.12.9/Submit) id h9UFCPBv063653 for pgsql-performance@postgresql.org; Thu, 30 Oct 2003 15:12:25 GMT From: Christopher Browne X-Newsgroups: comp.databases.postgresql.performance Subject: Re: Query puts 7.3.4 on endless loop but 7.4beta5 is fine. [ with better indenting ] Date: Thu, 30 Oct 2003 10:10:47 -0500 Organization: cbbrowne Computing Inc Lines: 32 Message-ID: References: <33298.203.145.130.142.1067457626.squirrel@mail.trade-india.com> <20031029202920.GZ7337@pallas.fsck.com> <33402.203.145.130.142.1067460429.squirrel@mail.trade-india.com> <200310301702.00275.mallah@trade-india.com> <13042.61.16.154.82.1067523120.squirrel@mail.trade-india.com> Mime-Version: 1.0 Content-Type: text/plain; charset=us-ascii X-Complaints-To: usenet@news.hub.org X-message-flag: Outlook is rather hackable, isn't it? X-Home-Page: http://www.cbbrowne.com/info/ X-Affero: http://svcs.affero.net/rm.php?r=cbbrowne User-Agent: Gnus/5.1002 (Gnus v5.10.2) XEmacs/21.4 (Reasonable Discussion, linux) Cancel-Lock: sha1:W9i3b8qbxcDaHOvSI2NO/CWlbr8= To: pgsql-performance@postgresql.org X-Virus-Scanned: by amavisd-new at postgresql.org X-Archive-Number: 200310/758 X-Sequence-Number: 4506 In the last exciting episode, mallah@trade-india.com wrote: >> mallah@trade-india.com (Rajesh Kumar Mallah) wrote: >>> Can you please have a Look at the below and suggest why it >>> apparently puts 7.3.4 on an infinite loop . the CPU utilisation of the backend running it >>> approches 99%. >> >> What would be useful, for this case, would be to provide the query plan, perhaps via >> >> EXPLAIN [Big Long Query]. >> >> The difference between that EXPLAIN and what you get on 7.4 might be quite interesting. >> >> I would think it quite unlikely that it is truly an "infinite" loop; it is rather more likely >> that the plan winds up being pretty bad and doing something [a bunch of nested loops, maybe?] >> that run longer than your patience will permit. > > :-) ok i will leave it running and try to get it. No, if you just do EXPLAIN (and not EXPLAIN ANALYZE), that returns without executing the query. If the query runs for a really long time, then we _know_ that there is something troublesome. EXPLAIN (no ANALYZE) should provide some insight without having anything run for a long time. If EXPLAIN [big long query] turns into what you are terming an "infinite loop," then you have a quite different problem, and it would be very useful to know that. -- "cbbrowne","@","ntlug.org" http://www3.sympatico.ca/cbbrowne/oses.html This is Linux country. On a quiet night, you can hear NT re-boot.