Thread: Performance problem on delete from for 10k rows. May takes 20 minutes through JDBC interface
Performance problem on delete from for 10k rows. May takes 20 minutes through JDBC interface
From
David Gagnon
Date:
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
Re: Performance problem on delete from for 10k rows. May takes 20 minutes through JDBC interface
From
Michael Fuhr
Date:
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/
> 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. --
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. > > >
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
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
>>
>>
>
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)
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
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 ... > > >
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.
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
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.