Re: SELECT LIMIT 1 VIEW Performance Issue

Поиск
Список
Период
Сортировка
От Merlin Moncure
Тема Re: SELECT LIMIT 1 VIEW Performance Issue
Дата
Msg-id 6EE64EF3AB31D5448D0007DD34EEB3417DD406@Herge.rcsinc.local
обсуждение исходный текст
Ответ на SELECT LIMIT 1 VIEW Performance Issue  (K C Lau <kclau60@netvigator.com>)
Ответы Re: SELECT LIMIT 1 VIEW Performance Issue  (K C Lau <kclau60@netvigator.com>)
Список pgsql-performance
> >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



В списке pgsql-performance по дате отправления:

Предыдущее
От: Antoine Bajolet
Дата:
Сообщение: Re: Nested Loop trouble : Execution time increases more
Следующее
От: "Kevin Grittner"
Дата:
Сообщение: Re: SELECT LIMIT 1 VIEW Performance Issue