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: Thu, 22 Sep 2005 13:48:04 +0100
Message-Id: <1127393284.4145.109.camel@localhost.localdomain>
Mime-Version: 1.0
Content-Transfer-Encoding: 7bit

On Thu, 2005-09-22 at 18:40 +0800, K C Lau wrote:
> We use similar views as base views throughout our OLTP system to get the 
> latest time-based record(s). So it is quite impossible to use summary 
> tables etc. Are there other ways to do it?
> 
> The subquery would pinpoint the record(s) with the composite primary key. 
> Both MS Sql and Oracle do not have such performance problem. So this 
> problem is effectively stopping us from migrating to PostgreSQL.
> 
> Any suggestions would be most appreciated.

Even if this were fixed for 8.1, which seems unlikely, would you be able
to move to that release immediately?

ISTM you have two choices, in priority, complexity and time/cost order
1) custom mods to your app
2) custom mods to PostgreSQL

Maybe its possible to reconstruct your query with sub-sub-selects so
that you have a correlated query with manually pushed down clauses,
which also references a more constant base view?

Is a 51ms query really such a problem for you?

Best Regards, Simon Riggs