Re: DELETE performance issues

On Fri, Oct 27, 2006 at 03:40:51PM -0400, Schwenker, Stephen wrote:
> I'm having a performance issue with postgresql. I'm using it with JBoss
> BPM api and when I use the api to delete a process instance from the
> database, it seems like the delete requests are taking quite a long time
> to delete.

How long is "quite a long time"? How long are you expecting the
delete to take? Do you get better performance if you don't use the
API, like when executing the same statement in psql? How many rows
are you deleting? How many rows are in the table? Is the delete
able to use an index? Do you have unindexed foreign key references
to the table you're deleting from? What does EXPLAIN ANALYZE show?
Are you regularly vacuuming and analyzing the table? What version
of PostgreSQL are you using? What OS and hardware?

> Can someone tell me what logging I can turn on so I can figure out
> what is causing the high load and slow deletes? I can then use that
> info to tune the runtime parameters.

What non-default settings are you currently using?

--
Michael Fuhr