Efficient deletions?
От | Bertrand Petit |
---|---|
Тема | Efficient deletions? |
Дата | |
Msg-id | 20030802022152.A86089@memo.frmug.org обсуждение исходный текст |
Ответы |
Re: Efficient deletions?
|
Список | pgsql-sql |
Hello, This is my first message to this list. I'm curently trying to improve my postgres skillsm I've assigned myself a project involving a database whose dataset is quite larger than those I had to use until now. I'm curently facing a trouble with the DELETE query: I need to delete from a table a set of rows choosen by a sub-select. The query curently looks like this: DELETE FROM foo WHERE (col1, col2) IN (SELECT...) It seems from the output of ANALYSE that the sub-query is executed for each foo rows. That's really not efficient as the set returned by the sub-select is constant. I had a similar trouble with a SELECT query but it was eavily solved by placing one of the sub-select in the FROM part of the query. Unfortunately it look that can't be done this way with DELETE. Is there a way to instruct the planner that this sub-query is constant over the time of the DELETE execution? Or is there another more efficent way to express this? I conducted tests with a sub-select returning about 20,000 rows and a foo table of 370,000 rows, out of patience I had to interrupt the query after five hours. That worries me as this query is part of an update process that could be fired several times a day. -- %!PS 297.6 420.9 translate 90 rotate 0 setgray gsave 0 1 1{pop 0 180 moveto 100 180 170 100 170 -10 curveto 180 -9 180 -9 190 -10 curveto 190 100 100 180 0 180 curveto fill 180 rotate}for grestore/Bookman-LightItalic findfont 240 scalefont setfont -151.536392 -63.7998886 moveto (bp)show showpage
В списке pgsql-sql по дате отправления: