Re: 7.4 Wishlist

On Tue, 3 Dec 2002, Bruce Momjian wrote:

> Magnus Naeslund(f) wrote:
> > Good!
> > Now convert this query so that it only evaluates the date_part thing
> > ONCE:
> >
> > select t.id, date_part('days',now()-t.stamp) from table_name t where
> > date_part('days',now()-t.stamp) > 20;
> >
> > I hope you all are kidding me in not seeing the real issue i'm trying to
> > show here.
>
> Does this work?
>
> SELECT t.id, x.date_part
> FROM table_name t, (select date_part('days',now()-t.stamp)) as x
> WHERE x.date_part > 20;

No, because the values in x are correlated to the particular row in
table_name, so I think you have to make it one big subselect in from. In
addition the optimizer is "smart" enough to push the condition down in
most cases which I think will force the function to be called twice unless
you trigger one of its cases that prevent it from doing so. That's an
optimizer hint I'd like (don't push conditions into this subquery,
really...). :)