Long-running DELETE...WHERE...
От | jboes@nexcerpt.com (Jeff Boes) |
---|---|
Тема | Long-running DELETE...WHERE... |
Дата | |
Msg-id | d40a65a1.0201141027.6b15bc97@posting.google.com обсуждение исходный текст |
Ответы |
Re: Long-running DELETE...WHERE...
|
Список | pgsql-sql |
Why would a delete involving a subselect run so much longer than the individual delete commands? My situation: table A has 200,000 rows. I've made up a temporary table which holds the single-valued primary key for 80,000 rows which I want to delete. DELETE FROM a WHERE id IN (select ID from tmp LIMIT 800); runs for several minutes. But if I do \o tmpfile \t SELECT 'DELETE FROM a WHERE id = ' || id || ';' from tmp limit 800; \o \i tmpfile this completes in about 15 seconds, or 1/50 of the time for the single-statement delete above. In trying to optimize this process, I disabled all the relational integrity triggers (foreign keys) involving the table, and then I dropped all the indexes EXCEPT that of the primary key. All the experiments were done within a single transaction using BEGIN. The database version is 7.1.3, and the table was vacuumed very recently.
В списке pgsql-sql по дате отправления: