On 20 Feb 2014, at 7:21, Sameer Kumar <sameer.ku...@ashnik.com> wrote:

> 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’;

The inverse of that expression, if it’s possible to formulate one, would most 
likely use the index though:

select count(*) from streams where stream_date = inv_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.
>> 
> 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

AFAIK, you can’t use expression based indexes to partition a table, so that 
won’t help the OP much.

Alban Hertroys
--
If you can't see the forest for the trees,
cut the trees and you'll find there is no forest.



-- 
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

Reply via email to