From: | Jim Nasby <jim(at)nasby(dot)net> |
---|---|
To: | Claudio Freire <klaussfreire(at)gmail(dot)com> |
Cc: | Merlin Moncure <mmoncure(at)gmail(dot)com>, Pavel Stehule <pavel(dot)stehule(at)gmail(dot)com>, Atri Sharma <atri(dot)jiit(at)gmail(dot)com>, Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us>, David Johnston <polobo(at)yahoo(dot)com>, PostgreSQL-development <pgsql-hackers(at)postgresql(dot)org> |
Subject: | Re: Planner hints in Postgresql |
Date: | 2014-03-17 22:14:44 |
Message-ID: | 532773D4.6080504@nasby.net |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-hackers |
On 3/17/14, 5:12 PM, Claudio Freire wrote:
>
> On Mon, Mar 17, 2014 at 7:09 PM, Jim Nasby <jim(at)nasby(dot)net <mailto: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 <mailto: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.
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 limit by 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 could do something better.)
--
Jim C. Nasby, Data Architect jim(at)nasby(dot)net
512.569.9461 (cell) http://jim.nasby.net
From | Date | Subject | |
---|---|---|---|
Next Message | Josh Berkus | 2014-03-17 22:15:06 | Re: Planner hints in Postgresql |
Previous Message | Jim Nasby | 2014-03-17 22:12:57 | Re: Planner hints in Postgresql |