pg.ddx.io pgsql-sql@postgresql.org mailing list archive
help / color / mirror / Atom feedEfficient deletions?
2+ messages / 2 participants
[nested] [flat]
* Efficient deletions?
@ 2003-08-02 00:21 Bertrand Petit <pgsql-sql@phoe.frmug.org>
0 siblings, 1 reply; 2+ messages in thread
From: Bertrand Petit @ 2003-08-02 00:21 UTC (permalink / raw)
To: 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
^ permalink raw reply [nested|flat] 2+ messages in thread
* Re: Efficient deletions?
@ 2003-08-02 07:21 A.Bhuvaneswaran <bhuvan@symonds.net>
parent: Bertrand Petit <pgsql-sql@phoe.frmug.org>
0 siblings, 0 replies; 2+ messages in thread
From: A.Bhuvaneswaran @ 2003-08-02 07:21 UTC (permalink / raw)
To: Bertrand Petit <pgsql-sql@phoe.frmug.org>; +Cc: pgsql-sql
> DELETE FROM foo WHERE (col1, col2) IN (SELECT...)
DELETE FROM foo WHERE col1 = sub_select_table.col1 and col2 =
sub_select_table.col2;
regards,
bhuvaneswaran
^ permalink raw reply [nested|flat] 2+ messages in thread
end of thread, other threads:[~2003-08-02 07:21 UTC | newest]
Thread overview: 2+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2003-08-02 00:21 Efficient deletions? Bertrand Petit <pgsql-sql@phoe.frmug.org>
2003-08-02 07:21 ` A.Bhuvaneswaran <bhuvan@symonds.net>
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