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 pa
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='0' order by PlayerId Desc, AtDate Desc;
> can the optimizer recognise the fact the query is selecting by the
primary
> key (PlayerID,AtDate), so
[to K C:] sorry, was out on vactation all last week. I was visualizing
the problem incorrectly anyways...
Jim wrote:
> That function is not immutable, it should be defined as stable.
That is 100% correct: however now and then I declare stable functions as
immutable in some cases because the plan
On Tue, Oct 04, 2005 at 04:15:41PM -0500, Jim C. Nasby wrote:
> >Index Cond: ((playerid)::text = '0'::text)
Also, why is playerid a text field? Comparing ints will certainly be
faster...
--
Jim C. Nasby, Sr. Engineering Consultant [EMAIL PROTECTED]
Pervasive Software http://perv
On Fri, Sep 23, 2005 at 04:53:55PM +0800, K C Lau wrote:
> Thank you all for your suggestions. I' tried, with some variations too, but
> still no success. The times given are the best of a few repeated tries on
> an 8.1 beta 2 db freshly migrated from 8.0.3 on Windows.
>
> For reference, only th
On Fri, Sep 23, 2005 at 08:17:03PM +0800, K C Lau wrote:
> esdt=> create or replace function player_max_atdate (varchar(32)) returns
> varchar(32) as $$
> esdt$> select distinct on (PlayerID) AtDate from player where PlayerID= $1
> order by PlayerID desc, AtDate desc limit 1;
> esdt$> $$ languag
At 20:17 05/09/23, K C Lau wrote:
At 19:15 05/09/23, Simon Riggs wrote:
select distinct on (PlayerID) PlayerID,AtDate from Player a
where PlayerID='0' order by PlayerId, AtDate Desc;
Does that work for you?
Best Regards, Simon Riggs
esdt=> explain analyze select distinct on (PlayerID) Pl
K C Lau wrote:
I'm wondering if this performance issue is common enough for other users
to merit a fix in pg, especially as it seems that with MVCC, each of the
data records need to be accessed in addition to scanning the index.
Yes - there are certainly cases where index only access (or so
Dear Mark,
Thank you. That seems like a more manageable alternative if nothing else
works out. It should cover many of the OLTP update transactions. But it
does mean quite a bit of programming changes and adding another index on
all such tables, and it would not cover those cases when we need
K C Lau wrote:
Thank you all for your suggestions. I' tried, with some variations too,
but still no success. The times given are the best of a few repeated
tries on an 8.1 beta 2 db freshly migrated from 8.0.3 on Windows.
A small denormalization, where you mark the row with the latest atdate
Dear Merlin,
At 20:34 05/09/23, Merlin Moncure wrote:
Can you time just the execution of this function and compare vs. pure
SQL version? If the times are different, can you do a exaplain analyze
of a prepared version of above?
esdt=> prepare test(character varying) as select atdate from playe
> At 02:07 05/09/23, Merlin Moncure wrote:
> > > >Here is a trick I use sometimes with views, etc. This may or may
not
> be
> > > >effective to solve your problem but it's worth a shot. Create
one
> small
> > > >SQL function taking date, etc. and returning the values and
define it
> > > >immutabl
At 19:15 05/09/23, Simon Riggs wrote:
select distinct on (PlayerID) PlayerID,AtDate from Player a
where PlayerID='0' order by PlayerId, AtDate Desc;
Does that work for you?
Best Regards, Simon Riggs
esdt=> explain analyze select distinct on (PlayerID) PlayerID,AtDate from
Player a where
On Thu, 2005-09-22 at 18:40 +0800, K C Lau wrote:
> > > esdt=> explain analyze select PlayerID,AtDate from Player a
> > > where PlayerID='0' and AtDate = (select b.AtDate from Player b
> > > where b.PlayerID = a.PlayerID order by b.PlayerID desc, b.AtDate desc
> > > LIMIT 1);
I think you
Thank you all for your suggestions. I' tried, with some variations too, but
still no success. The times given are the best of a few repeated tries on
an 8.1 beta 2 db freshly migrated from 8.0.3 on Windows.
For reference, only the following gets the record quickly:
esdt=> explain analyze selec
Have you tried the "best choice" pattern -- where you select the set of
candidate rows and then exclude those for which a better choice
exists within the set? I often get better results with this pattern than
with the alternatives. Transmuting your query to use this patter gives:
select PlayerI
> >Here is a trick I use sometimes with views, etc. This may or may not
be
> >effective to solve your problem but it's worth a shot. Create one
small
> >SQL function taking date, etc. and returning the values and define it
> >immutable. Now in-query it is treated like a constant.
>
> We don't u
On Thu, 2005-09-22 at 22:39 +0800, K C Lau wrote:
> >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 immed
K C Lau <[EMAIL PROTECTED]> writes:
> At 20:48 05/09/22, Simon Riggs wrote:
>> 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
At 22:37 05/09/22, Merlin Moncure wrote:
> >create or replace view VCurPlayer as select * from Player a
> >where a.AtDate = (select Max(b.AtDate) from Player b where a.PlayerID=
> >b.PlayerID);
Here is a trick I use sometimes with views, etc. This may or may not be
effective to solve your prob
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
> >I previously posted the following as a sequel to my SELECT DISTINCT
> >Performance Issue question. We would most appreciate any clue or
> >suggestions on how to overcome this show-stopping issue. We are using
> >8.0.3 on Windows.
> >
> >Is it a known limitation when using a view with SELECT ...
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
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
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='0' and
Hi All,
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='0' and AtDate = (select b.AtDate from Player b
where b.Pl
26 matches
Mail list logo