Message-ID: <43A2CCEF.9030605@kylecordes.com>
Date: Fri, 16 Dec 2005 08:19:27 -0600
From: Kyle Cordes <kyle@kylecordes.com>
User-Agent: Mozilla Thunderbird 1.0.7 (Windows/20050923)
MIME-Version: 1.0
To: pgsql-performance@postgresql.org
Subject: Re: Overriding the optimizer
References: <200512141603.52583.blargity@gmail.com>
	<7484.1134600423@sss.pgh.pa.us>
	<1134629567.14248.27.camel@firebolt>
	<200512150148.16125.blargity@gmail.com>
	<1134644526.14248.60.camel@firebolt>
	<43A1C40D.8040006@paradise.net.nz>
	<43A1F6DB.3080805@modgraph-usa.com>
	<43A226E7.7040508@paradise.net.nz>
	<43A22BE3.6020103@modgraph-usa.com> <20051216052025.GB691@filer>
In-Reply-To: <20051216052025.GB691@filer>
Content-Type: text/plain; charset=ISO-8859-1; format=flowed
Content-Transfer-Encoding: 7bit

Kevin Brown wrote:

>Craig A. James wrote:
>  
>
>>Hints are dangerous, and I consider them a last resort.
>>    
>>
>
>If you consider them a last resort, then why do you consider them to
>be a better alternative than a workaround such as turning off
>enable_seqscan, when all the other tradeoffs are considered?
>  
>

I would like a bit finer degree of control on this - I'd like to be able 
to tell PG that for my needs, it is never OK to scan an entire table of 
more than N rows.  I'd typically set N to 1,000,000 or so.  What I would 
really like is for my DBMS to give me a little more pushback - I'd like 
to ask it to run a query, and have it either find a "good" way to run 
the query, or politely refuse to run it at all.

Yes, I know that is an unusual request  :-)

The context is this - in a busy OLTP system, sometimes a query comes 
through that, for whatever reason (foolishness on my part as a 
developer, unexpected use by a user, imperfection of the optimizer, 
etc.), takes a really long time to run, usually because it table-scans 
one or more large tables.  If several of these happen at once, it can 
grind an important production system effectively to a halt.  I'd like to 
have a few users/operations get a "sorry, I couldn't find a good way to 
do that" message, rather than all the users find that their system has 
effectively stopped working.

Kyle Cordes
www.kylecordes.com