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.


---------------------------(end of broadcast)---------------------------
TIP 9: In versions below 8.0, the planner will ignore your desire to
      choose an index scan if your joining column's datatypes do not
      match

Reply via email to