Re: Performance problem on delete from for 10k rows. May - Mailing list 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
>>
>>
>