> At 02:07 05/09/23, Merlin Moncure wrote: > > > >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. > > esdt=> create or replace function player_max_atdate (varchar(32)) returns > varchar(32) as $$ > esdt$> select atdate from player where playerid = $1 order by playerid > desc, AtDate desc limit 1; > esdt$> $$ language sql immutable;
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? prepare test(character varying) as select atdate from player where playerid = $1 order by playerid desc, AtDate desc limit 1; explain analyze execute test('22220'); > CREATE FUNCTION > esdt=> create or replace view VCurPlayer3 as select * from Player where > AtDate = player_max_atdate(PlayerID); > CREATE VIEW This is wrong, it should have been create or replace view VCurPlayer3 as select *, player_max_atdate(PlayerID) as max_date from Player; I did a test on a table with 124k records and a two part key, ID & date. esp# select count(*) from parts_order_file; count -------- 124158 (1 row) esp=# select count(*) from parts_order_file where pr_dealer_no = '000500'; count ------- 27971 (1 row) created same function, view v, etc. esp=# explain analyze select * from v where pr_dealer_no = '000500' limit 1; QUERY PLAN ------------------------------------------------------------------------ ---------------------------- ---------------------------------------------------------------- Limit (cost=0.00..3.87 rows=1 width=10) (actual time=1.295..1.297 rows=1 loops=1) -> Index Scan using parts_order_file_pr_dealer_no_key on parts_order_file (cost=0.00..109369.15 rows=28226 width=10) (actual time=1.287..1.287 rows=1 loops=1) Index Cond: (pr_dealer_no = '000500'::bpchar) Total runtime: 1.413 ms (4 rows) 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?): create or replace function player_max_atdate (varchar(32)) returns date as $$ DECLARE player_record record; return date date; BEGIN for player_record in execute 'select atdate from player where playerid = \'' || $1 || '\' order by playerid desc, AtDate desc limit 1;' loop return_date = player_record.atdate; end loop; return return_date; END; $ language plpgsql immutable; Merlin ---------------------------(end of broadcast)--------------------------- TIP 6: explain analyze is your friend