From: | Alban Hertroys <haramrae(at)gmail(dot)com> |
---|---|
To: | Sameer Kumar <sameer(dot)kumar(at)ashnik(dot)com> |
Cc: | Tim Kane <tim(dot)kane(at)gmail(dot)com>, pgsql-general General <pgsql-general(at)postgresql(dot)org> |
Subject: | Re: Possible to improve optimisation / index usage based on domain properties of a function |
Date: | 2014-02-20 07:34:21 |
Message-ID: | FAFD46C8-942F-48BC-895F-90F5D35D0DF2@gmail.com |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-general |
On 20 Feb 2014, at 7:21, Sameer Kumar <sameer(dot)kumar(at)ashnik(dot)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.
From | Date | Subject | |
---|---|---|---|
Next Message | Sameer Kumar | 2014-02-20 07:40:44 | Re: Possible to improve optimisation / index usage based on domain properties of a function |
Previous Message | James Harper | 2014-02-20 07:15:13 | oid of Datum |