Re: very slow execution of stored procedures

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