[PERFORM] How to determine cause of performance problem?

2005-09-23 Thread Joost Kraaijeveld
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

Re: [PERFORM] tsearch2 seem very slow

2005-09-23 Thread Oleg Bartunov
Ahmad, how fast is repeated runs ? First time system could be very slow. Also, have you checked my page http://www.sai.msu.su/~megera/oddmuse/index.cgi/Tsearch_V2_Notes and some info about tsearch2 internals http://www.sai.msu.su/~megera/oddmuse/index.cgi/Tsearch_V2_internals Oleg On

Re: [PERFORM] SELECT LIMIT 1 VIEW Performance Issue

2005-09-23 Thread K C Lau
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

Re: [PERFORM] How to determine cause of performance problem?

2005-09-23 Thread Ulrich Wisser
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.

[PERFORM] Releasing memory during External sorting?

2005-09-23 Thread Simon Riggs
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

Re: [PERFORM] How to determine cause of performance problem?

2005-09-23 Thread Michael Stone
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

Re: [PERFORM] How to determine cause of performance problem?

2005-09-23 Thread Joost Kraaijeveld
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

Re: [PERFORM] How to determine cause of performance problem?

2005-09-23 Thread Michael Stone
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

Re: [PERFORM] SELECT LIMIT 1 VIEW Performance Issue

2005-09-23 Thread Simon Riggs
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:

Re: [PERFORM] How to determine cause of performance problem?

2005-09-23 Thread Arjen van der Meijden
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

Re: [PERFORM] tsearch2 seem very slow

2005-09-23 Thread Oleg Bartunov
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

Re: [PERFORM] How to determine cause of performance problem?

2005-09-23 Thread Dave Cramer
Joost, I presume you are using a relatively new jdbc driver. Make sure you have added prepareThreshold=1 to the url to that it will use a named server side prepared statement You could also use your mod 100 code block to implement batch processing of the inserts. see addBatch, in jdbc

Re: [PERFORM] SELECT LIMIT 1 VIEW Performance Issue

2005-09-23 Thread K C Lau
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

Re: [PERFORM] SELECT LIMIT 1 VIEW Performance Issue

2005-09-23 Thread Merlin Moncure
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

Re: [PERFORM] How to determine cause of performance problem?

2005-09-23 Thread Joost Kraaijeveld
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

Re: [PERFORM] optimization downgrade perfomance?

2005-09-23 Thread Tom Lane
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

Re: [PERFORM] How to determine cause of performance problem?

2005-09-23 Thread Joost Kraaijeveld
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

Re: [PERFORM] How to determine cause of performance problem?

2005-09-23 Thread Arjen van der Meijden
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

Re: [PERFORM] VACUUM FULL vs CLUSTER

2005-09-23 Thread Bruno Wolff III
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

Re: [PERFORM] How to determine cause of performance problem?

2005-09-23 Thread Tom Lane
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

Re: [PERFORM] How to determine cause of performance problem?

2005-09-23 Thread Joost Kraaijeveld
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

Re: [PERFORM] How to determine cause of performance problem?

2005-09-23 Thread Tom Lane
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,

Re: [PERFORM] SELECT LIMIT 1 VIEW Performance Issue

2005-09-23 Thread K C Lau
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

Re: [PERFORM] How to determine cause of performance problem?

2005-09-23 Thread Michael Stone
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

[PERFORM] slow database, queries accumulating

2005-09-23 Thread Anjan Dave
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

Re: [PERFORM] VACUUM FULL vs CLUSTER

2005-09-23 Thread Stef
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?

Re: [PERFORM] tsearch2 seem very slow

2005-09-23 Thread Ahmad Fajar
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

Re: [PERFORM] VACUUM FULL vs CLUSTER

2005-09-23 Thread Alvaro Herrera
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

Re: [PERFORM] VACUUM FULL vs CLUSTER

2005-09-23 Thread Bruno Wolff III
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

Re: [PERFORM] Query slower on 8.0.3 (Windows) vs 7.3 (cygwin)

2005-09-23 Thread Gurpreet Aulakh
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

Re: [PERFORM] VACUUM FULL vs CLUSTER

2005-09-23 Thread Stef
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

Re: [PERFORM] Releasing memory during External sorting?

2005-09-23 Thread Tom Lane
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

Re: [PERFORM] Releasing memory during External sorting?

2005-09-23 Thread Mark Lewis
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

Re: [PERFORM] VACUUM FULL vs CLUSTER

2005-09-23 Thread Chris Browne
[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

Re: [PERFORM] Releasing memory during External sorting?

2005-09-23 Thread Tom Lane
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

Re: [PERFORM] Releasing memory during External sorting?

2005-09-23 Thread Ron Peacetree
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

Re: [PERFORM] Releasing memory during External sorting?

2005-09-23 Thread Ron Peacetree
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

Re: [PERFORM] Query slower on 8.0.3 (Windows) vs 7.3 (cygwin)

2005-09-23 Thread Tom Lane
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

[PERFORM] Index use in BETWEEN statement...

2005-09-23 Thread Cristian Prieto
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

Re: [PERFORM] SELECT LIMIT 1 VIEW Performance Issue

2005-09-23 Thread Mark Kirkwood
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

Re: [PERFORM] optimization downgrade perfomance?

2005-09-23 Thread Tom Lane
eVl [EMAIL PROTECTED] writes: You tell us --- let's see EXPLAIN ANALYZE results for both cases. Here EXPLAIN ANALYZE results for both queries attached. The problem seems to be that the is_uaix() function is really slow (somewhere around 4 msec per call it looks like). Look at the first scan

Re: [PERFORM] SELECT LIMIT 1 VIEW Performance Issue

2005-09-23 Thread K C Lau
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

Re: [PERFORM] SELECT LIMIT 1 VIEW Performance Issue

2005-09-23 Thread Mark Kirkwood
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

Re: [PERFORM] [HACKERS] Releasing memory during External sorting?

2005-09-23 Thread Tom Lane
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

Re: [PERFORM] [HACKERS] Releasing memory during External sorting?

2005-09-23 Thread Simon Riggs
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

Re: [PERFORM] [HACKERS] Releasing memory during External sorting?

2005-09-23 Thread Tom Lane
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

Re: [PERFORM] Releasing memory during External sorting?

2005-09-23 Thread Ron Peacetree
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