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

Tim Kane <tim.kane@xxxxxxxxx> · Thu, 20 Feb 2014 12:03:27 +0000

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.kumar@xxxxxxxxxx>
Date:  Thursday, 20 February 2014 07:40
To:  Alban Hertroys <haramrae@xxxxxxxxx>
Cc:  Tim Kane <tim.kane@xxxxxxxxx>, pgsql-general General <pgsql-general@xxxxxxxxxxxxxx>
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@xxxxxxxxx> 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

This email may contain confidential, privileged or copyright material and is solely for the use of the intended recipient(s).
-- 
Sent via pgsql-general mailing list (pgsql-general@xxxxxxxxxxxxxx)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general