On 11/01/2018 02:40 AM, David Rowley wrote:
> On 1 November 2018 at 12:24, Andres Freund <andres@anarazel.de> wrote:
>> FWIW, I kind of wonder if we built proper infrastructure to allow to
>> make such inferrences from function calls, whether it could also be made
>> to support the transformation of LIKEs into indexable <= >= clauses.
>
> Perhaps, but I doubt it would be the same function to do both. Surely
> I need something that accepts details about the function call as
> arguments and returns an Expr * of the argument that we can derive the
> order of the return value from, or NULL. I think the transformation
> you need might be more along the lines of returning a List * of quals
> that can substitute an OpExpr containing a function call. I'm not that
> clear on how we'd know the new quals were better than the existing
> ones. For example extract('year', dt) = 2018 could be transformed to
> dt >= '2018-01-01' AND dt < '2019-01-01', but how would we know that
> was better. There might be an index on extract('year', dt).
>
IMHO there is only a handful of "useful" transformations of this kind,
depending on the operator class of an index. So when building index
paths and checking which quals may be used as index conditions, we'd do
try transforming incompatible quals and leave the rest up to the
existing create_index_path machinery (which already makes the decision
about which quals to use for index search etc.)
regards
--
Tomas Vondra http://www.2ndQuadrant.com
PostgreSQL Development, 24x7 Support, Remote DBA, Training & Services