Re: Using an ALIAS in WHERE clause

From: Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us>
To: "Magnus Naeslund(f)" <mag(at)fbab(dot)net>
Cc: pgsql-general(at)postgresql(dot)org
Subject: Re: Using an ALIAS in WHERE clause
Date: 2002-11-29 01:58:44
Message-ID: 18709.1038535124@sss.pgh.pa.us
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-general

"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

In response to

Browse pgsql-general by date

  From Date Subject
Next Message Tom Lane 2002-11-29 03:06:20 Re: Server v7.3RC2 Dies
Previous Message CN 2002-11-29 01:54:24 Re: Server v7.3RC2 Dies