On 11/01/2018 02:40 AM, David Rowley wrote:
On 1 November 2018 at 12:24, Andres Freund <and...@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
