Message-Id: <6.2.1.2.0.20050922221846.02c45fb8@localhost>
Date: Thu, 22 Sep 2005 22:39:29 +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: <1127393284.4145.109.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>
	<1127393284.4145.109.camel@localhost.localdomain>
Mime-Version: 1.0
Content-Type: text/plain; charset="us-ascii"; format=flowed

At 20:48 05/09/22, Simon Riggs wrote:
>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?

Yes. In fact when we first developed our system a few years ago, we tested 
on MS7.0, Oracle 8 and PG 7.1.1 and we did not hit that problem. When we 
try again with PG 8.0, the performance becomes unbearable, but other areas 
appear ok and other queries are often faster than MS Sql2k.

>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?

We would be most happy to try them if we have some example views or pointers.

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

Unfortunately yes, as our target performance is in the high hundreds of 
transactions per sec. And 51 ms is already the best case for a single 
select, with everything cached in memory immediately after the same select 
which took 390 ms on a quiet system.

>Best Regards, Simon Riggs

Best regards,
KC.