DomainKey-Signature: a=rsa-sha1; c=nofws; d=gmail.com; s=gamma;
	h=message-id:date:from:user-agent:mime-version:to:cc:subject
	:references:in-reply-to:content-type:content-transfer-encoding;
	b=lv9AJ/tM/eU7EGTufLYlF/gmKLC08uG8Be6q+k9L1FEnJvLGqzoswEkqJc9LSulmmN
	VWaYXLKVMcTnadxVHWcum+hKDax2sljSLHYwPecUwAfY8WfUlFEa44pibSZZhBYzxphO
	/Yn0dFLW7yMNVr1vHpa8nz2qvh0fszjwEz4g8=
Message-ID: <4CDD2DC1.80107@gmail.com>
Date: Fri, 12 Nov 2010 14:06:25 +0200
From: Vitalii Tymchyshyn <tivv00@gmail.com>
User-Agent: Mozilla/5.0 (X11; U; Linux x86_64; en-US;
	rv:1.9.1.15) Gecko/20101027 Thunderbird/3.0.10
MIME-Version: 1.0
To: =?UTF-8?B?Q8OpZHJpYyBWaWxsZW1haW4=?=
 <cedric.villemain.debian@gmail.com>
CC: pgsql-performance@postgresql.org
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>
	<4CDB7BAA.7020506@vmsinfo.com>
	<4CDBB0A40200002500037571@gw.wicourts.gov>
	<AANLkTi=dzhRTTNm1LkyNCAC5+PWbp1jWtKFh4T1Bu4nj@mail.gmail.com>
	<18450.1289499783@sss.pgh.pa.us> <19088.1289501929@sss.pgh.pa.us>
	<AANLkTi=dnDwLEyxienHs7ZGP1k18YQzaQMPY7KP-hjMT@mail.gmail.com>
	<19796.1289504156@sss.pgh.pa.us>
	<AANLkTi=c6ZvbgkzkRvtf2BTdzYoKGNhWU1CTRShsBoeN@mail.gmail.com>
	<4CDD1279.5090809@gmail.com>
	<AANLkTi=4XP-QRVtR5aRhC+OMjkJanvq-tp4YU6LO-zbP@mail.gmail.com>
In-Reply-To: <AANLkTi=4XP-QRVtR5aRhC+OMjkJanvq-tp4YU6LO-zbP@mail.gmail.com>
Content-Type: text/plain; charset=UTF-8; format=flowed
Content-Transfer-Encoding: 8bit

12.11.10 12:56, Cédric Villemain написав(ла):
> I supposed it was an answer to my mail but not sure... please keep
> CC'ed people, it is easier to follow threads (at least for me)
>    
OK
> 2010/11/12 Vitalii Tymchyshyn<tivv00@gmail.com>:
>    
>> I'd say there are two Qs here:
>>
>> 1) Modify costs based on information on how much of the table is in cache.
>> It would be great  if this can be done, but I'd prefer to have it as admin
>> knobs (because of plan stability). May be both admin and automatic ways can
>> be followed with some parallel (disableable) process modify knobs on admin
>> behalf. In this case different strategies to automatically modify knobs can
>> be applied.
>>      
> OS cache is usualy stable enough to keep your plans stable too, I think.
>    
Not if it is on edge. There are always edge cases where data fluctuates 
near some threshold.
>    
>> 2) Modify costs for part of table retrieval. Then you need to define "part".
>> Current ways are partitioning and partial indexes. Some similar to partial
>> index thing may be created, that has only "where" clause and no data. But
>> has statistics and knobs (and may be personal bufferspace if they are
>> introduced). I don't like to gather data about "last X percents" or like,
>> because it works only in clustering and it's hard for optimizer to decide if
>> it will be enough to scan only this percents for given query.
>>      
> Modifying random_page_cost and sequential_page_cost thanks to
> statistics about cached blocks can be improved if we know the
> distribution.
>
> It does not mean : we know we have last 15% in cache, and we are goign
> to request those 15%.
>    

You mean *_cost for the whole table, don't you? That is case (1) for me.
Case (2) is when different cost values are selected based on what 
portion of table is requested in the query. E.g. when we have data for 
the whole day in one table, data for the last hour is cached and all the 
other data is not. Optimizer then may use different *_cost for query 
that requires all the data and for query that requires only last hour 
data. But, as I've said, that is much more complex task then (1).

Best regards, Vitalii Tymchyshyn