Date: Sat, 24 Sep 2005 13:40:25 +1200
From: Mark Kirkwood <markir@paradise.net.nz>
Subject: Re: SELECT LIMIT 1 VIEW Performance Issue
In-reply-to: <6.2.1.2.0.20050923155952.05889a50@localhost>
To: K C Lau <kclau60@netvigator.com>
Cc: pgsql-performance@postgresql.org
Message-id: <4334AE89.8060806@paradise.net.nz>
MIME-version: 1.0
Content-type: text/plain; format=flowed; charset=ISO-8859-1
Content-transfer-encoding: 7bit
User-Agent: Mozilla Thunderbird 1.0.6 (X11/20050726)
References: <6EE64EF3AB31D5448D0007DD34EEB3417DD406@Herge.rcsinc.local>
	<6.2.1.2.0.20050923155952.05889a50@localhost>

K C Lau wrote:
> Thank you all for your suggestions. I' tried, with some variations too, 
> but still no success. The times given are the best of a few repeated 
> tries on an 8.1 beta 2 db freshly migrated from 8.0.3 on Windows.
> 

A small denormalization, where you mark the row with the latest atdate 
for each playerid may get you the performance you want.

e.g: (8.1beta1)

ALTER TABLE player ADD islastatdate boolean;

UPDATE player SET islastatdate = true where (playerid,atdate) IN
(SELECT playerid, atdate FROM vcurplayer);

CREATE OR REPLACE VIEW vcurplayer AS
SELECT * FROM player a
WHERE islastatdate;

CREATE INDEX player_id_lastatdate ON player(playerid, islastatdate)
WHERE islastatdate;

ANALYZE player;

Generating some test data produced:

EXPLAIN ANALYZE
SELECT playerid,atdate
FROM vcurplayer
WHERE playerid='22220';

                                                            QUERY PLAN 

--------------------------------------------------------------------------------------------------------------------------------
  Index Scan using player_id_lastatdate on player a  (cost=0.00..4.33 
rows=1 width=13) (actual time=0.142..0.149 rows=1 loops=1)
    Index Cond: ((playerid = '22220'::text) AND (lastatdate = true))
    Filter: lastatdate
  Total runtime: 0.272 ms
(4 rows)

Whereas with the original view definition:

CREATE OR REPLACE VIEW vcurplayer AS
SELECT * FROM player a
WHERE a.atdate =
( SELECT max(b.atdate) FROM player b
   WHERE a.playerid = b.playerid);

EXPLAIN ANALYZE
SELECT playerid,atdate
FROM vcurplayer
WHERE playerid='22220';
 
     QUERY PLAN 

-----------------------------------------------------------------------------------------------------------------------------------------------------------------
  Index Scan using player_id_date on player a  (cost=0.00..7399.23 
rows=11 width=13) (actual time=121.738..121.745 rows=1 loops=1)
    Index Cond: (playerid = '22220'::text)
    Filter: (atdate = (subplan))
    SubPlan
      ->  Result  (cost=1.72..1.73 rows=1 width=0) (actual 
time=0.044..0.047 rows=1 loops=2000)
            InitPlan
              ->  Limit  (cost=0.00..1.72 rows=1 width=4) (actual 
time=0.028..0.031 rows=1 loops=2000)
                    ->  Index Scan Backward using player_id_date on 
player b  (cost=0.00..3787.94 rows=2198 width=4) (actual 
time=0.019..0.019 rows=1 loops=2000)
                          Index Cond: ($0 = playerid)
                          Filter: (atdate IS NOT NULL)
  Total runtime: 121.916 ms
(11 rows)

Note that my generated data has too many rows for each playerid, but the 
  difference in performance should illustrate the idea.

Cheers

Mark