You can also use the freeware junction utility from
http://www.sysinternals.com as we do on Win 2K, XP and 2003.
After installing it, shutdown pg, move pg_xlog to another drive, create
junction as pg_xlog to point to new directory, then restart pg.
Cheers,
KC.
At 03:04 06/12/05, Merlin Moncu
At 10:39 06/04/29, Tom Lane wrote:
K C Lau <[EMAIL PROTECTED]> writes:
> Without knowing the internals, I have this simplistic idea: if Postgres
> maintains the current lowest transaction ID for all active
transactions, it
> probably could recycle dead tuples on the fly.
[
At 03:00 06/04/29, Bruno Wolff III wrote:
On Fri, Apr 28, 2006 at 17:37:30 +,
Bealach-na Bo <[EMAIL PROTECTED]> wrote:
> >The above shows that the indexes contained 10M rows and 160M of dead
> >space each. That means you weren't vacuuming nearly enough.
>
> How is it that a row in the tabl
Thank you very much, Tom. We'll try it and report if there is any
significant impact performance-wise.
Best regards,
KC.
At 00:25 06/03/25, Tom Lane wrote:
K C Lau <[EMAIL PROTECTED]> writes:
> Indeed, I get rejected even with:
> .. WHERE ANY(array) = 'xx'
> It
With 8.1.3, I get an error when trying to do this on a Text[] column
:
.. WHERE ANY(array) LIKE 'xx%'
Indeed, I get rejected even with:
.. WHERE ANY(array) = 'xx'
In both cases, the error is: ERROR: syntax error at or near
"any" ...
It would only work as documented in the manual (8.10.5):
S
TED]> writes:
> On Fri, Jan 20, 2006 at 12:35:36PM +0800, K C Lau wrote:
> Here's the problem... the estimate for the backwards index scan is *way*
> off:
>> -> Limit (cost=0.00..1.26 rows=1 width=4) (actual
>> time=200032.928..200032.931 rows=1 loops=1)
>&g
At 01:20 06/01/21, Jim C. Nasby wrote:
BTW, these queries below are meaningless; they are not equivalent to
min(logsn).
> esdt=> explain analyze select LogSN from Log where create_time <
> '2005/10/19' order by create_time limit 1;
Thank you for pointing it out.
It actually returns the min(l
The following query took 17 seconds:
select count(LogSN), min(LogSN), max(LogSN) from Log where create_time <
'2005/10/19';
Figuring that getting the count will involve scanning the database, I took
it out, but the new query took 200 seconds:
select min(LogSN), max(LogSN) from Log where crea
At 09:26 06/01/12, you wrote:
On Jan 12, 2006, at 9:36 , K C Lau wrote:
NOTICE: CREATE TABLE / PRIMARY KEY will create implicit index
"cities_pkey" for table "cities"
Is there a way to suppress this notice when I create tables in a
script?
Set[1] your log_min_me
At 07:21 06/01/12, Michael Fuhr wrote:
On Wed, Jan 11, 2006 at 02:38:42PM -0800, Burak Seydioglu wrote:
> I do a load of sql joins using primary and foreign keys. What i would like
> to know if PostgreSQL creates indexes on these columns automatically (in
> addition to using them to maintain refe
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
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
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
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 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
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
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?
>
&g
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 proble
ll 1743 data records for that
player? Is that a bug or known issue?
BTW, I tried using 8.1 beta2 on Windows and its performance is similar, I
have also tried other variants such as MAX and DISTINCT but with no success.
Any help is most appreciated.
Best regards,
KC.
At 10:46 05/06/15, K C L
Hi All,
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 ... LIMI
/msg00110.php
on this show-stopper problem for which I still have no clue how to get
around. Suggestions are much appreciated.
Thanks and regards, KC.
At 21:34 05/06/08, George Essig wrote:
On 6/2/05, K C Lau <[EMAIL PROTECTED]> wrote:
...
>
> select DISTINCT ON (PlayerID) PlayerID
At 19:45 05/06/06, PFC wrote:
Previously, we have also tried to use LIMIT 1 instead of DISTINCT, but
the performance was no better:
select PlayerID,AtDate from Player where PlayerID='0' order by
PlayerID desc, AtDate desc LIMIT 1
The DISTINCT query will pull out all the rows and k
Hi All,
We are testing PostgreSQL 8.0.3 on MS Windows for porting an OLTP system
from MS SqlServer.
We got a major performance issue which seems to boil down to the following
type of query:
select DISTINCT ON (PlayerID) PlayerID,AtDate from Player where
PlayerID='0' order by PlayerID
24 matches
Mail list logo
