pg.ddx.io  pgsql-performance@postgresql.org mailing list archive  
help / color / mirror / Atom feed
Performance problem on delete from for 10k rows. May takes 20 minutes through JDBC interface
12+ messages / 6 participants
[nested] [flat]

* Performance problem on delete from for 10k rows. May takes 20 minutes through JDBC interface
@ 2005-03-15 21:24 David Gagnon <dgagnon@siunik.com>
  2005-03-15 23:38 ` Re: Performance problem on delete from for 10k rows. May takes 20 minutes through JDBC interface Michael Fuhr <mike@fuhr.org>
  2005-03-15 23:50 ` Re: Performance problem on delete from for 10k rows. Rod Taylor <pg@rbt.ca>
  0 siblings, 2 replies; 12+ messages in thread

From: David Gagnon @ 2005-03-15 21:24 UTC (permalink / raw)
  To: pgsql-performance

Hi all,

  Il get this strange problem when deleting rows from a Java program.  
Sometime (For what I noticed it's not all the time) the server take 
almost forever to delete rows from table.

Here It takes 20 minutes to delete the IC table.

Java logs:
INFO  [Thread-386] (Dao.java:227)  2005-03-15 15:38:34,754 : Execution 
SQL file: resources/ukConfiguration/reset_application.sql
DELETE FROM YR
INFO  [Thread-386] (Dao.java:227)  2005-03-15 15:38:34,964 : Execution 
SQL file: resources/inventory/item/reset_application.sql
DELETE FROM IC
INFO  [Thread-386] (Dao.java:227)  2005-03-15 15:58:45,072 : Execution 
SQL file: resources/ukResource/reset_application.sql
DELETE FROM RA


 I get this problem on my dev (Windows/7.4/Cygwin) environment.  But now 
I see that it's also have this problem on my production env.  Yes I 
tought I was maybe just a cygwin/Windows problem .. apparently not :-((((

On my dev I can see the Postgresql related process running at almost 50% 
of CPU usage for all the time.  So I suppose it's something inside 
Postgresql.  I rememeber having tried to delete the content of my table 
(IC) from PgAdminIII and I took couples of seconds!!! Not minutes.  So 
the process don't jam but take time .. any Idea what postgresql is doing 
during this time??

If you have any idea on what the problem could be... I really appreciate 
it. 

Thanks for any help!
/David
 
 





^ permalink  raw  reply  [nested|flat] 12+ messages in thread

* Re: Performance problem on delete from for 10k rows. May takes 20 minutes through JDBC interface
  2005-03-15 21:24 Performance problem on delete from for 10k rows. May takes 20 minutes through JDBC interface David Gagnon <dgagnon@siunik.com>
@ 2005-03-15 23:38 ` Michael Fuhr <mike@fuhr.org>
  2005-03-16 13:18   ` Re: Performance problem on delete from for 10k rows.  May David Gagnon <dgagnon@siunik.com>
  1 sibling, 1 reply; 12+ messages in thread

From: Michael Fuhr @ 2005-03-15 23:38 UTC (permalink / raw)
  To: David Gagnon <dgagnon@siunik.com>; +Cc: pgsql-performance

On Tue, Mar 15, 2005 at 04:24:17PM -0500, David Gagnon wrote:

>  Il get this strange problem when deleting rows from a Java program.  
> Sometime (For what I noticed it's not all the time) the server take 
> almost forever to delete rows from table.

Do other tables have foreign key references to the table you're
deleting from?  If so, are there indexes on the foreign key columns?

Do you have triggers or rules on the table?

Have you queried pg_locks during the long-lasting deletes to see
if the deleting transaction is waiting for a lock on something?

> I rememeber having tried to delete the content of my table (IC) from
> PgAdminIII and I took couples of seconds!!! Not minutes.

How many records did you delete in this case?  If there are foreign
key references, how many records were in the referencing tables?
How repeatable is the disparity in delete time?  A single test case
might have been done under different conditions, so it might not
mean much.  No offense intended, but "I remember" doesn't carry as
much weight as a documented example.

-- 
Michael Fuhr
http://www.fuhr.org/~mfuhr/



^ permalink  raw  reply  [nested|flat] 12+ messages in thread

* Re: Performance problem on delete from for 10k rows.  May
  2005-03-15 21:24 Performance problem on delete from for 10k rows. May takes 20 minutes through JDBC interface David Gagnon <dgagnon@siunik.com>
  2005-03-15 23:38 ` Re: Performance problem on delete from for 10k rows. May takes 20 minutes through JDBC interface Michael Fuhr <mike@fuhr.org>
@ 2005-03-16 13:18   ` David Gagnon <dgagnon@siunik.com>
  2005-03-16 14:06     ` Re: Performance problem on delete from for 10k rows.  May Dave Cramer <pg@fastcrypt.com>
  2005-03-16 14:35     ` Re: Performance problem on delete from for 10k rows.  May Alvaro Herrera <alvherre@dcc.uchile.cl>
  0 siblings, 2 replies; 12+ messages in thread

From: David Gagnon @ 2005-03-16 13:18 UTC (permalink / raw)
  To: Michael Fuhr <mike@fuhr.org>; +Cc: pgsql-performance

Hi All,

I rerun the example with the debug info turned on in postgresl. As you 
can see all dependent tables (that as foreign key on table IC) are 
emptied before the DELETE FROM IC statement is issued.  For what I 
understand the performance problem seem to came from those selects that 
point back to IC ( LOG:  statement: SELECT 1 FROM ONLY "public"."ic" x 
WHERE "icnum" = $1 FOR UPDATE OF x).  There are 6 of them.  I don't know 
where they are comming from.  But if I want to delete the content of the 
table (~10k) it may be long to those 6 selects for each deleted rows.  
Why are those selects are there ?  Are those select really run on each 
row deleted?

I'm running version 7.4.5 on cygwin.  I ran the same delete from 
pgAdminIII and I got 945562ms for all the deletes within the same 
transaction  .. (so I was wrong saying it took less time in 
PgAdminIII... sorry about this).

Do you have any idea why those 6 selects are there?

Maybe I can drop indexes before deleting the content of the table.  I 
didn't planned to because tables are quite small and it's more 
complicated in my environment.  And tell me if I'm wrong but if I drop 
indexed do I have to reload all my stored procedure (to reset the 
planner related info)??? Remember having read that somewhere.. (was it 
in the Postgresql General Bit newletter ...anyway)

Thanks for your help I really appréciate it :-)

/David

LOG:  duration: 144.000 ms
LOG:  statement: DELETE FROM YN
LOG:  duration: 30.000 ms
LOG:  statement: DELETE FROM YO
LOG:  statement: SELECT 1 FROM ONLY "public"."yo" x WHERE "yotype" = $1 
AND "yonum" = $2 FOR UPDATE OF x
LOG:  statement: SELECT 1 FROM ONLY "public"."yn" x WHERE "ynyotype" = 
$1 AND "ynyonum" = $2 FOR UPDATE OF x
LOG:  statement: SELECT 1 FROM ONLY "public"."yo" x WHERE "yotype" = $1 
AND "yonum" = $2 FOR UPDATE OF x
LOG:  statement: SELECT 1 FROM ONLY "public"."yr" x WHERE "yryotype" = 
$1 AND "yryonum" = $2 FOR UPDATE OF x
LOG:  duration: 83.000 ms
LOG:  connection received: host=127.0.0.1 port=2196
LOG:  connection authorized: user=admin database=webCatalog
LOG:  statement: set datestyle to 'ISO'; select version(), case when 
pg_encoding_to_char(1) = 'SQL_ASCII' then 'UNKNOWN' else 
getdatabaseencoding() end;
LOG:  duration: 2.000 ms
LOG:  statement: set client_encoding = 'UNICODE'
LOG:  duration: 0.000 ms
LOG:  statement: DELETE FROM IY
LOG:  duration: 71.000 ms
LOG:  statement: DELETE FROM IA
LOG:  duration: 17.000 ms
LOG:  statement: DELETE FROM IQ
LOG:  duration: 384.000 ms
LOG:  statement: DELETE FROM IC
LOG:  statement: SELECT 1 FROM ONLY "public"."ic" x WHERE "icnum" = $1 
FOR UPDATE OF x
LOG:  statement: SELECT 1 FROM ONLY "public"."iq" x WHERE "iqicnum" = $1 
FOR UPDATE OF x
LOG:  statement: SELECT 1 FROM ONLY "public"."ic" x WHERE "icnum" = $1 
FOR UPDATE OF x
LOG:  statement: SELECT 1 FROM ONLY "public"."ia" x WHERE "iaicnum" = $1 
FOR UPDATE OF x
LOG:  statement: SELECT 1 FROM ONLY "public"."ic" x WHERE "icnum" = $1 
FOR UPDATE OF x
LOG:  statement: SELECT 1 FROM ONLY "public"."iy" x WHERE "iyicnumo" = 
$1 FOR UPDATE OF x
LOG:  statement: SELECT 1 FROM ONLY "public"."ic" x WHERE "icnum" = $1 
FOR UPDATE OF x
LOG:  statement: SELECT 1 FROM ONLY "public"."iy" x WHERE "iyicnumr" = 
$1 FOR UPDATE OF x
LOG:  statement: SELECT 1 FROM ONLY "public"."ic" x WHERE "icnum" = $1 
FOR UPDATE OF x
LOG:  statement: SELECT 1 FROM ONLY "public"."il" x WHERE "ilicnum" = $1 
FOR UPDATE OF x
LOG:  statement: SELECT 1 FROM ONLY "public"."ic" x WHERE "icnum" = $1 
FOR UPDATE OF x
LOG:  statement: SELECT 1 FROM ONLY "public"."bd" x WHERE "bdicnum" = $1 
FOR UPDATE OF x
LOG:  duration: 656807.000 msMichael Fuhr wrote:





-----------------------
DELETE FROM BM;
DELETE FROM BD;
DELETE FROM BO;
DELETE FROM IL;
DELETE FROM YR;
DELETE FROM YN;
DELETE FROM YO;
DELETE FROM IY;
DELETE FROM IA;
DELETE FROM IQ;
DELETE FROM IC;

Michael Fuhr wrote:

>On Tue, Mar 15, 2005 at 04:24:17PM -0500, David Gagnon wrote:
>
>  
>
>> Il get this strange problem when deleting rows from a Java program.  
>>Sometime (For what I noticed it's not all the time) the server take 
>>almost forever to delete rows from table.
>>    
>>
>
>Do other tables have foreign key references to the table you're
>deleting from?  If so, are there indexes on the foreign key columns?
>
>Do you have triggers or rules on the table?
>
>Have you queried pg_locks during the long-lasting deletes to see
>if the deleting transaction is waiting for a lock on something?
>
>  
>
>>I rememeber having tried to delete the content of my table (IC) from
>>PgAdminIII and I took couples of seconds!!! Not minutes.
>>    
>>
>
>How many records did you delete in this case?  If there are foreign
>key references, how many records were in the referencing tables?
>How repeatable is the disparity in delete time?  A single test case
>might have been done under different conditions, so it might not
>mean much.  No offense intended, but "I remember" doesn't carry as
>much weight as a documented example.
>
>  
>




^ permalink  raw  reply  [nested|flat] 12+ messages in thread

* Re: Performance problem on delete from for 10k rows.  May
  2005-03-15 21:24 Performance problem on delete from for 10k rows. May takes 20 minutes through JDBC interface David Gagnon <dgagnon@siunik.com>
  2005-03-15 23:38 ` Re: Performance problem on delete from for 10k rows. May takes 20 minutes through JDBC interface Michael Fuhr <mike@fuhr.org>
  2005-03-16 13:18   ` Re: Performance problem on delete from for 10k rows.  May David Gagnon <dgagnon@siunik.com>
@ 2005-03-16 14:06     ` Dave Cramer <pg@fastcrypt.com>
  2005-03-16 14:26       ` Re: Performance problem on delete from for 10k rows.  May David Gagnon <dgagnon@siunik.com>
  1 sibling, 1 reply; 12+ messages in thread

From: Dave Cramer @ 2005-03-16 14:06 UTC (permalink / raw)
  To: David Gagnon <dgagnon@siunik.com>; +Cc: Michael Fuhr <mike@fuhr.org>; pgsql-performance



David Gagnon wrote:

> Hi All,
>
> I rerun the example with the debug info turned on in postgresl. As you 
> can see all dependent tables (that as foreign key on table IC) are 
> emptied before the DELETE FROM IC statement is issued.  For what I 
> understand the performance problem seem to came from those selects 
> that point back to IC ( LOG:  statement: SELECT 1 FROM ONLY 
> "public"."ic" x WHERE "icnum" = $1 FOR UPDATE OF x).  There are 6 of 
> them.  I don't know where they are comming from.  But if I want to 
> delete the content of the table (~10k) it may be long to those 6 
> selects for each deleted rows.  Why are those selects are there ?  Are 
> those select really run on each row deleted?

You are using hibernate. Hibernate is generating them to lock the tables.

>
>
> I'm running version 7.4.5 on cygwin.  I ran the same delete from 
> pgAdminIII and I got 945562ms for all the deletes within the same 
> transaction  .. (so I was wrong saying it took less time in 
> PgAdminIII... sorry about this).
>
> Do you have any idea why those 6 selects are there?

Hibernate

>
> Maybe I can drop indexes before deleting the content of the table.  I 
> didn't planned to because tables are quite small and it's more 
> complicated in my environment.  And tell me if I'm wrong but if I drop 
> indexed do I have to reload all my stored procedure (to reset the 
> planner related info)??? Remember having read that somewhere.. (was it 
> in the Postgresql General Bit newletter ...anyway)
>
> Thanks for your help I really appréciate it :-)
>
> /David
>
> LOG:  duration: 144.000 ms
> LOG:  statement: DELETE FROM YN
> LOG:  duration: 30.000 ms
> LOG:  statement: DELETE FROM YO
> LOG:  statement: SELECT 1 FROM ONLY "public"."yo" x WHERE "yotype" = 
> $1 AND "yonum" = $2 FOR UPDATE OF x
> LOG:  statement: SELECT 1 FROM ONLY "public"."yn" x WHERE "ynyotype" = 
> $1 AND "ynyonum" = $2 FOR UPDATE OF x
> LOG:  statement: SELECT 1 FROM ONLY "public"."yo" x WHERE "yotype" = 
> $1 AND "yonum" = $2 FOR UPDATE OF x
> LOG:  statement: SELECT 1 FROM ONLY "public"."yr" x WHERE "yryotype" = 
> $1 AND "yryonum" = $2 FOR UPDATE OF x
> LOG:  duration: 83.000 ms
> LOG:  connection received: host=127.0.0.1 port=2196
> LOG:  connection authorized: user=admin database=webCatalog
> LOG:  statement: set datestyle to 'ISO'; select version(), case when 
> pg_encoding_to_char(1) = 'SQL_ASCII' then 'UNKNOWN' else 
> getdatabaseencoding() end;
> LOG:  duration: 2.000 ms
> LOG:  statement: set client_encoding = 'UNICODE'
> LOG:  duration: 0.000 ms
> LOG:  statement: DELETE FROM IY
> LOG:  duration: 71.000 ms
> LOG:  statement: DELETE FROM IA
> LOG:  duration: 17.000 ms
> LOG:  statement: DELETE FROM IQ
> LOG:  duration: 384.000 ms
> LOG:  statement: DELETE FROM IC
> LOG:  statement: SELECT 1 FROM ONLY "public"."ic" x WHERE "icnum" = $1 
> FOR UPDATE OF x
> LOG:  statement: SELECT 1 FROM ONLY "public"."iq" x WHERE "iqicnum" = 
> $1 FOR UPDATE OF x
> LOG:  statement: SELECT 1 FROM ONLY "public"."ic" x WHERE "icnum" = $1 
> FOR UPDATE OF x
> LOG:  statement: SELECT 1 FROM ONLY "public"."ia" x WHERE "iaicnum" = 
> $1 FOR UPDATE OF x
> LOG:  statement: SELECT 1 FROM ONLY "public"."ic" x WHERE "icnum" = $1 
> FOR UPDATE OF x
> LOG:  statement: SELECT 1 FROM ONLY "public"."iy" x WHERE "iyicnumo" = 
> $1 FOR UPDATE OF x
> LOG:  statement: SELECT 1 FROM ONLY "public"."ic" x WHERE "icnum" = $1 
> FOR UPDATE OF x
> LOG:  statement: SELECT 1 FROM ONLY "public"."iy" x WHERE "iyicnumr" = 
> $1 FOR UPDATE OF x
> LOG:  statement: SELECT 1 FROM ONLY "public"."ic" x WHERE "icnum" = $1 
> FOR UPDATE OF x
> LOG:  statement: SELECT 1 FROM ONLY "public"."il" x WHERE "ilicnum" = 
> $1 FOR UPDATE OF x
> LOG:  statement: SELECT 1 FROM ONLY "public"."ic" x WHERE "icnum" = $1 
> FOR UPDATE OF x
> LOG:  statement: SELECT 1 FROM ONLY "public"."bd" x WHERE "bdicnum" = 
> $1 FOR UPDATE OF x
> LOG:  duration: 656807.000 msMichael Fuhr wrote:
>
>
>
>
>
> -----------------------
> DELETE FROM BM;
> DELETE FROM BD;
> DELETE FROM BO;
> DELETE FROM IL;
> DELETE FROM YR;
> DELETE FROM YN;
> DELETE FROM YO;
> DELETE FROM IY;
> DELETE FROM IA;
> DELETE FROM IQ;
> DELETE FROM IC;
>
> Michael Fuhr wrote:
>
>> On Tue, Mar 15, 2005 at 04:24:17PM -0500, David Gagnon wrote:
>>
>>  
>>
>>> Il get this strange problem when deleting rows from a Java program.  
>>> Sometime (For what I noticed it's not all the time) the server take 
>>> almost forever to delete rows from table.
>>>   
>>
>>
>> Do other tables have foreign key references to the table you're
>> deleting from?  If so, are there indexes on the foreign key columns?
>>
>> Do you have triggers or rules on the table?
>>
>> Have you queried pg_locks during the long-lasting deletes to see
>> if the deleting transaction is waiting for a lock on something?
>>
>>  
>>
>>> I rememeber having tried to delete the content of my table (IC) from
>>> PgAdminIII and I took couples of seconds!!! Not minutes.
>>>   
>>
>>
>> How many records did you delete in this case?  If there are foreign
>> key references, how many records were in the referencing tables?
>> How repeatable is the disparity in delete time?  A single test case
>> might have been done under different conditions, so it might not
>> mean much.  No offense intended, but "I remember" doesn't carry as
>> much weight as a documented example.
>>
>>  
>>
>
>
> ---------------------------(end of broadcast)---------------------------
> TIP 8: explain analyze is your friend
>
>

-- 
Dave Cramer
http://www.postgresintl.com
519 939 0336
ICQ#14675561




^ permalink  raw  reply  [nested|flat] 12+ messages in thread

* Re: Performance problem on delete from for 10k rows.  May
  2005-03-15 21:24 Performance problem on delete from for 10k rows. May takes 20 minutes through JDBC interface David Gagnon <dgagnon@siunik.com>
  2005-03-15 23:38 ` Re: Performance problem on delete from for 10k rows. May takes 20 minutes through JDBC interface Michael Fuhr <mike@fuhr.org>
  2005-03-16 13:18   ` Re: Performance problem on delete from for 10k rows.  May David Gagnon <dgagnon@siunik.com>
  2005-03-16 14:06     ` Re: Performance problem on delete from for 10k rows.  May Dave Cramer <pg@fastcrypt.com>
@ 2005-03-16 14:26       ` David Gagnon <dgagnon@siunik.com>
  0 siblings, 0 replies; 12+ messages in thread

From: David Gagnon @ 2005-03-16 14:26 UTC (permalink / raw)
  To: pg@fastcrypt.com; +Cc: Michael Fuhr <mike@fuhr.org>; pgsql-performance

Hi,

I'm using ibatis. But in this particular case the sql statement come 
from a plain ascii file and it's run by the Ibatis ScriptRunner class.  
Beside the fact this class come from ibatis framework it's just plain 
sql connection (I'm I wrong???).  Just to be sure, here is the code from 
the class.  I must say that i run script that contains create table, 
alter table, insert   statements with the same runner. 

If I wrong please tell me .. I like to be wrong when the result is 
eliminating a misunderstanding from my part :-)

Thanks for your help!

/David



 public void runScript(Connection conn, Reader reader)
            throws IOException, SQLException {
        StringBuffer command = null;
        try {
            LineNumberReader lineReader = new LineNumberReader(reader);
            String line = null;
            while ((line = lineReader.readLine()) != null) {
                if (command == null) {
                    command = new StringBuffer();
                }
                String trimmedLine = line.trim();
                if (trimmedLine.startsWith("--")) {
                    println(trimmedLine);
                    if (log.isDebugEnabled()) {
                        log.debug(trimmedLine);
                    }
                } else if (trimmedLine.length() < 1 || 
trimmedLine.startsWith("//")) {
                    //Do nothing
                } else if (trimmedLine.endsWith(";")) {
                    command.append(line.substring(0, 
line.lastIndexOf(";")));
                    command.append(" ");
                    Statement statement = conn.createStatement();

                    println(command);
                    if (log.isDebugEnabled()) {
                        log.debug(command);
                    }

                    boolean hasResults = false;
                    if (stopOnError) {
                        hasResults = statement.execute(command.toString());
                    } else {
                        try {
                            statement.execute(command.toString());
                        } catch (SQLException e) {
                            e.fillInStackTrace();
                            printlnError("Error executing: " + command);
                            printlnError(e);
                        }
                    }

                    if (autoCommit && !conn.getAutoCommit()) {
                        conn.commit();
                    }

                    ResultSet rs = statement.getResultSet();
                    if (hasResults && rs != null) {
                        ResultSetMetaData md = rs.getMetaData();
                        int cols = md.getColumnCount();
                        for (int i = 0; i < cols; i++) {
                            String name = md.getColumnName(i);
                            print(name + "\t");
                        }
                        println("");
                        while (rs.next()) {
                            for (int i = 0; i < cols; i++) {
                                String value = rs.getString(i);
                                print(value + "\t");
                            }
                            println("");
                        }
                    }

                    command = null;
                    try {
                        statement.close();
                    } catch (Exception e) {
                        // Ignore to workaround a bug in Jakarta DBCP
//                        e.printStackTrace();
                    }
                    Thread.yield();
                } else {
                    command.append(line);
                    command.append(" ");
                }
            }
            if (!autoCommit) {
                conn.commit();
            }
        } catch (SQLException e) {
            e.fillInStackTrace();
            printlnError("Error executing: " + command);
            printlnError(e);
            log.error("Error executing: " + command, e);
            throw e;
        } catch (IOException e) {
            e.fillInStackTrace();
            printlnError("Error executing: " + command);
            printlnError(e);
            log.error("Error executing: " + command, e);
            throw e;
        } finally {
            conn.rollback();
            flush();
        }
    }


Dave Cramer wrote:

>
>
> David Gagnon wrote:
>
>> Hi All,
>>
>> I rerun the example with the debug info turned on in postgresl. As 
>> you can see all dependent tables (that as foreign key on table IC) 
>> are emptied before the DELETE FROM IC statement is issued.  For what 
>> I understand the performance problem seem to came from those selects 
>> that point back to IC ( LOG:  statement: SELECT 1 FROM ONLY 
>> "public"."ic" x WHERE "icnum" = $1 FOR UPDATE OF x).  There are 6 of 
>> them.  I don't know where they are comming from.  But if I want to 
>> delete the content of the table (~10k) it may be long to those 6 
>> selects for each deleted rows.  Why are those selects are there ?  
>> Are those select really run on each row deleted?
>
>
> You are using hibernate. Hibernate is generating them to lock the tables.
>
>>
>>
>> I'm running version 7.4.5 on cygwin.  I ran the same delete from 
>> pgAdminIII and I got 945562ms for all the deletes within the same 
>> transaction  .. (so I was wrong saying it took less time in 
>> PgAdminIII... sorry about this).
>>
>> Do you have any idea why those 6 selects are there?
>
>
> Hibernate
>
>>
>> Maybe I can drop indexes before deleting the content of the table.  I 
>> didn't planned to because tables are quite small and it's more 
>> complicated in my environment.  And tell me if I'm wrong but if I 
>> drop indexed do I have to reload all my stored procedure (to reset 
>> the planner related info)??? Remember having read that somewhere.. 
>> (was it in the Postgresql General Bit newletter ...anyway)
>>
>> Thanks for your help I really appréciate it :-)
>>
>> /David
>>
>> LOG:  duration: 144.000 ms
>> LOG:  statement: DELETE FROM YN
>> LOG:  duration: 30.000 ms
>> LOG:  statement: DELETE FROM YO
>> LOG:  statement: SELECT 1 FROM ONLY "public"."yo" x WHERE "yotype" = 
>> $1 AND "yonum" = $2 FOR UPDATE OF x
>> LOG:  statement: SELECT 1 FROM ONLY "public"."yn" x WHERE "ynyotype" 
>> = $1 AND "ynyonum" = $2 FOR UPDATE OF x
>> LOG:  statement: SELECT 1 FROM ONLY "public"."yo" x WHERE "yotype" = 
>> $1 AND "yonum" = $2 FOR UPDATE OF x
>> LOG:  statement: SELECT 1 FROM ONLY "public"."yr" x WHERE "yryotype" 
>> = $1 AND "yryonum" = $2 FOR UPDATE OF x
>> LOG:  duration: 83.000 ms
>> LOG:  connection received: host=127.0.0.1 port=2196
>> LOG:  connection authorized: user=admin database=webCatalog
>> LOG:  statement: set datestyle to 'ISO'; select version(), case when 
>> pg_encoding_to_char(1) = 'SQL_ASCII' then 'UNKNOWN' else 
>> getdatabaseencoding() end;
>> LOG:  duration: 2.000 ms
>> LOG:  statement: set client_encoding = 'UNICODE'
>> LOG:  duration: 0.000 ms
>> LOG:  statement: DELETE FROM IY
>> LOG:  duration: 71.000 ms
>> LOG:  statement: DELETE FROM IA
>> LOG:  duration: 17.000 ms
>> LOG:  statement: DELETE FROM IQ
>> LOG:  duration: 384.000 ms
>> LOG:  statement: DELETE FROM IC
>> LOG:  statement: SELECT 1 FROM ONLY "public"."ic" x WHERE "icnum" = 
>> $1 FOR UPDATE OF x
>> LOG:  statement: SELECT 1 FROM ONLY "public"."iq" x WHERE "iqicnum" = 
>> $1 FOR UPDATE OF x
>> LOG:  statement: SELECT 1 FROM ONLY "public"."ic" x WHERE "icnum" = 
>> $1 FOR UPDATE OF x
>> LOG:  statement: SELECT 1 FROM ONLY "public"."ia" x WHERE "iaicnum" = 
>> $1 FOR UPDATE OF x
>> LOG:  statement: SELECT 1 FROM ONLY "public"."ic" x WHERE "icnum" = 
>> $1 FOR UPDATE OF x
>> LOG:  statement: SELECT 1 FROM ONLY "public"."iy" x WHERE "iyicnumo" 
>> = $1 FOR UPDATE OF x
>> LOG:  statement: SELECT 1 FROM ONLY "public"."ic" x WHERE "icnum" = 
>> $1 FOR UPDATE OF x
>> LOG:  statement: SELECT 1 FROM ONLY "public"."iy" x WHERE "iyicnumr" 
>> = $1 FOR UPDATE OF x
>> LOG:  statement: SELECT 1 FROM ONLY "public"."ic" x WHERE "icnum" = 
>> $1 FOR UPDATE OF x
>> LOG:  statement: SELECT 1 FROM ONLY "public"."il" x WHERE "ilicnum" = 
>> $1 FOR UPDATE OF x
>> LOG:  statement: SELECT 1 FROM ONLY "public"."ic" x WHERE "icnum" = 
>> $1 FOR UPDATE OF x
>> LOG:  statement: SELECT 1 FROM ONLY "public"."bd" x WHERE "bdicnum" = 
>> $1 FOR UPDATE OF x
>> LOG:  duration: 656807.000 msMichael Fuhr wrote:
>>
>>
>>
>>
>>
>> -----------------------
>> DELETE FROM BM;
>> DELETE FROM BD;
>> DELETE FROM BO;
>> DELETE FROM IL;
>> DELETE FROM YR;
>> DELETE FROM YN;
>> DELETE FROM YO;
>> DELETE FROM IY;
>> DELETE FROM IA;
>> DELETE FROM IQ;
>> DELETE FROM IC;
>>
>> Michael Fuhr wrote:
>>
>>> On Tue, Mar 15, 2005 at 04:24:17PM -0500, David Gagnon wrote:
>>>
>>>  
>>>
>>>> Il get this strange problem when deleting rows from a Java 
>>>> program.  Sometime (For what I noticed it's not all the time) the 
>>>> server take almost forever to delete rows from table.
>>>>   
>>>
>>>
>>>
>>> Do other tables have foreign key references to the table you're
>>> deleting from?  If so, are there indexes on the foreign key columns?
>>>
>>> Do you have triggers or rules on the table?
>>>
>>> Have you queried pg_locks during the long-lasting deletes to see
>>> if the deleting transaction is waiting for a lock on something?
>>>
>>>  
>>>
>>>> I rememeber having tried to delete the content of my table (IC) from
>>>> PgAdminIII and I took couples of seconds!!! Not minutes.
>>>>   
>>>
>>>
>>>
>>> How many records did you delete in this case?  If there are foreign
>>> key references, how many records were in the referencing tables?
>>> How repeatable is the disparity in delete time?  A single test case
>>> might have been done under different conditions, so it might not
>>> mean much.  No offense intended, but "I remember" doesn't carry as
>>> much weight as a documented example.
>>>
>>>  
>>>
>>
>>
>> ---------------------------(end of broadcast)---------------------------
>> TIP 8: explain analyze is your friend
>>
>>
>




^ permalink  raw  reply  [nested|flat] 12+ messages in thread

* Re: Performance problem on delete from for 10k rows.  May
  2005-03-15 21:24 Performance problem on delete from for 10k rows. May takes 20 minutes through JDBC interface David Gagnon <dgagnon@siunik.com>
  2005-03-15 23:38 ` Re: Performance problem on delete from for 10k rows. May takes 20 minutes through JDBC interface Michael Fuhr <mike@fuhr.org>
  2005-03-16 13:18   ` Re: Performance problem on delete from for 10k rows.  May David Gagnon <dgagnon@siunik.com>
@ 2005-03-16 14:35     ` Alvaro Herrera <alvherre@dcc.uchile.cl>
  2005-03-16 14:56       ` Re: Performance problem on delete from for 10k rows.  May Dave Cramer <pg@fastcrypt.com>
  2005-03-16 15:13       ` Re: Performance problem on delete from for 10k rows.  May David Gagnon <dgagnon@siunik.com>
  1 sibling, 2 replies; 12+ messages in thread

From: Alvaro Herrera @ 2005-03-16 14:35 UTC (permalink / raw)
  To: David Gagnon <dgagnon@siunik.com>; +Cc: Michael Fuhr <mike@fuhr.org>; pgsql-performance

On Wed, Mar 16, 2005 at 08:18:39AM -0500, David Gagnon wrote:

David,

> I rerun the example with the debug info turned on in postgresl. As you 
> can see all dependent tables (that as foreign key on table IC) are 
> emptied before the DELETE FROM IC statement is issued.  For what I 
> understand the performance problem seem to came from those selects that 
> point back to IC ( LOG:  statement: SELECT 1 FROM ONLY "public"."ic" x 
> WHERE "icnum" = $1 FOR UPDATE OF x).  There are 6 of them.  I don't know 
> where they are comming from.

I think they come from the FK checking code.  Try to run a VACUUM on the
IC table just before you delete from the other tables; that should make
the checking almost instantaneous (assuming the vacuuming actually
empties the table, which would depend on other transactions).

It would be better to be able to use TRUNCATE to do this, but in 8.0 you
can't if the tables have FKs.  8.1 is better on that regard ...

-- 
Alvaro Herrera (<alvherre[@]dcc.uchile.cl>)
"Ninguna manada de bestias tiene una voz tan horrible como la humana" (Orual)



^ permalink  raw  reply  [nested|flat] 12+ messages in thread

* Re: Performance problem on delete from for 10k rows.  May
  2005-03-15 21:24 Performance problem on delete from for 10k rows. May takes 20 minutes through JDBC interface David Gagnon <dgagnon@siunik.com>
  2005-03-15 23:38 ` Re: Performance problem on delete from for 10k rows. May takes 20 minutes through JDBC interface Michael Fuhr <mike@fuhr.org>
  2005-03-16 13:18   ` Re: Performance problem on delete from for 10k rows.  May David Gagnon <dgagnon@siunik.com>
  2005-03-16 14:35     ` Re: Performance problem on delete from for 10k rows.  May Alvaro Herrera <alvherre@dcc.uchile.cl>
@ 2005-03-16 14:56       ` Dave Cramer <pg@fastcrypt.com>
  1 sibling, 0 replies; 12+ messages in thread

From: Dave Cramer @ 2005-03-16 14:56 UTC (permalink / raw)
  To: Alvaro Herrera <alvherre@dcc.uchile.cl>; +Cc: David Gagnon <dgagnon@siunik.com>; Michael Fuhr <mike@fuhr.org>; pgsql-performance

Really? Postgres is generating these queries ???

Dave

Alvaro Herrera wrote:

>On Wed, Mar 16, 2005 at 08:18:39AM -0500, David Gagnon wrote:
>
>David,
>
>  
>
>>I rerun the example with the debug info turned on in postgresl. As you 
>>can see all dependent tables (that as foreign key on table IC) are 
>>emptied before the DELETE FROM IC statement is issued.  For what I 
>>understand the performance problem seem to came from those selects that 
>>point back to IC ( LOG:  statement: SELECT 1 FROM ONLY "public"."ic" x 
>>WHERE "icnum" = $1 FOR UPDATE OF x).  There are 6 of them.  I don't know 
>>where they are comming from.
>>    
>>
>
>I think they come from the FK checking code.  Try to run a VACUUM on the
>IC table just before you delete from the other tables; that should make
>the checking almost instantaneous (assuming the vacuuming actually
>empties the table, which would depend on other transactions).
>
>It would be better to be able to use TRUNCATE to do this, but in 8.0 you
>can't if the tables have FKs.  8.1 is better on that regard ...
>
>  
>

-- 
Dave Cramer
http://www.postgresintl.com
519 939 0336
ICQ#14675561




^ permalink  raw  reply  [nested|flat] 12+ messages in thread

* Re: Performance problem on delete from for 10k rows.  May
  2005-03-15 21:24 Performance problem on delete from for 10k rows. May takes 20 minutes through JDBC interface David Gagnon <dgagnon@siunik.com>
  2005-03-15 23:38 ` Re: Performance problem on delete from for 10k rows. May takes 20 minutes through JDBC interface Michael Fuhr <mike@fuhr.org>
  2005-03-16 13:18   ` Re: Performance problem on delete from for 10k rows.  May David Gagnon <dgagnon@siunik.com>
  2005-03-16 14:35     ` Re: Performance problem on delete from for 10k rows.  May Alvaro Herrera <alvherre@dcc.uchile.cl>
@ 2005-03-16 15:13       ` David Gagnon <dgagnon@siunik.com>
  2005-03-16 16:28         ` Re: Performance problem on delete from for 10k rows.  May Stephan Szabo <sszabo@megazone.bigpanda.com>
  1 sibling, 1 reply; 12+ messages in thread

From: David Gagnon @ 2005-03-16 15:13 UTC (permalink / raw)
  To: Alvaro Herrera <alvherre@dcc.uchile.cl>; +Cc: Michael Fuhr <mike@fuhr.org>; pgsql-performance

Hi

>>I rerun the example with the debug info turned on in postgresl. As you 
>>can see all dependent tables (that as foreign key on table IC) are 
>>emptied before the DELETE FROM IC statement is issued.  For what I 
>>understand the performance problem seem to came from those selects that 
>>point back to IC ( LOG:  statement: SELECT 1 FROM ONLY "public"."ic" x 
>>WHERE "icnum" = $1 FOR UPDATE OF x).  There are 6 of them.  I don't know 
>>where they are comming from.
>>    
>>
>
>I think they come from the FK checking code.  Try to run a VACUUM on the
>IC table just before you delete from the other tables; that should make
>the checking almost instantaneous (assuming the vacuuming actually
>empties the table, which would depend on other transactions).
>  
>
I'll try to vaccum first before I start the delete to see if it change 
something.

There is probably a good reason why but I don't understant why in a 
foreign key check it need to check the date it points to.

You delete a row from table IC and do a check for integrity on tables 
that have foreign keys on IC (make sense).  But why checking back IC?  
I'm pretty sure there is a good reason but it seems to have a big 
performance impact... In this case.  It means it's not really feasable 
to empty the content of a schema.  The table has only 10k .. with a huge 
table it's not feasible just because the checks on itselft!

Is someone can explain why there is this extra check?  Is that can be 
fixed or improved?

Thanks for your help

/David





LOG:  duration: 144.000 ms
LOG:  statement: DELETE FROM YN
LOG:  duration: 30.000 ms
LOG:  statement: DELETE FROM YO
LOG:  statement: SELECT 1 FROM ONLY "public"."yo" x WHERE "yotype" = $1 
AND "yonum" = $2 FOR UPDATE OF x
LOG:  statement: SELECT 1 FROM ONLY "public"."yn" x WHERE "ynyotype" = 
$1 AND "ynyonum" = $2 FOR UPDATE OF x
LOG:  statement: SELECT 1 FROM ONLY "public"."yo" x WHERE "yotype" = $1 
AND "yonum" = $2 FOR UPDATE OF x
LOG:  statement: SELECT 1 FROM ONLY "public"."yr" x WHERE "yryotype" = 
$1 AND "yryonum" = $2 FOR UPDATE OF x
LOG:  duration: 83.000 ms
LOG:  connection received: host=127.0.0.1 port=2196
LOG:  connection authorized: user=admin database=webCatalog
LOG:  statement: set datestyle to 'ISO'; select version(), case when 
pg_encoding_to_char(1) = 'SQL_ASCII' then 'UNKNOWN' else 
getdatabaseencoding() end;
LOG:  duration: 2.000 ms
LOG:  statement: set client_encoding = 'UNICODE'
LOG:  duration: 0.000 ms
LOG:  statement: DELETE FROM IY
LOG:  duration: 71.000 ms
LOG:  statement: DELETE FROM IA
LOG:  duration: 17.000 ms
LOG:  statement: DELETE FROM IQ
LOG:  duration: 384.000 ms
LOG:  statement: DELETE FROM IC
LOG:  statement: SELECT 1 FROM ONLY "public"."ic" x WHERE "icnum" = $1 
FOR UPDATE OF x
LOG:  statement: SELECT 1 FROM ONLY "public"."iq" x WHERE "iqicnum" = $1 
FOR UPDATE OF x
LOG:  statement: SELECT 1 FROM ONLY "public"."ic" x WHERE "icnum" = $1 
FOR UPDATE OF x
LOG:  statement: SELECT 1 FROM ONLY "public"."ia" x WHERE "iaicnum" = $1 
FOR UPDATE OF x
LOG:  statement: SELECT 1 FROM ONLY "public"."ic" x WHERE "icnum" = $1 
FOR UPDATE OF x
LOG:  statement: SELECT 1 FROM ONLY "public"."iy" x WHERE "iyicnumo" = 
$1 FOR UPDATE OF x
LOG:  statement: SELECT 1 FROM ONLY "public"."ic" x WHERE "icnum" = $1 
FOR UPDATE OF x
LOG:  statement: SELECT 1 FROM ONLY "public"."iy" x WHERE "iyicnumr" = 
$1 FOR UPDATE OF x
LOG:  statement: SELECT 1 FROM ONLY "public"."ic" x WHERE "icnum" = $1 
FOR UPDATE OF x
LOG:  statement: SELECT 1 FROM ONLY "public"."il" x WHERE "ilicnum" = $1 
FOR UPDATE OF x
LOG:  statement: SELECT 1 FROM ONLY "public"."ic" x WHERE "icnum" = $1 
FOR UPDATE OF x
LOG:  statement: SELECT 1 FROM ONLY "public"."bd" x WHERE "bdicnum" = $1 
FOR UPDATE OF x
LOG:  duration: 656807.000 msMichael Fuhr wrote:




>It would be better to be able to use TRUNCATE to do this, but in 8.0 you
>can't if the tables have FKs.  8.1 is better on that regard ...
>
>  
>




^ permalink  raw  reply  [nested|flat] 12+ messages in thread

* Re: Performance problem on delete from for 10k rows.  May
  2005-03-15 21:24 Performance problem on delete from for 10k rows. May takes 20 minutes through JDBC interface David Gagnon <dgagnon@siunik.com>
  2005-03-15 23:38 ` Re: Performance problem on delete from for 10k rows. May takes 20 minutes through JDBC interface Michael Fuhr <mike@fuhr.org>
  2005-03-16 13:18   ` Re: Performance problem on delete from for 10k rows.  May David Gagnon <dgagnon@siunik.com>
  2005-03-16 14:35     ` Re: Performance problem on delete from for 10k rows.  May Alvaro Herrera <alvherre@dcc.uchile.cl>
  2005-03-16 15:13       ` Re: Performance problem on delete from for 10k rows.  May David Gagnon <dgagnon@siunik.com>
@ 2005-03-16 16:28         ` Stephan Szabo <sszabo@megazone.bigpanda.com>
  2005-03-16 17:02           ` Re: Performance problem on delete from for 10k rows.  May David Gagnon <dgagnon@siunik.com>
  0 siblings, 1 reply; 12+ messages in thread

From: Stephan Szabo @ 2005-03-16 16:28 UTC (permalink / raw)
  To: David Gagnon <dgagnon@siunik.com>; +Cc: Alvaro Herrera <alvherre@dcc.uchile.cl>; Michael Fuhr <mike@fuhr.org>; pgsql-performance

On Wed, 16 Mar 2005, David Gagnon wrote:

> Hi
>
> >>I rerun the example with the debug info turned on in postgresl. As you
> >>can see all dependent tables (that as foreign key on table IC) are
> >>emptied before the DELETE FROM IC statement is issued.  For what I
> >>understand the performance problem seem to came from those selects that
> >>point back to IC ( LOG:  statement: SELECT 1 FROM ONLY "public"."ic" x
> >>WHERE "icnum" = $1 FOR UPDATE OF x).  There are 6 of them.  I don't know
> >>where they are comming from.
> >>
> >>
> >
> >I think they come from the FK checking code.  Try to run a VACUUM on the
> >IC table just before you delete from the other tables; that should make
> >the checking almost instantaneous (assuming the vacuuming actually
> >empties the table, which would depend on other transactions).
> >
> >
> I'll try to vaccum first before I start the delete to see if it change
> something.
>
> There is probably a good reason why but I don't understant why in a
> foreign key check it need to check the date it points to.
>
> You delete a row from table IC and do a check for integrity on tables
> that have foreign keys on IC (make sense).  But why checking back IC?

Because in the general case there might be another row which satisfies the
constraint added between the delete and the check.




^ permalink  raw  reply  [nested|flat] 12+ messages in thread

* Re: Performance problem on delete from for 10k rows.  May
  2005-03-15 21:24 Performance problem on delete from for 10k rows. May takes 20 minutes through JDBC interface David Gagnon <dgagnon@siunik.com>
  2005-03-15 23:38 ` Re: Performance problem on delete from for 10k rows. May takes 20 minutes through JDBC interface Michael Fuhr <mike@fuhr.org>
  2005-03-16 13:18   ` Re: Performance problem on delete from for 10k rows.  May David Gagnon <dgagnon@siunik.com>
  2005-03-16 14:35     ` Re: Performance problem on delete from for 10k rows.  May Alvaro Herrera <alvherre@dcc.uchile.cl>
  2005-03-16 15:13       ` Re: Performance problem on delete from for 10k rows.  May David Gagnon <dgagnon@siunik.com>
  2005-03-16 16:28         ` Re: Performance problem on delete from for 10k rows.  May Stephan Szabo <sszabo@megazone.bigpanda.com>
@ 2005-03-16 17:02           ` David Gagnon <dgagnon@siunik.com>
  2005-03-16 20:35             ` Re: Performance problem on delete from for 10k rows.  May Stephan Szabo <sszabo@megazone.bigpanda.com>
  0 siblings, 1 reply; 12+ messages in thread

From: David Gagnon @ 2005-03-16 17:02 UTC (permalink / raw)
  To: Stephan Szabo <sszabo@megazone.bigpanda.com>; +Cc: Alvaro Herrera <alvherre@dcc.uchile.cl>; Michael Fuhr <mike@fuhr.org>; pgsql-performance



Stephan Szabo wrote:

>On Wed, 16 Mar 2005, David Gagnon wrote:
>
>  
>
>>Hi
>>
>>    
>>
>>>>I rerun the example with the debug info turned on in postgresl. As you
>>>>can see all dependent tables (that as foreign key on table IC) are
>>>>emptied before the DELETE FROM IC statement is issued.  For what I
>>>>understand the performance problem seem to came from those selects that
>>>>point back to IC ( LOG:  statement: SELECT 1 FROM ONLY "public"."ic" x
>>>>WHERE "icnum" = $1 FOR UPDATE OF x).  There are 6 of them.  I don't know
>>>>where they are comming from.
>>>>
>>>>
>>>>        
>>>>
>>>I think they come from the FK checking code.  Try to run a VACUUM on the
>>>IC table just before you delete from the other tables; that should make
>>>the checking almost instantaneous (assuming the vacuuming actually
>>>empties the table, which would depend on other transactions).
>>>
>>>
>>>      
>>>
>>I'll try to vaccum first before I start the delete to see if it change
>>something.
>>
>>There is probably a good reason why but I don't understant why in a
>>foreign key check it need to check the date it points to.
>>
>>You delete a row from table IC and do a check for integrity on tables
>>that have foreign keys on IC (make sense).  But why checking back IC?
>>    
>>
>
>Because in the general case there might be another row which satisfies the
>constraint added between the delete and the check.
>
>  
>
So it's means if I want to reset the shema with DELETE FROM Table 
statemnets  I must first drop indexes, delete the data and then recreate 
indexes and reload stored procedure.

Or I can suspend the foreign key check in the db right.  I saw something 
on this.  Is that possible to do this from the JDBC interface?

Is there any other options I can consider ?

Thanks for your help!
/David



^ permalink  raw  reply  [nested|flat] 12+ messages in thread

* Re: Performance problem on delete from for 10k rows.  May
  2005-03-15 21:24 Performance problem on delete from for 10k rows. May takes 20 minutes through JDBC interface David Gagnon <dgagnon@siunik.com>
  2005-03-15 23:38 ` Re: Performance problem on delete from for 10k rows. May takes 20 minutes through JDBC interface Michael Fuhr <mike@fuhr.org>
  2005-03-16 13:18   ` Re: Performance problem on delete from for 10k rows.  May David Gagnon <dgagnon@siunik.com>
  2005-03-16 14:35     ` Re: Performance problem on delete from for 10k rows.  May Alvaro Herrera <alvherre@dcc.uchile.cl>
  2005-03-16 15:13       ` Re: Performance problem on delete from for 10k rows.  May David Gagnon <dgagnon@siunik.com>
  2005-03-16 16:28         ` Re: Performance problem on delete from for 10k rows.  May Stephan Szabo <sszabo@megazone.bigpanda.com>
  2005-03-16 17:02           ` Re: Performance problem on delete from for 10k rows.  May David Gagnon <dgagnon@siunik.com>
@ 2005-03-16 20:35             ` Stephan Szabo <sszabo@megazone.bigpanda.com>
  0 siblings, 0 replies; 12+ messages in thread

From: Stephan Szabo @ 2005-03-16 20:35 UTC (permalink / raw)
  To: David Gagnon <dgagnon@siunik.com>; +Cc: Alvaro Herrera <alvherre@dcc.uchile.cl>; Michael Fuhr <mike@fuhr.org>; pgsql-performance

On Wed, 16 Mar 2005, David Gagnon wrote:

>
>
> Stephan Szabo wrote:
>
> >On Wed, 16 Mar 2005, David Gagnon wrote:
> >
> >
> >
> >>Hi
> >>
> >>
> >>
> >>>>I rerun the example with the debug info turned on in postgresl. As you
> >>>>can see all dependent tables (that as foreign key on table IC) are
> >>>>emptied before the DELETE FROM IC statement is issued.  For what I
> >>>>understand the performance problem seem to came from those selects that
> >>>>point back to IC ( LOG:  statement: SELECT 1 FROM ONLY "public"."ic" x
> >>>>WHERE "icnum" = $1 FOR UPDATE OF x).  There are 6 of them.  I don't know
> >>>>where they are comming from.
> >>>>
> >>>>
> >>>>
> >>>>
> >>>I think they come from the FK checking code.  Try to run a VACUUM on the
> >>>IC table just before you delete from the other tables; that should make
> >>>the checking almost instantaneous (assuming the vacuuming actually
> >>>empties the table, which would depend on other transactions).
> >>>
> >>>
> >>>
> >>>
> >>I'll try to vaccum first before I start the delete to see if it change
> >>something.
> >>
> >>There is probably a good reason why but I don't understant why in a
> >>foreign key check it need to check the date it points to.
> >>
> >>You delete a row from table IC and do a check for integrity on tables
> >>that have foreign keys on IC (make sense).  But why checking back IC?
> >>
> >>
> >
> >Because in the general case there might be another row which satisfies the
> >constraint added between the delete and the check.
> >
> >
> >
> So it's means if I want to reset the shema with DELETE FROM Table
> statemnets  I must first drop indexes, delete the data and then recreate
> indexes and reload stored procedure.
>
> Or I can suspend the foreign key check in the db right.  I saw something
> on this.  Is that possible to do this from the JDBC interface?

I think you can remove the constraints and re-add them after which should
hopefully be fast (a vacuum on the tables after the delete and before the
add might help, but I'm not sure).  You could potentially defer the
constraint if it were deferrable, but I don't think that would help any.



^ permalink  raw  reply  [nested|flat] 12+ messages in thread

* Re: Performance problem on delete from for 10k rows.
  2005-03-15 21:24 Performance problem on delete from for 10k rows. May takes 20 minutes through JDBC interface David Gagnon <dgagnon@siunik.com>
@ 2005-03-15 23:50 ` Rod Taylor <pg@rbt.ca>
  1 sibling, 0 replies; 12+ messages in thread

From: Rod Taylor @ 2005-03-15 23:50 UTC (permalink / raw)
  To: David Gagnon <dgagnon@siunik.com>; +Cc: pgsql-performance

>  I get this problem on my dev (Windows/7.4/Cygwin) environment.  But now 
> I see that it's also have this problem on my production env.  Yes I 
> tought I was maybe just a cygwin/Windows problem .. apparently not :-((((

Care to try again with logging enabled on the PostgreSQL side within the
development environment?

log_statement = true
log_duration = true
log_connections = on

Then run it via Java and from pgAdminIII and send us the two log
snippets as attachments?

Thanks.
-- 




^ permalink  raw  reply  [nested|flat] 12+ messages in thread


end of thread, other threads:[~2005-03-16 20:35 UTC | newest]

Thread overview: 12+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2005-03-15 21:24 Performance problem on delete from for 10k rows. May takes 20 minutes through JDBC interface David Gagnon <dgagnon@siunik.com>
2005-03-15 23:38 ` Michael Fuhr <mike@fuhr.org>
2005-03-16 13:18   ` Re: Performance problem on delete from for 10k rows.  May David Gagnon <dgagnon@siunik.com>
2005-03-16 14:06     ` Re: Performance problem on delete from for 10k rows.  May Dave Cramer <pg@fastcrypt.com>
2005-03-16 14:26       ` Re: Performance problem on delete from for 10k rows.  May David Gagnon <dgagnon@siunik.com>
2005-03-16 14:35     ` Re: Performance problem on delete from for 10k rows.  May Alvaro Herrera <alvherre@dcc.uchile.cl>
2005-03-16 14:56       ` Re: Performance problem on delete from for 10k rows.  May Dave Cramer <pg@fastcrypt.com>
2005-03-16 15:13       ` Re: Performance problem on delete from for 10k rows.  May David Gagnon <dgagnon@siunik.com>
2005-03-16 16:28         ` Re: Performance problem on delete from for 10k rows.  May Stephan Szabo <sszabo@megazone.bigpanda.com>
2005-03-16 17:02           ` Re: Performance problem on delete from for 10k rows.  May David Gagnon <dgagnon@siunik.com>
2005-03-16 20:35             ` Re: Performance problem on delete from for 10k rows.  May Stephan Szabo <sszabo@megazone.bigpanda.com>
2005-03-15 23:50 ` Re: Performance problem on delete from for 10k rows. Rod Taylor <pg@rbt.ca>

This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox