From: K C Lau <kclau60@netvigator.com>
To: Simon Riggs <simon@2ndquadrant.com>
Cc: pgsql-performance@postgresql.org
Subject: Re: SELECT LIMIT 1 VIEW Performance Issue
Date: Thu, 22 Sep 2005 18:40:46 +0800
Message-ID: <6.2.1.2.0.20050922181009.02c36ca8@localhost> (raw)
In-Reply-To: <1127378456.4145.76.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>
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.
Best regards,
KC.
At 16:40 05/09/22, Simon Riggs wrote:
>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
>
>
>
>---------------------------(end of broadcast)---------------------------
>TIP 6: explain analyze is your friend
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: pgsql-performance@postgresql.org
Cc: kclau60@netvigator.com, simon@2ndquadrant.com
Subject: Re: SELECT LIMIT 1 VIEW Performance Issue
In-Reply-To: <6.2.1.2.0.20050922181009.02c36ca8@localhost>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox