Received: from sd.tpf.co.jp (sd.tpf.co.jp [210.161.239.34]) by hub.org (8.9.3/8.9.3) with ESMTP id DAA93756 for ; Thu, 25 May 2000 03:13:00 -0400 (EDT) (envelope-from Inoue@tpf.co.jp) Received: from cadzone ([126.0.1.40] (may be forged)) by sd.tpf.co.jp (2.5 Build 2640 (Berkeley 8.8.6)/8.8.4) with SMTP id QAA02000; Thu, 25 May 2000 16:12:00 +0900 From: "Hiroshi Inoue" To: "Tom Lane" Cc: "Ryan Bradetich" , Subject: RE: Use of index in 7.0 vs 6.5 Date: Thu, 25 May 2000 16:19:54 +0900 Message-ID: <000e01bfc619$9fa5f9c0$2801007e@tpf.co.jp> MIME-Version: 1.0 Content-Type: text/plain; charset="iso-8859-1" Content-Transfer-Encoding: 7bit X-Priority: 3 (Normal) X-MSMail-Priority: Normal X-Mailer: Microsoft Outlook 8.5, Build 4.71.2173.0 In-Reply-To: <21530.959235278@sss.pgh.pa.us> X-MimeOLE: Produced By Microsoft MimeOLE V5.00.2314.1300 Importance: Normal X-Archive-Number: 200005/138 > -----Original Message----- > From: pgsql-sql-owner@hub.org [mailto:pgsql-sql-owner@hub.org]On Behalf > Of Tom Lane > [snip] > > This seems to point up (once again) the deficiency of assuming that > the most-common value in the table is a good guide to the frequency > of typical values. You showed that host_id = 446 occurs in 2.8% of > the rows in this table; a search for 446 very probably would be faster > as a seqscan than as an indexscan (you might care to try it and see). > But that's probably a statistical outlier that's not got much to do > with the frequency of typical values in the table. > > The only really good answer to this problem is to collect more-detailed > statistics in VACUUM ANALYZE, which I hope to see us doing in a release > or so. For example we could count up distinct values for the first column of an index by scanning its index relation. > In the meantime I am wondering about deliberately skewing the > cost model in favor of indexscans, because I sure haven't heard many > complaints about erroneous selection of indexscans... > > One way to put a thumb on the scales is to reduce the value of the SET > variable random_page_cost. The default value is 4.0, which seems to > correspond more or less to reality, but reducing it to 3 or so would > shift the planner pretty nicely in the direction of indexscans. > Or how about changing current fudge factor ? For example,from 0.5 to 0.2 which is the fudge factor of attdisbursion calculation. Regards. Hiroshi Inoue Inoue@tpf.co.jp