Re: Planner hints in Postgresql

On 3/17/14, 5:12 PM, Claudio Freire wrote:
>
> On Mon, Mar 17, 2014 at 7:09 PM, Jim Nasby <jim@nasby.net <mailto:jim@nasby.net>> wrote:
>
>     On 3/17/14, 2:16 PM, Merlin Moncure wrote:
>
>         On Mon, Mar 17, 2014 at 1:45 PM, Pavel Stehule<pavel.stehule@gmail.__com <mailto:pavel.stehule@gmail.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.

More efficient than a range index? Maybe, but I'm doubtful. Even if that's true, in a warehouse you're going to want to
limitby weeks, months, quarters, years, etc. So now you're stuck building tons of special indexes.
 

(Granted, most warehouses build a separate date dimension because of these kinds of problems... I'm hoping that we
coulddo something better.)
 
-- 
Jim C. Nasby, Data Architect                       jim@nasby.net
512.569.9461 (cell)                         http://jim.nasby.net