X-Original-To: pgsql-performance-postgresql.org@localhost.postgresql.org Received: from localhost (neptune.hub.org [200.46.204.2]) by svr1.postgresql.org (Postfix) with ESMTP id C8FC7D1B492 for ; Thu, 27 Nov 2003 00:09:49 +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 97927-04 for ; Wed, 26 Nov 2003 20:09:27 -0400 (AST) Received: from sss.pgh.pa.us (unknown [192.204.191.242]) by svr1.postgresql.org (Postfix) with ESMTP id D98CED1B45C for ; Wed, 26 Nov 2003 20:09:18 -0400 (AST) Received: from sss2.sss.pgh.pa.us (tgl@localhost [127.0.0.1]) by sss.pgh.pa.us (8.12.10/8.12.10) with ESMTP id hAR09P19018734; Wed, 26 Nov 2003 19:09:25 -0500 (EST) To: Roger Ging Cc: pgsql-performance@postgresql.org Subject: Re: Followup - expression (functional) index use in joins In-reply-to: <3FC51B2C.2030700@paccomsys.com> References: <3FC4F360.2090609@paccomsys.com> <200311261912.01286.dev@archonet.com> <3FC51B2C.2030700@paccomsys.com> Comments: In-reply-to Roger Ging message dated "Wed, 26 Nov 2003 13:29:16 -0800" Date: Wed, 26 Nov 2003 19:09:25 -0500 Message-ID: <18733.1069891765@sss.pgh.pa.us> From: Tom Lane X-Virus-Scanned: by amavisd-new at postgresql.org X-Archive-Number: 200311/283 X-Sequence-Number: 4843 Roger Ging writes: > Ran vacuum analyse on both program and logfile tables. Estimates are > more in line with reality now, And they are what now? You really can't expect to get useful help here when you're being so miserly with the details ... FWIW, I suspect you could force 7.4 to generate 7.3's plan by setting enable_mergejoin to off (might have to also set enable_hashjoin to off, if it then tries for a hash join). 7.3 could not even consider those join types in this example, while 7.4 can. The interesting question from my perspective is why the planner is guessing wrong about the relative costs of the plans. EXPLAIN ANALYZE results with each type of join forced would be useful to look at. regards, tom lane