X-Original-To: pgsql-sql-postgresql.org@localhost.postgresql.org Received: from localhost (unknown [200.46.204.144]) by svr1.postgresql.org (Postfix) with ESMTP id 35998329CD2 for ; Wed, 15 Sep 2004 02:21:45 +0100 (BST) Received: from svr1.postgresql.org ([200.46.204.71]) by localhost (av.hub.org [200.46.204.144]) (amavisd-new, port 10024) with ESMTP id 01912-10 for ; Wed, 15 Sep 2004 01:21:32 +0000 (GMT) Received: from fw2.faithwestinc.com (fw2.faithwestinc.com [63.192.50.118]) by svr1.postgresql.org (Postfix) with ESMTP id 610EF329C63 for ; Wed, 15 Sep 2004 02:21:30 +0100 (BST) Received: from [10.67.1.49] ([10.67.1.49]) by fw2.faithwestinc.com with Microsoft SMTPSVC(5.0.2195.6713); Tue, 14 Sep 2004 18:21:34 -0700 Message-ID: <41479917.7010305@faithwestinc.com> Date: Tue, 14 Sep 2004 18:21:27 -0700 From: Robert Davis User-Agent: Mozilla/5.0 (X11; U; Linux i686; en-US; rv:1.7.2) Gecko/20040805 Netscape/7.2 X-Accept-Language: en-us, en MIME-Version: 1.0 To: pgsql-sql@postgresql.org Subject: explain analyze results are different for each iteration Content-Type: text/plain; charset=us-ascii; format=flowed Content-Transfer-Encoding: 7bit X-OriginalArrivalTime: 15 Sep 2004 01:21:34.0937 (UTC) FILETIME=[57219C90:01C49AC2] X-Virus-Scanned: by amavisd-new at hub.org X-Spam-Status: No, hits=0.0 tagged_above=0.0 required=5.0 tests= X-Spam-Level: X-Archive-Number: 200409/100 X-Sequence-Number: 18946 I'm trying to benchmark some complex sql queries. One query, in particular, is causing problems -- its cost values can vary from 228 to 907, its Total Runtimes from 60 ms to 5176 ms. The query plans show that the optimizer is choosing different plans for different iterations of the same query. Does anyone have any idea what's going on here or maybe how I can force Postgres to prefer the faster plan? The query plans are very long (11 tables joined) but I can include them if it might help. Thanks for any advice, Roberto