From: K C Lau <kclau60@netvigator.com>
To: Merlin Moncure <merlin.moncure@rcsonline.com>
To: pgsql-performance@postgresql.org
Subject: Re: SELECT LIMIT 1 VIEW Performance Issue
Date: Wed, 12 Oct 2005 21:00:15 +0800
Message-ID: <6.2.1.2.0.20051012203443.0512a588@localhost> (raw)
In-Reply-To: <6EE64EF3AB31D5448D0007DD34EEB3417DD58A@Herge.rcsinc.local>
References: <6EE64EF3AB31D5448D0007DD34EEB3417DD58A@Herge.rcsinc.local>
Dear Merlin and all,
That direct SQL returns in 0 ms. The problem only appears when a view is used.
What we've done to work around this problem is to modify the table to add a
field DataStatus which is set to 1 for the latest record for each player,
and reset to 0 when it is superceded.
A partial index is then created as:
CREATE INDEX IDX_CurPlayer on Player (PlayerID) where DataStatus = 1;
The VCurPlayer view is changed to:
CREATE or REPLACE VIEW VCurPlayer as select * from Player where DataStatus = 1;
and it now returns in 0 ms.
This is not the best solution, but until (if ever) the original problem is
fixed, we have not found an alternative work around.
The good news is that even with the additional overhead of maintaining an
extra index and the problem of vacuuming, pg 8.0.3 still performs
significantly faster on Windows than MS Sql 2000 in our OLTP application
testing so far.
Thanks to all for your help.
Best regards,
KC.
At 20:14 05/10/12, you wrote:
>KC wrote:
> >
> > So I guess it all comes back to the basic question:
> >
> > For the query select distinct on (PlayerID) * from Player a where
> > PlayerID='22220' order by PlayerId Desc, AtDate Desc;
> > can the optimizer recognise the fact the query is selecting by the
>primary
> > key (PlayerID,AtDate), so it can skip the remaining rows for that
> > PlayerID,
> > as if LIMIT 1 is implied?
> >
> > Best regards, KC.
>
>Hi KC, have you tried:
>select * from player where playerid = '22220' and atdate < 9999999999
>order by platerid desc, atdate desc limit 1;
>
>??
>Merlin
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, merlin.moncure@rcsonline.com
Subject: Re: SELECT LIMIT 1 VIEW Performance Issue
In-Reply-To: <6.2.1.2.0.20051012203443.0512a588@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