Mike Sofen schrieb am 14.04.2016 um 14:29:
> The general rule in the SQL Server world is that using a function in
> a Where clause or join will eliminate usage of an index that would
> have been leveraged if the function didn't exist. The reason is that
> functions are non-deterministic, so the optimizer can't possibly tell
> in advance what the outcome will be and thus takes the safest route
> to completion.
> I'm betting that the same logic holds in PG (I just
> haven't tested it enough to be absolutely sure).
Well, this is only true if the function "hides" the value of a column, or
if the function is not marked stable. A condition like:
where x = some_function(42)
can absolutely use an index on the column x (and I'm pretty sure this
is true for SQL Server as well).
You can even create an index on a function expression, so that something
like
where some_function(x) = 42
can make use of an index if that is defined as: on table_name((some_function(x)))
(Something SQL Server can't do)
You can only create such an index if the function is marked as "immutable"
which basically says that when calling the same function twice with the
same value it will return the exact same value:
http://www.postgresql.org/docs/current/static/xfunc-volatility.html
But in general I do agree that one should be very careful with
conditions where the types don't match or where expressions are
used that can't make use of an index.
> In the case of now() in the Where clause, to avoid the
> conversion/loss of index usage, I always place (what should be a
> static value anyway) the output of now() into a local variable and
> then use that in the Where clause...and get my index back.
now() (and current_timestamp as well) are defined to return the
same value throughout the entire transaction.
So the optimizer _should_ be smart enough to do the conversion only
once at the beginning of the statement and then use that converted
value during the execution of the statement without the need
to re-evaluate it for each row.
But my question wasn't about whether it's a good idea to
use a function in the where clause, but why there is such a huge(!)
difference in performance between now() and current_date especially
given the fact that both are only evaluated once.
Thomas
