Received: from maia.hub.org (maia-2.hub.org [200.46.204.251])
	by mail.postgresql.org (Postfix) with ESMTP id 30BE91336B42
	for <pgsql-performance-postgresql.org@mail.postgresql.org>;
	Thu, 11 Nov 2010 01:14:27 -0400 (AST)
Received: from mail.postgresql.org ([200.46.204.86])
	by maia.hub.org (mx1.hub.org [200.46.204.251]) (amavisd-maia,
	port 10024) with ESMTP id 68301-07
	for <pgsql-performance-postgresql.org@mail.postgresql.org>;
	Thu, 11 Nov 2010 05:14:20 +0000 (UTC)
X-Greylist: from auto-whitelisted by SQLgrey-1.7.6
Received: from hrndva-omtalb.mail.rr.com (hrndva-omtalb.mail.rr.com
	[71.74.56.122])
	by mail.postgresql.org (Postfix) with ESMTP id D23B5133616B
	for <pgsql-performance@postgresql.org>;
	Thu, 11 Nov 2010 01:14:19 -0400 (AST)
X-Authority-Analysis: v=1.1 cv=+c36koQ5Dcj/1qolKHjtkYAGXvrVJRRiKMp+84F5sLg=
	c=1 sm=0 a=vkuk7Pryd0cA:10 a=RAii6je2cdnAH6ATCOpc6g==:17
	a=_lsggufOAAAA:20 a=_Ixf_7qHAAAA:8 a=S3uQyyR-rfxAoe1KvWAA:9
	a=9uKDlP1eZ21m-R3DQRQA:7 a=hp4ILrr4E1inFYVOj4tnLYK7-PIA:4
	a=wPNLvfGTeEIA:10 a=ryAo9hy1-OkA:10 a=6vsjEBoLwlAA:10
	a=0tfO7MgyNwYA:10 a=aCKbt__JLEAyLMx-elEA:9
	a=S_mRrubV3jfUDsCshzAA:7 a=6yvgKWNCOqGmxQdDawE58ZoWxQsA:4
	a=RAii6je2cdnAH6ATCOpc6g==:117
X-Cloudmark-Score: 0
X-Originating-IP: 74.73.87.196
Received: from [74.73.87.196] ([74.73.87.196:49891] helo=[192.168.1.103])
	by hrndva-oedge03.mail.rr.com (envelope-from
	<mladen.gogala@vmsinfo.com>) (ecelerity 2.2.3.46 r()) with ESMTP
	id 80/23-24070-9AB7BDC4; Thu, 11 Nov 2010 05:14:18 +0000
Message-ID: <4CDB7BAA.7020506@vmsinfo.com>
Date: Thu, 11 Nov 2010 00:14:18 -0500
From: Mladen Gogala <mladen.gogala@vmsinfo.com>
User-Agent: Mozilla/5.0 (Windows; U; Windows NT 6.0; en-US;
	rv:1.9.2.12) Gecko/20101027 Lightning/1.0b2 Thunderbird/3.1.6
MIME-Version: 1.0
To: Kevin Grittner <Kevin.Grittner@wicourts.gov>
CC: Robert Haas <robertmhaas@gmail.com>,
	"pgsql-performance@postgresql.org" <pgsql-performance@postgresql.org>,
	Tom Lane <tgl@sss.pgh.pa.us>
Subject: Re: anti-join chosen even when slower than old plan
References: <4CD9663502000025000374B1@gw.wicourts.gov>
	<20961.1289347680@sss.pgh.pa.us>
	<4CDA62C60200002500037512@gw.wicourts.gov>
	<AANLkTi=eNXKEBo5dXJDd4+aHhfmkibH++fqjj12+DA3o@mail.gmail.com>
	<4CDACBBF020000250003755B@gw.wicourts.gov>
In-Reply-To: <4CDACBBF020000250003755B@gw.wicourts.gov>
Content-Type: multipart/alternative;
	boundary="------------090909090705010807060503"
X-Virus-Scanned: Maia Mailguard 1.0.1
X-Spam-Status: No, hits=-1.899 tagged_above=-10 required=5
	tests=BAYES_00=-1.9, HTML_MESSAGE=0.001, RCVD_IN_DNSWL_NONE=-0.0001
X-Spam-Level: 
X-Archive-Number: 201011/142
X-Sequence-Number: 41166

This is a multi-part message in MIME format.
--------------090909090705010807060503
Content-Type: text/plain; charset=ISO-8859-1; format=flowed
Content-Transfer-Encoding: 7bit

On 11/10/2010 5:43 PM, Kevin Grittner wrote:
> The only half-sane answer I've thought of is to apply a different
> cost to full-table or full-index scans based on the ratio with
> effective cache size.

The "effective_cache_size" is, in my humble opinion, a wrong method.  It 
would be much easier to have a parameter, let's call it 
"optimizer_index_caching", which would give the assumption of the 
percentage of an index that is cached. In other words, if 
"optimizer_index_caching" was set to 80, the optimizer would assume that 
80% of any index is cached and would apply different cost estimate. It's 
not exact but it's simple and modifiable. It would also be a great tool 
in the hands of the DBA which has to manage OLTP database or DW database 
and would be able to create a definitive bias toward one type of the 
execution plan.
I have to confess that the idea about such parameter is not entirely 
mine:*http://tinyurl.com/33gu4f6*

-- 
Mladen Gogala
Sr. Oracle DBA
1500 Broadway
New York, NY 10036
(212) 329-5251
www.vmsinfo.com


--------------090909090705010807060503
Content-Type: text/html; charset=ISO-8859-1
Content-Transfer-Encoding: 7bit

<!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 4.01 Transitional//EN">
<html>
  <head>
    <meta content="text/html; charset=ISO-8859-1"
      http-equiv="Content-Type">
    <title></title>
  </head>
  <body bgcolor="#ffffff" text="#000000">
    On 11/10/2010 5:43 PM, Kevin Grittner wrote:
    <blockquote cite="mid:4CDACBBF020000250003755B@gw.wicourts.gov"
      type="cite">
      <pre wrap="">The only half-sane answer I've thought of is to apply a different
cost to full-table or full-index scans based on the ratio with
effective cache size. </pre>
    </blockquote>
    <br>
    The "effective_cache_size" is, in my humble opinion, a wrong
    method.&nbsp; It would be much easier to have a parameter, let's call it
    "optimizer_index_caching", which would give the assumption of the
    percentage of an index that is cached. In other words, if
    "optimizer_index_caching" was set to 80, the optimizer would assume
    that 80% of any index is cached and would apply different cost
    estimate. It's not exact but it's simple and modifiable. It would
    also be a great tool in the hands of the DBA which has to manage
    OLTP database or DW database and would be able to create a
    definitive bias toward one type of the execution plan. <br>
    I have to confess that the idea about such parameter is not entirely
    mine:<b> <a class="moz-txt-link-freetext" href="http://tinyurl.com/33gu4f6">http://tinyurl.com/33gu4f6</a></b><br>
    <br>
    <pre class="moz-signature" cols="72">-- 
Mladen Gogala 
Sr. Oracle DBA
1500 Broadway
New York, NY 10036
(212) 329-5251
<a class="moz-txt-link-abbreviated" href="http://www.vmsinfo.com">www.vmsinfo.com</a> 
</pre>
  </body>
</html>

--------------090909090705010807060503--