Re: Using an ALIAS in WHERE clause

"Magnus Naeslund\(f\)" <mag(at)fbab(dot)net> writes:
> select
> ...
> extract('days' from (now() - i.created)) as days_overdue,
> ...
> where
> ...
> extract('days' from (now() - i.created)) >= 20

> Is the days_overdue calculated twice,

Yes.

> Not sure of how big of a performance win it would be, the extract thing
> shouldn't be that slow, right?

In general I think this is useless micro-optimization ;-). There are
few functions in SQL that are expensive enough that it's worth worrying
about calling them twice per row.

If you have a case where it really does matter (super-expensive
user-defined function, perhaps) you could probably do something with
the multi-level-SELECT technique I illustrated.

Years ago, someone at Berkeley did a thesis about planning in the
presence of expensive functions, and the remnants of that thesis are
still in the Postgres sources --- but it's dead code and would not be
easy to resurrect. I personally doubt it could be worth the trouble.

regards, tom lane