Re: Optimisation deficiency: currval('seq')-->seq scan, constant-->index scan

Hi!

On Sun, 20 Aug 2000, Hannu Krosing wrote:

> It seems that optimiser is unaware that currval('seq') can be treated as
> a constant within
> an expression and thus produces suboptimal plans for WHERE clauses that
> use currval
> thus using a seq scan instead of index scan.
>
> Is it possible (planned) to mark functions as returning a constant when
> given a constant
> argument and start using it _as a constant_ (pre-evaluated) in queries

Just one question regrarding this:

Suppose you have
select ... where x in (select currval('seq')) and y in (select
nextval('seq'))....

What's the precise semantics of this? Should there be any precise
semantics? Whats the order of execution? currval before or after
nextval? It seems to me that the declarative nature of SQL makes that no
order whatsoever should be assumed...

In the case of uncorrelated queries, there is the option of
materializing (which I think - after looking at the code - that pg does
not use) the subqueries results as there is no need to recompute them. In
this case materializing vs re-executing seems to cause a semantinc
difference because in mater there is only one execution of nextval and in
reexecution nextval is executed unknown number of times.

If all this as pre-evaluated this last problem would disapear.

Side-effects, side-effects, ...

Best regards,
Tiago
PS - I'm starting the thesis part of a MSc which will be about query
optimization in pg. Here the thesis part of the MSc takes arround one
year, so at least for the next year I'll try to work hard on pg.