On 3/17/14, 5:12 PM, Claudio Freire wrote:
On Mon, Mar 17, 2014 at 7:09 PM, Jim Nasby <j...@nasby.net <mailto:j...@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.steh...@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 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 j...@nasby.net 512.569.9461 (cell) http://jim.nasby.net -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
