Re: SELECT LIMIT 1 VIEW Performance Issue

> >Here is a trick I use sometimes with views, etc.  This may or may not
be
> >effective to solve your problem but it's worth a shot.  Create one
small
> >SQL function taking date, etc. and returning the values and define it
> >immutable.  Now in-query it is treated like a constant.
>
> We don't use functions as a rule, but I would be glad to give it a
try.
> I would most appreciate if you could define a sample function and
rewrite
> the VCurPlayer view above. Both PlayerID and AtDate are varchar
fields.

> esdt=> explain analyze select PlayerID,AtDate from Player a
>   where PlayerID='22220' and AtDate = (select b.AtDate from Player b
>   where b.PlayerID = '22220' order by b.PlayerID desc, b.AtDate desc
LIMIT 1
try:

create function player_max_at_date (varchar) returns date as
$$
    select atdate from player where playerid = $1 order by playerid
desc, AtDate desc limit 1;
$$ language sql immutable;

create view v as select playerid, player_max_at_date(playerid) from
player;
select * from v where playerid = 'x'; --etc

note: this function is not really immutable.  try with both 'immutable'
and 'stable' if performance is same, do stable.

You're welcome in advance, ;)
Merlin