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.ku...@ashnik.com> Date: Thursday, 20 February 2014 07:40 To: Alban Hertroys <haram...@gmail.com> Cc: Tim Kane <tim.k...@gmail.com>, pgsql-general General <pgsql-general@postgresql.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 <haram...@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. >> > >> > 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).
<<inline: image005.jpg>>
<<inline: image006.jpg>>
-- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
