Message-ID: <4CDC2424.9010102@vmsinfo.com>
Date: Thu, 11 Nov 2010 12:13:08 -0500
From: Mladen Gogala <mladen.gogala@vmsinfo.com>
Organization: VMS
User-Agent: Thunderbird 2.0.0.24 (X11/20101027)
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>
	<4CDB7BAA.7020506@vmsinfo.com>
	<4CDBB0A40200002500037571@gw.wicourts.gov>
In-Reply-To: <4CDBB0A40200002500037571@gw.wicourts.gov>
Content-Type: text/plain; charset=ISO-8859-1; format=flowed
Content-Transfer-Encoding: 7bit

Kevin Grittner wrote:
> Mladen Gogala <mladen.gogala@vmsinfo.com> wrote:
>  
>   
>> create a definitive bias toward one type of the execution plan.
>>     
>  
> We're talking about trying to support the exact opposite.  
I understand this, that is precisely the reason for my intervention into 
the discussion of experts, which I am not.
> This all
> started because a database which was tuned for good response time
> for relatively small queries against a "hot" portion of some tables
> chose a bad plan for a weekend maintenance run against the full
> tables.  We're talking about the possibility of adapting the cost
> factors based on table sizes as compared to available cache, to more
> accurately model the impact of needing to do actual disk I/O for
> such queries.
>   
Kevin, in my experience, the hardest thing to do is to tune so called 
mixed type databases. In practice, databases are usually separated: OLTP 
database on one group of servers, reporting database and the data 
warehouse on another group of servers. Postgres 9.0 has made a great 
stride toward such possibility with the new replication facilities. 
Again, having an optimizer which will choose the plan completely 
accurately is, at least in my opinion, less important than having a 
possibility of manual control, the aforementioned "knobs and buttons" 
and produce the same plan for the same statement. Trying to make the 
optimizer smart enough for all types of loads is akin to looking for the 
Holy Grail. Inevitably, you will face some hard questions, like the one 
about the airspeed velocity of an unladen swallow, and the whole search 
is likely to end in pretty funny way, not producing the desired 
"optimizing genie in the CPU".
>  
> This also is very different from trying to adapt queries to what
> happens to be currently in cache.  As already discussed on a recent
> thread, the instability in plans and the failure to get to an
> effective cache set make that a bad idea.  The idea discussed here
> would maintain a stable plan for a given query, it would just help
> choose a good plan based on the likely level of caching.
>   
Kevin, I am talking from the perspective of a DBA who is involved with a 
production databases on day-to-day basis. I am no expert but I do 
believe to speak from a perspective of users that Postgres has to win in 
order to make further inroads into the corporate server rooms. Without 
the possibility of such control and the plan stability, it is hard for 
me to recommend more extensive use of PostgreSQL to my boss. Whatever 
solution is chosen, it needs to have "knobs and buttons" and produce the 
plans that will not change when the CPU usage goes up.

-- 
 
Mladen Gogala 
Sr. Oracle DBA
1500 Broadway
New York, NY 10036
(212) 329-5251
http://www.vmsinfo.com 
The Leader in Integrated Media Intelligence Solutions