dev@archonet.com writes:
> Looks like the parser is getting confused by the timestamp(x)
> conversions.
I did some experimentation and found that if you write
WHERE timestamp_var = char_var
what you actually end up with is
WHERE text(timestamp_var) = text(char_var)
which of course is going to be horrendously slow: not only is it not
using the index, but it's doing a timestamp-to-text conversion for
every row.
It's not real clear to me why you get this rather than a complaint that
the '=' operator is ambiguous, but that's what you get.
> Because timestamp() is marked non-cachable, Postgres doesn't know it is
> constant over the duration of the query and so scans every row in the
> table re-calculating the timestamp each time.
Right. If there's an invocation (whether implicit or explicit) of
text-to-timestamp conversion in the WHERE clause, Postgres won't
consider it indexable. The best way around this is to force the value
being compared to to be timestamp *before* you get to the query.
> There are two ways you could help Postgres to do the right thing:
> 1. Use another variable for the conversions:
Instead of bothering with another variable, I'd suggest changing the
declared type of the function's parameter to be timestamp in the first
place.
> 2. Mark the timestamp() conversions as cachable
This would be a BAD idea. Likely consequences include timestamp('now')
being evaluated at first use of a function, and not changing thereafter.
Probably not what you wanted...
regards, tom lane