Re: Possible to improve optimisation / index usage based on domain properties of a function - Mailing list pgsql-general
On Thu, Feb 20, 2014 at 3:34 PM, Alban Hertroys <haramrae@gmail.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.The inverse of that expression, if it’s possible to formulate one, would most likely use the index though:
>
> select count(*) from streams where date(stream_date) = ‘2013-01-08’;
select count(*) from streams where stream_date = inv_date(‘2013-01-08’);
He has already posted that:
select count(*) from streams where stream_date >= ‘2013-01-08’ and stream_date < ‘2013-01-09’;
This would use index
>AFAIK, you can’t use expression based indexes to partition a table, so that won’t help the OP much.
>> 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
1. I think Tim is talking about index usage [an index which he has on stream_date] and not partition
2. Tim, the index usage or non-usage in your two queries would remain same even if you have a single huge table
3. I believe the partitioning tirgger/rule could re-direct records based on an expression too [e.g. when date(stream_date)=24-Jan-2014, send it to Partition_24Jan14]. I am not sure if query planner would go to a particular partition when we query based on date(stream_date). I need to test this.
Best Regards,
Sameer Kumar | Database Consultant
ASHNIK PTE. LTD.
101 Cecil Street, #11-11 Tong Eng Building, Singapore 069533
M: +65 8110 0350 T: +65 6438 3504 | www.ashnik.com
This email may contain confidential, privileged or copyright material and is solely for the use of the intended recipient(s).
Attachment
pgsql-general by date: