Re: [PERFORM] Poor performance of delete by primary key

Unfortunately there's no very simple way to determine which FK is the problem. (In 8.1 it'll be possible to do that with EXPLAIN ANALYZE, but in existing releases EXPLAIN doesn't break out the time spent in each trigger ...) You have to just eyeball the schema :-(. phpPgAdmin has a handy info

Re: [PERFORM] poor VACUUM performance on large tables

Jan Peterson <[EMAIL PROTECTED]> writes: > Based on this, it looks like we could stand to bump up our FSM another > couple hundred thousand. Does it buy us anything to reduce the number > of FSM relations from the default of 1000? Not a lot; as the comment says, those slots are only about 50 byte

Re: [PERFORM] poor VACUUM performance on large tables

Thomas F. O'Connell: >Do you have your Free Space Map settings configured appropriately? Our current FSM settings are: max_fsm_pages = 50 # min max_fsm_relations*16, 6 bytes each max_fsm_relations = 1000# min 100, ~50 bytes each > You'll want to run a VACUUM VERBOSE and note

[PERFORM] log_duration times

Hi,   We want to discover how to improve the performance of an application and with that intention I turned on log_duration, log_statement=all and the time stamp escape character (%t) of log_line_prefix.   Subtracting the time stamp of the last SQL statement from the first one I disovered that the

Re: [PERFORM] Poor performance of delete by primary key

Mark Lewis <[EMAIL PROTECTED]> writes: > I had a similar problem, so I downloaded 8.1 from CVS, ran it on a > relatively gnarly dev workstation, imported a dump of my 8.0 database, > and ran my troublesome queries with the new EXPLAIN ANALYZE. > This process took about an hour and worked great, pr

Re: [PERFORM] insert performance for win32

> This makes me wonder if we are looking in the wrong place. Maybe the > problem is coming from psql? More results to follow. problem is not coming from psql. One thing I did notice that in a 250k insert transaction the insert time grows with #recs inserted. Time to insert first 50k recs is

Re: [PERFORM] Poor performance of delete by primary key

I had a similar problem, so I downloaded 8.1 from CVS, ran it on a relatively gnarly dev workstation, imported a dump of my 8.0 database, and ran my troublesome queries with the new EXPLAIN ANALYZE. This process took about an hour and worked great, provided that you've actually named your foreign

Re: [PERFORM] insert performance for win32

> > In my experience win32 is par with linux generally with a few gotchas on > > either side.  Are your times with fsync=no? It's much harder to give > > apples-apples comparison with fsync=on for various reasons. > It is with fsync=off on windows, fsync=on on linux well, inside a transaction this

Re: [PERFORM] Poor performance of delete by primary key

Brian Choate wrote: Hello, We are seeing a very strange behavior from postgres. For one of our very common tasks we have to delete records from a table of around 500,000 rows. The delete is by id which is the primary key. It seems to be consistently taking around 10 minutes to preform. This is t

Re: [PERFORM] Massive performance issues

Hi, Many thanks for all your thoughts and advice. With just 2GB or RAM, no change to the harddisc (still SATA) but proper tuning of Postgresql (still 7.4) and aggressive normalization to shrink row width, I have managed to get suitable performance, with, when fully cached, queries on a 5 million r

Re: [PERFORM] Poor performance of delete by primary key

On Tue, Sep 06, 2005 at 11:32:00AM -0400, Tom Lane wrote: > "Brian Choate" <[EMAIL PROTECTED]> writes: > > We are seeing a very strange behavior from postgres. For one of our very = > > common tasks we have to delete records from a table of around 500,000 = > > rows. The delete is by id which is th

Re: [PERFORM] Poor performance of delete by primary key

"Brian Choate" <[EMAIL PROTECTED]> writes: > We are seeing a very strange behavior from postgres. For one of our very = > common tasks we have to delete records from a table of around 500,000 = > rows. The delete is by id which is the primary key. It seems to be = > consistently taking around 10 mi

Re: [PERFORM] insert performance for win32

> > In my experience win32 is par with linux generally with a few gotchas on > either side.  Are your times with fsync=no? It's much harder to give > apples-apples comparison with fsync=on for various reasons. It is with fsync=off on windows, fsync=on on linux > > Are you running stats_command_str

Re: [PERFORM] Postgresql Hardware - Recommendations

On 9/5/05, [EMAIL PROTECTED] <[EMAIL PROTECTED]> wrote: > ... The only info I can > give so far is that the database size is about 60GB, and that it will be > frequently accessed by multiple users (about 100 will be connected > during business hours). The applications accessing the database are > m

[PERFORM] Poor performance of delete by primary key

Title: Poor performance of delete by primary key Hello, We are seeing a very strange behavior from postgres. For one of our very common tasks we have to delete records from a table of around 500,000 rows. The delete is by id which is the primary key. It seems to be consistently taking around

Re: [PERFORM] insert performance for win32

> Hi, > > I usually use PostgreSQL coupled with Linux, but I have to use Windows for > a > perticular project. > > So I wanted to do some tests to know if the performance will be acceptable > (I > don't need PostgreSQL to be as fast with windows as with linux, but it has > to > be usable...). In

Re: [PERFORM] Improving performance of a query

> > Carlos wrote: > > SELECT * FROM SSIRRA where > > (YEAR = 2004 and CUSTOMER = 04 and CODE = 00 and PART >= 00) or > > (YEAR = 2004 and CUSTOMER = 04 and CODE > 00) or > > (YEAR = 2004 and CUSTOMER > 04) or > > (YEAR > 2004) > > [snip] > > > > ah, the positional query. Yo

[PERFORM] insert performance for win32

Hi, I usually use PostgreSQL coupled with Linux, but I have to use Windows for a perticular project. So I wanted to do some tests to know if the performance will be acceptable (I don't need PostgreSQL to be as fast with windows as with linux, but it has to be usable...). I started with tryin

Re: [PERFORM] Improving performance of a query

On Tue, 6 Sep 2005, Merlin Moncure wrote: > Carlos wrote: > SELECT * FROM SSIRRA where > (YEAR = 2004 and CUSTOMER = 04 and CODE = 00 and PART >= 00) or > (YEAR = 2004 and CUSTOMER = 04 and CODE > 00) or > (YEAR = 2004 and CUSTOMER > 04) or > (YEAR > 2004) > [snip] > > ah,

Re: [PERFORM] When to do a vacuum for highly active table

[EMAIL PROTECTED] ("Rigmor Ukuhe") writes: >> -Original Message- >> From: [EMAIL PROTECTED] [mailto:pgsql-performance- >> [EMAIL PROTECTED] On Behalf Of Markus Benne >> Sent: Wednesday, August 31, 2005 12:14 AM >> To: pgsql-performance@postgresql.org >> Subject: [PERFORM] When to do a vacu

Re: [PERFORM] Improving performance of a query

Carlos wrote: SELECT * FROM SSIRRA where (YEAR = 2004 and CUSTOMER = 04 and CODE = 00 and PART >= 00) or (YEAR = 2004 and CUSTOMER = 04 and CODE > 00) or (YEAR = 2004 and CUSTOMER > 04) or (YEAR > 2004) [snip] ah, the positional query. You can always rewrite this query

Re: [PERFORM] Advise about how to delete entries

Arnau wrote: Hi all, > > COPY FROM a file with all the ID's to delete, into a temporary table, and do a joined delete to your main table (thus, only one query). I already did this, but I don't have idea about how to do this join, could you give me a hint ;-) ? Thank you very much

Re: [PERFORM] Postgresql Hardware - Recommendations

On 9/5/05 6:50 AM, "[EMAIL PROTECTED]" <[EMAIL PROTECTED]> wrote: > The questions we are asking us now are: > > 1) Intel or AMD (or alternate Platform) > Are we better of with Xeons or Opterons? Should we consider the IBM > OpenPower platform? Opteron spanks Xeon for database loads. Advantag