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.20050922102035.07bcf2e0@localhost>
References: <6.2.1.2.0.20050615100524.04f9d6a8@localhost>
	<6.2.1.2.0.20050922102035.07bcf2e0@localhost>
Content-Type: text/plain
Organization: 2nd Quadrant
Date: Thu, 22 Sep 2005 09:40:56 +0100
Message-Id: <1127378456.4145.76.camel@localhost.localdomain>
Mime-Version: 1.0
Content-Transfer-Encoding: 7bit

On Thu, 2005-09-22 at 12:21 +0800, K C Lau wrote:

> Investigating further on this problem I brought up in June, the following 
> query with pg 8.0.3 on Windows scans all 1743 data records for a player:
> 
> 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);
> 

>   Total runtime: 51.133 ms
> 
> Using a static value in the subquery produces the desired result below, but 
> since we use views for our queries (see last part of this email), we cannot 
> push the static value into the subquery:
> 
> esdt=> explain analyze select PlayerID,AtDate from Player a
>   where PlayerID='22220' and AtDate = (select b.AtDate from Player b
>   where b.PlayerID = '22220' order by b.PlayerID desc, b.AtDate desc LIMIT 1);

>   Total runtime: 0.149 ms
> 
> The Player table has a primary key on PlayerID, AtDate. Is there a way to 
> stop the inner-most index scan looping all 1743 data records for that 
> player?  Is that a bug or known issue?

Currently the planner can't tell whether a subquery is correlated or not
until it has planned the query. So it is unable to push down the
qualification automatically in the way you have achieved manually. The
new min() optimisation doesn't yet work with GROUP BY which is what you
would use to reformulate the query that way, so no luck that way either.

If you don't want to do this in a view, calculate the values for all
players at once and store the values in a summary table for when you
need them.

Best Regards, Simon Riggs