X-Original-To: pgsql-performance-postgresql.org@localhost.postgresql.org
Received: from localhost (av.hub.org [200.46.204.144])
	by svr1.postgresql.org (Postfix) with ESMTP id 66DB1D8E94
	for <pgsql-performance-postgresql.org@localhost.postgresql.org>;
	Fri, 23 Sep 2005 08:15:39 -0300 (ADT)
Received: from svr1.postgresql.org ([200.46.204.71])
	by localhost (av.hub.org [200.46.204.144]) (amavisd-new, port 10024)
	with ESMTP id 31675-07
	for <pgsql-performance-postgresql.org@localhost.postgresql.org>;
	Fri, 23 Sep 2005 11:15:35 +0000 (GMT)
Received: from cmailm4.svr.pol.co.uk (cmailm4.svr.pol.co.uk [195.92.193.211])
	by svr1.postgresql.org (Postfix) with ESMTP id 0B2A5D8EA1
	for <pgsql-performance@postgresql.org>;
	Fri, 23 Sep 2005 08:15:36 -0300 (ADT)
Received: from modem-181.lion.dialup.pol.co.uk ([217.135.160.181]
	helo=192.168.0.102) by cmailm4.svr.pol.co.uk with esmtp (Exim 4.41)
	id 1EIlWk-00042I-B3; Fri, 23 Sep 2005 12:15:38 +0100
Subject: Re: SELECT LIMIT 1 VIEW Performance Issue
From: Simon Riggs <simon@2ndquadrant.com>
To: K C Lau <kclau60@netvigator.com>
Cc: pgsql-performance@postgresql.org
In-Reply-To: <6.2.1.2.0.20050922181009.02c36ca8@localhost>
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>
Content-Type: text/plain
Organization: 2nd Quadrant
Date: Fri, 23 Sep 2005 12:15:41 +0100
Message-Id: <1127474141.19345.10.camel@localhost.localdomain>
Mime-Version: 1.0
X-Mailer: Evolution 2.0.2 (2.0.2-3) 
Content-Transfer-Encoding: 7bit
X-Virus-Scanned: by amavisd-new at hub.org
X-Spam-Status: No, hits=0.052 required=5 tests=[AWL=0.002,
	FORGED_RCVD_HELO=0.05]
X-Spam-Level: 
X-Archive-Number: 200509/283
X-Sequence-Number: 14591

On Thu, 2005-09-22 at 18:40 +0800, K C Lau wrote: 
> > > esdt=> explain analyze select PlayerID,AtDate from Player a
> > >   where PlayerID='22220' and AtDate = (select b.AtDate from Player b
> > >   where b.PlayerID = a.PlayerID order by b.PlayerID desc, b.AtDate desc
> > > LIMIT 1);

I think you should try:

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