Re: Possible to improve optimisation / index usage based on domain properties of a function

Thanks Alban, Sameer.

My use of partitions should have been more of a side note really.
I was particularly interested in wether the query planner could optimise a
date_folded equality expression into a range query - for the case where it
could benefit from an existing index on the non-folded values.

Granted, an expression based index would solve this.
It just seemed an opportunity to open up opportunities for the QEP – at
least for the simple case.

Cheers,

TIm

From: Sameer Kumar <sameer(dot)kumar(at)ashnik(dot)com>
Date: Thursday, 20 February 2014 07:40
To: Alban Hertroys <haramrae(at)gmail(dot)com>
Cc: Tim Kane <tim(dot)kane(at)gmail(dot)com>, pgsql-general General
<pgsql-general(at)postgresql(dot)org>
Subject: Re: [GENERAL] Possible to improve optimisation / index usage based
on domain properties of a function

On Thu, Feb 20, 2014 at 3:34 PM, Alban Hertroys <haramrae(at)gmail(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’);
>

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

>> >
>>> >> 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.

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 <http://www.ashnik.com/>

<http://www.ashnik.com/>

This email may contain confidential, privileged or copyright material and is
solely for the use of the intended recipient(s).