Skip site navigation (1) Skip section navigation (2)

Re: SELECT LIMIT 1 VIEW Performance Issue

From: K C Lau <kclau60(at)netvigator(dot)com>
To: "Merlin Moncure" <merlin(dot)moncure(at)rcsonline(dot)com>
Cc: <pgsql-performance(at)postgresql(dot)org>
Subject: Re: SELECT LIMIT 1 VIEW Performance Issue
Date: 2005-09-23 15:08:20
Message-ID: 6.2.1.2.0.20050923224328.05897fa0@localhost (view raw or flat)
Thread:
Lists: 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.


In response to

pgsql-performance by date

Next:From: Michael StoneDate: 2005-09-23 15:31:18
Subject: Re: How to determine cause of performance problem?
Previous:From: Simon RiggsDate: 2005-09-23 15:06:18
Subject: Re: Releasing memory during External sorting?

Privacy Policy | About PostgreSQL
Copyright © 1996-2014 The PostgreSQL Global Development Group