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