Message-Id: <6.2.1.2.0.20050923195457.02c4fd30@localhost>
Date: Fri, 23 Sep 2005 20:17:03 +0800
To: Simon Riggs <simon@2ndquadrant.com>
From: K C Lau <kclau60@netvigator.com>
Subject: Re: SELECT LIMIT 1 VIEW Performance Issue
Cc: pgsql-performance@postgresql.org
In-Reply-To: <1127474141.19345.10.camel@localhost.localdomain>
References: <6.2.1.2.0.20050615100524.04f9d6a8@localhost>
	<6.2.1.2.0.20050922102035.07bcf2e0@localhost>
	<1127378456.4145.76.camel@localhost.localdomain>
	<6.2.1.2.0.20050922181009.02c36ca8@localhost>
	<1127474141.19345.10.camel@localhost.localdomain>
Mime-Version: 1.0
Content-Type: text/plain; charset="us-ascii"; format=flowed

At 19:15 05/09/23, Simon Riggs wrote:
>select distinct on (PlayerID) PlayerID,AtDate from Player a
>where PlayerID='22220' order by PlayerId, AtDate Desc;
>
>Does that work for you?
>
>Best Regards, Simon Riggs

esdt=> explain analyze select distinct on (PlayerID) PlayerID,AtDate from 
Player a where PlayerID='22220' order by PlayerId, AtDate Desc;
  Unique  (cost=1417.69..1426.47 rows=2 width=23) (actual 
time=31.231..36.609 rows=1 loops=1)
    ->  Sort  (cost=1417.69..1422.08 rows=1756 width=23) (actual 
time=31.129..32.473 rows=1743 loops=1)
          Sort Key: playerid, atdate
          ->  Index Scan using pk_player on player a  (cost=0.00..1323.05 
rows=1756 width=23) (actual time=0.035..6.575 rows=1743 loops=1)
                Index Cond: ((playerid)::text = '22220'::text)
  Total runtime: 36.943 ms

The sort was eliminated with: order by PlayerId Desc, AtDate Desc:

esdt=> explain analyze select distinct on (PlayerID) PlayerID,AtDate from 
Player a where PlayerID='22220' order by PlayerId Desc, AtDate Desc;
  Unique  (cost=0.00..1327.44 rows=2 width=23) (actual time=0.027..8.438 
rows=1 loops=1)
    ->  Index Scan Backward using pk_player on player 
a  (cost=0.00..1323.05 rows=1756 width=23) (actual time=0.022..4.950 
rows=1743 loops=1)
          Index Cond: ((playerid)::text = '22220'::text)
  Total runtime: 8.499 ms

That is the fastest of all queries looping the 1743 rows.
I do get the desired result by adding LIMIT 1:

esdt=> explain analyze select distinct on (PlayerID) PlayerID,AtDate from 
Player a where PlayerID='22220' order by PlayerId Desc, AtDate Desc LIMIT 1;

  Limit  (cost=0.00..663.72 rows=1 width=23) (actual time=0.032..0.033 
rows=1 loops=1)
    ->  Unique  (cost=0.00..1327.44 rows=2 width=23) (actual 
time=0.028..0.028 rows=1 loops=1)
          ->  Index Scan Backward using pk_player on player 
a  (cost=0.00..1323.05 rows=1756 width=23) (actual time=0.022..0.022 rows=1 
loops=1)
                Index Cond: ((playerid)::text = '22220'::text)
  Total runtime: 0.094 ms

However, when I use that within a function in a view, it is slow again:

esdt=> create or replace function player_max_atdate (varchar(32)) returns 
varchar(32) as $$
esdt$>  select distinct on (PlayerID) AtDate from player where PlayerID= $1 
order by PlayerID desc, AtDate desc limit 1;
esdt$> $$ language sql 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=76.660..76.664 rows=1 loops=1)
    Index Cond: ((playerid)::text = '22220'::text)
    Filter: ((atdate)::text = (player_max_atdate(playerid))::text)
  Total runtime: 76.716 ms

Why wouldn't the function get the row as quickly as the direct sql does?

Best regards, KC.