Re: Planner hints in Postgresql

On Mon, Mar 17, 2014 at 7:09 PM, Jim Nasby <jim(at)nasby(dot)net> wrote:

> On 3/17/14, 2:16 PM, Merlin Moncure wrote:
>
>> On Mon, Mar 17, 2014 at 1:45 PM, Pavel Stehule<pavel(dot)stehule(at)gmail(dot)com>
>> wrote:
>>
>>> >I don't believe so SELECTIVITY can work well too. Slow queries are
>>> usually
>>> >related to some strange points in data. I am thinking so well concept
>>> should
>>> >be based on validity of estimations. Some plans are based on totally
>>> wrong
>>> >estimation, but should be fast due less sensitivity to bad estimations.
>>> So
>>> >well concept is penalization some risk plans - or use brute force - like
>>> >COLUMN store engine does. Their plan is usually simply and tolerant to
>>> bad
>>> >estimations.
>>>
>> Disagree. There is a special case of slow query where problem is not
>> with the data but with the expression over the data; something in the
>> query defeats sampled selectivity. Common culprits are:
>>
>> *) CASE expressions
>> *) COALESCE
>> *) casts
>> *) simple tranformational expressions
>> *) predicate string concatenation
>>
>
> *) time/date functions, ie WHERE date_trunc( 'quarter', some_timestamp ) =
> '2014-1-1'
>
> Though, in this case it's probably much better to teach the parser how to
> turn that into a range expression.

Maybe, maybe not.

An index over the truncated time can potentially be much more efficient.