Re: SELECT LIMIT 1 VIEW Performance Issue - Mailing list pgsql-performance

> 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