From: | "Merlin Moncure" <merlin(dot)moncure(at)rcsonline(dot)com> |
---|---|
To: | "K C Lau" <kclau60(at)netvigator(dot)com> |
Cc: | <pgsql-performance(at)postgresql(dot)org> |
Subject: | Re: SELECT LIMIT 1 VIEW Performance Issue |
Date: | 2005-09-22 18:07:38 |
Message-ID: | 6EE64EF3AB31D5448D0007DD34EEB3417DD406@Herge.rcsinc.local |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | 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
From | Date | Subject | |
---|---|---|---|
Next Message | Kevin Grittner | 2005-09-22 18:19:56 | Re: SELECT LIMIT 1 VIEW Performance Issue |
Previous Message | Antoine Bajolet | 2005-09-22 17:52:54 | Re: Nested Loop trouble : Execution time increases more |