X-Original-To: pgsql-performance-postgresql.org@localhost.postgresql.org Received: from localhost (unknown [64.117.224.130]) by svr1.postgresql.org (Postfix) with ESMTP id 7A0C0D1B561 for ; Sun, 31 Aug 2003 00:23:45 +0000 (GMT) Received: from svr1.postgresql.org ([64.117.224.193]) by localhost (neptune.hub.org [64.117.224.130]) (amavisd-new, port 10024) with ESMTP id 73817-04 for ; Sat, 30 Aug 2003 21:23:38 -0300 (ADT) Received: from pass.bivio.com (locker.bivio.com [216.87.82.238]) by svr1.postgresql.org (Postfix) with ESMTP id 3F063D1B56B for ; Sat, 30 Aug 2003 21:23:34 -0300 (ADT) Received: from LIFT.bivio.com (lift.bivio.com [216.87.82.233]) by pass.bivio.com (8.9.3/8.9.3) with ESMTP id SAB25639; Sat, 30 Aug 2003 18:23:37 -0600 From: Rob Nagler MIME-Version: 1.0 Content-Type: text/plain; charset=us-ascii Content-Transfer-Encoding: 7bit Message-ID: <16209.15488.141000.729032@gargle.gargle.HOWL> Date: Sat, 30 Aug 2003 18:08:32 -0600 To: pgsql-performance@postgresql.org Subject: Re: How to force Nested Loop plan? In-Reply-To: <4040.1062281441@sss.pgh.pa.us> References: <16208.41885.960000.12706@gargle.gargle.HOWL> <24637.1062255721@sss.pgh.pa.us> <16208.50934.351000.90704@gargle.gargle.HOWL> <2074.1062260382@sss.pgh.pa.us> <16209.3105.94000.204936@gargle.gargle.HOWL> <4040.1062281441@sss.pgh.pa.us> X-Mailer: VM 6.96 under Emacs 21.1.1 Organization: bivio Software Artisans, Inc. X-Virus-Scanned: by amavisd-new at postgresql.org X-Archive-Number: 200308/547 X-Sequence-Number: 3363 Tom Lane writes: > Keep in mind though that you seem to be experimenting with a > fully-cached database; you may find that the planner's beliefs more > nearly approach reality when actual I/O has to occur. My hope is that the entire database should fit in memory. This may not be in the case right now with only 1GB, but it should be close. The pgsql/data/base/NNN directory is about 1.5GB on production. I'm pretty sure with constant vacuuming, we could keep that size down. A pgdump is about 60MB now, growing at about .5MB a day. > Another thing I'd be interested to know about is how closely the > physical order of the table entries correlates with min_date_time. Probably "pretty close". The primary key of aa_t is (bb_id, server_id), and bb_id is a sequence. aa_t is updated heavily on production, but these tests are on a fresh import so vacuuming and index order is not a factor. We do a reload every now and then to improve performance on production. min_date_time is highly correlated with bb_id, because both are increasing constantly. server_id is one of 16 values. > A high correlation reduces the actual cost of the indexscan (since > visiting the rows in index order becomes less of a random-access > proposition). We are aware that the planner doesn't model this effect > very well at present ... Oracle's optimizer is lacking here, too. The best optimizer I've seen was at Tandem, and even then hints were required. Are there plans for explicit hints to the planner? Thanks, Rob