Re: SELECT LIMIT 1 VIEW Performance Issue
От | K C Lau |
---|---|
Тема | Re: SELECT LIMIT 1 VIEW Performance Issue |
Дата | |
Msg-id | 6.2.1.2.0.20050923224328.05897fa0@localhost обсуждение исходный текст |
Ответ на | Re: SELECT LIMIT 1 VIEW Performance Issue ("Merlin Moncure" <merlin.moncure@rcsonline.com>) |
Список | pgsql-performance |
Dear Merlin, At 20:34 05/09/23, Merlin Moncure wrote: >Can you time just the execution of this function and compare vs. pure >SQL version? If the times are different, can you do a exaplain analyze >of a prepared version of above? esdt=> prepare test(character varying) as select atdate from player where esdt-> playerid = $1 order by playerid desc, AtDate desc limit 1; PREPARE esdt=> explain analyze execute test('22220'); Limit (cost=0.00..0.83 rows=1 width=23) (actual time=0.032..0.033 rows=1 loops=1) -> Index Scan Backward using pk_player on player (cost=0.00..970.53 rows=1166 width=23) (actual time=0.027..0.027 rows=1 loops=1) Index Cond: ((playerid)::text = ($1)::text) Total runtime: 0.088 ms The prepared SQL timing is similar to that of a direct SQL. > > esdt=> create or replace view VCurPlayer3 as select * from Player where > > AtDate = player_max_atdate(PlayerID); > >This is wrong, it should have been >create or replace view VCurPlayer3 as select *, >player_max_atdate(PlayerID) as max_date from Player; Your suggestion returns all the records plus a max AtDate column for each PlayerID. What I want to get with the view is the record that has the max value of AtDate for each PlayerID. The AtDate is a varchar(23) field containing a string date of format 'yyyymmddhh', not the SQL Date field. Sorry if that confused you. >Something is not jiving here. However, if the server plan still does >not come out correct, try the following (p.s. why is function returning >varchar(32) and not date?): esdt=> create or replace function player_max_atdate (varchar(32)) returns varchar(32) as $$ esdt$> DECLARE esdt$> player_record record; esdt$> return_date varchar(32); esdt$> BEGIN esdt$> for player_record in execute esdt$> 'select atdate from player where playerid = \'' || $1 || '\' order by playerid desc, AtDate desc limit 1;' loop esdt$> return_date = player_record.atdate; esdt$> end loop; esdt$> return return_date; esdt$> END; esdt$> $$ language plpgsql immutable; CREATE FUNCTION esdt=> create or replace view VCurPlayer3 as select * from Player where AtDate = player_max_atdate(PlayerID); CREATE VIEW esdt=> explain analyze select PlayerID,AtDate from VCurPlayer3 where PlayerID='22220'; Index Scan using pk_player on player (cost=0.00..1331.83 rows=9 width=23) (actual time=849.021..849.025 rows=1 loops=1) Index Cond: ((playerid)::text = '22220'::text) Filter: ((atdate)::text = (player_max_atdate(playerid))::text) Total runtime: 849.078 ms Your suggested plpgsql function seems to be even slower, with a best time of 849 ms after several tries. Is that expected? Thanks again and best regards, KC.
В списке pgsql-performance по дате отправления: