Hi,
I must convert an old table into a new table. The conversion goes at ~
100 records per second. Given the fact that I must convert 40 million
records, it takes too much time: more hours than the 48 hour weekend I
have for the conversion;-).
The tables are rather simple: both tables only have
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
Hi Joost,
why do you convert programmatically? I would do something like
create sequence s_objectid;
insert into
prototype.orders(objectid,ordernumber,orderdate,customernumber)
select next_val('s_objectid'),ordernummer, orderdatum, klantnummer from
odbc.orders
Sounds a lot faster to me.
I have concerns about whether we are overallocating memory for use in
external sorts. (All code relating to this is in tuplesort.c)
When we begin a sort we allocate (work_mem | maintenance_work_mem) and
attempt to do the sort in memory. If the sort set is too big to fit in
memory we then write to
On Fri, Sep 23, 2005 at 08:49:27AM +0200, Joost Kraaijeveld wrote:
3. Can I anyhow improve the performance without replacing my hardware,
e.g. by tweaking the software?
It's not clear what your object id generator does. If it's just a
sequence, it's not clear that you need this program at
On Fri, 2005-09-23 at 05:55 -0400, Michael Stone wrote:
It's not clear what your object id generator does. If it's just a
sequence, it's not clear that you need this program at all--just use a
SELECT INTO and make the object id a SERIAL.
It generates a GUID (and no, I do not want to turn this
On Fri, Sep 23, 2005 at 12:21:15PM +0200, Joost Kraaijeveld wrote:
On Fri, 2005-09-23 at 05:55 -0400, Michael Stone wrote:
It's not clear what your object id generator does. If it's just a
sequence, it's not clear that you need this program at all--just use a
SELECT INTO and make the object id
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:
On 23-9-2005 13:05, Michael Stone wrote:
On Fri, Sep 23, 2005 at 12:21:15PM +0200, Joost Kraaijeveld wrote:
Ok, that's great, but you didn't respond to the suggestion of using COPY
INTO instead of INSERT.
But I have no clue where to begin with determining the bottleneck (it
even may be a
On Fri, 23 Sep 2005, Ahmad Fajar wrote:
Hi Oleg,
I didn't deny on the third repeat or more, it can reach 600 msec. It is
only because the result still in postgres cache, but how about in the first
run? I didn't dare, the values is un-acceptable. Because my table will grows
rapidly, it's about
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
On Fri, 2005-09-23 at 13:19 +0200, Arjen van der Meijden wrote:
Another suggestion:
How many indexes and constraints are on the new table?
As mentioned in the first mail: in this tables only primary key
constraints, no other indexes or constraints.
Drop all of them and recreate them once the
eVl [EMAIL PROTECTED] writes:
When executing this SELECT (see SELECT.A above) it executes in about
700 ms, but when I want wipe out all info about local traffic, with query
like this:
SELECT * FROM ( SELECT.A ) a WHERE type = 'global';
It executes about 1 ms - more then 10
On Fri, 2005-09-23 at 07:05 -0400, Michael Stone wrote:
On Fri, Sep 23, 2005 at 12:21:15PM +0200, Joost Kraaijeveld wrote:
On Fri, 2005-09-23 at 05:55 -0400, Michael Stone wrote:
I didn't say it was, did I?
No, you did not. But only last week someon'es head was (luckely for him
only
On 23-9-2005 15:35, Joost Kraaijeveld wrote:
On Fri, 2005-09-23 at 13:19 +0200, Arjen van der Meijden wrote:
Drop all of them and recreate them once the table is filled. Of course
that only works if you know your data will be ok (which is normal for
imports of already conforming data like
On Tue, Sep 20, 2005 at 14:53:19 -0400,
Markus Benne [EMAIL PROTECTED] wrote:
I have a table that is purged by 25% each night. I'd like to do a
vacuum nightly after the purge to reclaim the space, but I think I'll
still need to do a vacuum full weekly.
Would there be any benefit to doing
Joost Kraaijeveld [EMAIL PROTECTED] writes:
I will test this a for perfomance improvement, but still, I wonder if ~
100 inserts/second is a reasonable performance for my software/hardware
combination.
Is the client code running on the same machine as the database server?
If not, what's the
On Fri, 2005-09-23 at 10:33 -0400, Tom Lane wrote:
Is the client code running on the same machine as the database server?
If not, what's the network delay and latency between them?
Yes, it is running on the same machine.
The major problem you're going to have here is at least one network
Joost Kraaijeveld [EMAIL PROTECTED] writes:
But do you maybe know a pointer to info, or tools that can measure, what
my machine is doing during all the time it is doing nothing? Something
like the performance monitor in Windows but than for Linux?
top, vmstat, iostat, sar, strace, oprofile,
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
On Fri, Sep 23, 2005 at 03:49:25PM +0200, Joost Kraaijeveld wrote:
On Fri, 2005-09-23 at 07:05 -0400, Michael Stone wrote:
Ok, that's great, but you didn't respond to the suggestion of using COPY
INTO instead of INSERT.
Part of the code I left out are some data conversions (e.g. from
Hi
We are experiencing consistent slowness on the database for
one application. This is more a reporting type of application, heavy on the
bytea data type usage (gets rendered into PDFs in the app server). A lot of
queries, mostly selects and a few random updates, get accumulated on the
Bruno Wolff III mentioned :
= If you have a proper FSM setting you shouldn't need to do vacuum fulls
= (unless you have an older version of postgres where index bloat might
= be an issue).
What version of postgres was the last version that had
the index bloat problem?
Hi Oleg,
For single index I try this query:
explain analyze
select articleid, title, datee from articles
where fti @@ to_tsquery('bankindonesia');
analyze result:
Index Scan using fti_idx on articles (cost=0.00..862.97 rows=420 width=51)
(actual time=0.067..183761.324
On Fri, Sep 23, 2005 at 06:16:44PM +0200, Stef wrote:
Bruno Wolff III mentioned :
= If you have a proper FSM setting you shouldn't need to do vacuum fulls
= (unless you have an older version of postgres where index bloat might
= be an issue).
What version of postgres was the last version
On Fri, Sep 23, 2005 at 18:16:44 +0200,
Stef [EMAIL PROTECTED] wrote:
Bruno Wolff III mentioned :
= If you have a proper FSM setting you shouldn't need to do vacuum fulls
= (unless you have an older version of postgres where index bloat might
= be an issue).
What version of postgres was
After further investigation I have found that the reason why the query is
slower on 8.0.3 is that the hash and hash joins are slower on the 8.0.3.
So the question comes down to : Why are hash and hash joins slower? Is this
a postgres configuration setting that I am missing? Is the locale still
Bruno Wolff III mentioned :
= = If you have a proper FSM setting you shouldn't need to do vacuum fulls
= = (unless you have an older version of postgres where index bloat might
= = be an issue).
Thanks Alvaro and Bruno
I just want to clarify something that I also couldn't
find a clear cut
Ron Peacetree [EMAIL PROTECTED] writes:
2= No optimal external sorting algorithm should use more than 2 passes.
3= Optimal external sorting algorithms should use 1 pass if at all possible.
A comparison-based sort must use at least N log N operations, so it
would appear to me that if you haven't
operations != passes. If you were clever, you could probably write a
modified bubble-sort algorithm that only made 2 passes. A pass is a
disk scan, operations are then performed (hopefully in memory) on what
you read from the disk. So there's no theoretical log N lower-bound on
the number of
[EMAIL PROTECTED] (Stef) writes:
Bruno Wolff III mentioned :
= If you have a proper FSM setting you shouldn't need to do vacuum fulls
= (unless you have an older version of postgres where index bloat might
= be an issue).
What version of postgres was the last version that had
the index
Mark Lewis [EMAIL PROTECTED] writes:
operations != passes. If you were clever, you could probably write a
modified bubble-sort algorithm that only made 2 passes. A pass is a
disk scan, operations are then performed (hopefully in memory) on what
you read from the disk. So there's no
Yep. Also, bear in mind that the lg(n!)= ~ nlgn - n lower bound on
the number of comparisions:
a= says nothing about the amount of data movement used.
b= only holds for generic comparison based sorting algorithms.
As Knuth says (vol 3, p180), Distribution Counting sorts without
ever comparing
From: Tom Lane [EMAIL PROTECTED]
Sent: Sep 23, 2005 2:15 PM
Subject: Re: [PERFORM] Releasing memory during External sorting?
Mark Lewis [EMAIL PROTECTED] writes:
operations != passes. If you were clever, you could probably write a
modified bubble-sort algorithm that only made 2 passes. A
Gurpreet Aulakh [EMAIL PROTECTED] writes:
After further investigation I have found that the reason why the query is
slower on 8.0.3 is that the hash and hash joins are slower on the 8.0.3.
So the question comes down to : Why are hash and hash joins slower?
I looked into this a bit and
Hello pals, I have the following table in Postgresql 8.0.1
Mydb# \d geoip_block
Table public.geoip_block
Column| Type | Modifiers
-++---
locid | bigint |
start_block | inet |
end_block | inet |
mydb# explain analyze select locid 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
Simon Riggs [EMAIL PROTECTED] writes:
If not, I would propose that when we move from qsort to tapesort mode we
free the larger work_mem setting (if one exists) and allocate only a
lower, though still optimal setting for the tapesort. That way the
memory can be freed for use by other users or
On Fri, 2005-09-23 at 10:09 -0400, Tom Lane wrote:
Simon Riggs [EMAIL PROTECTED] writes:
If not, I would propose that when we move from qsort to tapesort mode we
free the larger work_mem setting (if one exists) and allocate only a
lower, though still optimal setting for the tapesort. That
Simon Riggs [EMAIL PROTECTED] writes:
Since we know the predicted size of the sort set prior to starting the
sort node, could we not use that information to allocate memory
appropriately? i.e. if sort size is predicted to be more than twice the
size of work_mem, then just move straight to the
From: Simon Riggs [EMAIL PROTECTED]
Sent: Sep 23, 2005 5:37 AM
Subject: [PERFORM] Releasing memory during External sorting?
I have concerns about whether we are overallocating memory for use in
external sorts. (All code relating to this is in tuplesort.c)
A decent external sorting algorithm, say
44 matches
Mail list logo
