Skip site navigation (1) Skip section navigation (2)

Re: SELECT LIMIT 1 VIEW Performance Issue

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 (view raw or flat)
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



Responses

pgsql-performance by date

Next:From: Kevin GrittnerDate: 2005-09-22 18:19:56
Subject: Re: SELECT LIMIT 1 VIEW Performance Issue
Previous:From: Antoine BajoletDate: 2005-09-22 17:52:54
Subject: Re: Nested Loop trouble : Execution time increases more

Privacy Policy | About PostgreSQL
Copyright © 1996-2014 The PostgreSQL Global Development Group