From: K C Lau <kclau60@netvigator.com>
To: Merlin Moncure <merlin.moncure@rcsonline.com>
Cc: pgsql-performance@postgresql.org
Subject: Re: SELECT LIMIT 1 VIEW Performance Issue
Date: Fri, 23 Sep 2005 23:08:20 +0800
Message-ID: <6.2.1.2.0.20050923224328.05897fa0@localhost> (raw)
In-Reply-To: <6EE64EF3AB31D5448D0007DD34EEB3417DD41B@Herge.rcsinc.local>
References: <6EE64EF3AB31D5448D0007DD34EEB3417DD41B@Herge.rcsinc.local>
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.
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: pgsql-performance@postgresql.org
Cc: kclau60@netvigator.com, merlin.moncure@rcsonline.com
Subject: Re: SELECT LIMIT 1 VIEW Performance Issue
In-Reply-To: <6.2.1.2.0.20050923224328.05897fa0@localhost>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox