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’; 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’; 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. 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. Thoughts? Tim |
