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 it can
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
[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
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$ $$ language sql
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 the
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
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)
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
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 should try:
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
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
immutable. Now in-query
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
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
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:
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
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
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
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
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 ... LIMIT 1?
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
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 problem
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 7.1.1
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
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 use
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
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
26 matches
Mail list logo
