Re: Using an ALIAS in WHERE clause

"Magnus Naeslund\(f\)" <mag@fbab.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