> > > Hi all, > > Take the following scenario > > I have a set of partitions inherited from a parent table, called *streams* > . > One of the properties of these tables is a timestamp field, nothing fancy > about it. > > I also have a qualified index on this field. > > > I've noticed that if I perform the following query, the planner will > correctly use the CHECK constraints to determine the partition, and then > use the indexes available to retrieve the streams between the specified > dates. > > > *select count(*) from streams where stream_date >= '2013-01-08' and > stream_date < '2013-01-09';* > > This is correct way of writing such queries.
> > If however, I was to provide the below query, it uses a sequential scan > based plan. The planner is unable to utilise any indexes because it can't > know what the function is going to return - thus unable to constrain the > range at the time of planning the execution. > > *select count(*) from streams where date(stream_date) = '2013-01-08';* > > > This will not use index on stream_date. Nothing unusual in this. Nothing special with PostgreSQL. None of the RDBMS I have dealt with are smart enough to transform this query. > I'm wondering if we could build into postgres some level of metadata > regarding the properties of a function, such that the optimiser could > filter against the range of values that the function is expected to return. > > In this case, it could deduce that the date function will only ever return > a value for *stream_date* to within a certain maximum and minimum range. > Thus the planner could scan the index for all values of *stream_date*falling > within +/- 24 hours of the right operand, and then check/re-check > the results. > > If you can't go for the smarter query, go for more optimum index by "expression based index" http://www.postgresql.org/docs/9.1/static/indexes-expressional.html > > I suspect this would only be suitable for very basic functions, such as > date(), date_trunc() - I suspect, for any function that reduces cardinality > to any predictable degree. > > Wrong, there are many expressions which won't use indexes but the moment you shift the calculation from LHS to RHS, indexes will appear in plan. This I have seen with at least 3 other RDBMS. > Thoughts? > > I don't think the RDBMS optimizer should be overloaded with smartness which is expected from users writing it. If you do it, then there is no end to it. > Tim > > >
