[SQL] DELETE using an outer join

#1

Hi,

(this is not a real world problem, just something I'm playing around with).

Lately I had some queries of the form:

select t.*
from some_table t
where t.id not in (select some_id from some_other_table);

I could improve the performance of them drastically by changing the NOT NULL into an outer join:

select t.*
from some_table t
left join some_other_table ot on ot.id = t.id
where ot.id is null;

Now I was wondering if a DELETE statement could be rewritten with the same "strategy":

Something like:

delete from some_table
where id not in (select min(id)
from some_table
group by col1, col2
having count(*) > 1);

(It's the usual - at least for me - "get rid of duplicates" statement)

The DELETE .. USING seems to only allow inner joins because it requires the join to be done in the WHERE clause.
So I can't think of a way to turn that NOT IN from the DELETE into an outer join with a derived table.

Am I right that this kind of transformation is not possible or am I missing something?

Regards
Thomas

--
Sent via pgsql-sql mailing list (pgsql-sql (AT) postgresql (DOT) org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-sql

#2

On Thu, Jul 19, 2012 at 4:43 PM, Thomas Kellerer <spam_eater (AT) gmx (DOT) net> wrote:

Quote:

delete from some_table
where id not in (select min(id)
from some_table
group by col1, col2
having count(*) > 1);

(It's the usual - at least for me - "get rid of duplicates" statement)

If you want to remove duplicates you can do it this way.

DELETE FROM some_table USING some_table AS s
WHERE
some_table.col1 = s.col1 AND
some_table.col2 = s.col2 AND
some_table.id < s.id;

The query plan should be better than one with the sub query and NOT IN.

ps. May be this example is worth to append to the documentation?

--
Sergey Konoplev

a database architect, software developer at PostgreSQL-Consulting.com
http://www.postgresql-consulting.com

Jabber: gray.ru (AT) gmail (DOT) com Skype: gray-hemp Phone: +79160686204

--
Sent via pgsql-sql mailing list (pgsql-sql (AT) postgresql (DOT) org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-sql

#3

Thomas Kellerer <spam_eater (AT) gmx (DOT) net> writes:

Quote:

Lately I had some queries of the form:

select t.*
from some_table t
where t.id not in (select some_id from some_other_table);

I could improve the performance of them drastically by changing the NOT NULL into an outer join:

select t.*
from some_table t
left join some_other_table ot on ot.id = t.id
where ot.id is null;

If you're using a reasonably recent version of PG, replacing the NOT IN
by a NOT EXISTS test should also help.

Quote:

Now I was wondering if a DELETE statement could be rewritten with the same "strategy":

Not at the moment. There have been discussions of allowing the same
table name to be respecified in USING, but there are complications.

regards, tom lane

--
Sent via pgsql-sql mailing list (pgsql-sql (AT) postgresql (DOT) org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-sql

#4

Tom Lane, 19.07.2012 16:52:

Quote:

If you're using a reasonably recent version of PG, replacing the NOT IN
by a NOT EXISTS test should also help.

Thanks. I wasn't aware of that (and the NOT EXISTS does indeed produce the same plan as the OUTER JOIN solution)

Quote:

Now I was wondering if a DELETE statement could be rewritten with the same "strategy":

Not at the moment. There have been discussions of allowing the same
table name to be respecified in USING, but there are complications.

Thanks as well. It's not a big deal for me. I was just curious if I missed something.

Regards
Thomas

--
Sent via pgsql-sql mailing list (pgsql-sql (AT) postgresql (DOT) org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-sql

#5

On Thu, Jul 19, 2012 at 6:52 PM, Tom Lane <tgl (AT) sss (DOT) pgh.pa.us> wrote:

Quote:

Now I was wondering if a DELETE statement could be rewritten with the same "strategy":

Not at the moment. There have been discussions of allowing the same
table name to be respecified in USING, but there are complications.

However it works.

DELETE FROM some_table USING some_table AS s
WHERE
some_table.col1 = s.col1 AND
some_table.col2 = s.col2 AND
some_table.id < s.id;

--
Sergey Konoplev

a database and software architect
http://www.linkedin.com/in/grayhemp

Jabber: gray.ru (AT) gmail (DOT) com Skype: gray-hemp Phone: +79160686204

--
Sent via pgsql-sql mailing list (pgsql-sql (AT) postgresql (DOT) org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-sql

#6

Sergey Konoplev, 20.07.2012 10:21:

Quote:

On Thu, Jul 19, 2012 at 6:52 PM, Tom Lane <tgl (AT) sss (DOT) pgh.pa.us> wrote:
Now I was wondering if a DELETE statement could be rewritten with the same "strategy":

Not at the moment. There have been discussions of allowing the same
table name to be respecified in USING, but there are complications.

However it works.

DELETE FROM some_table USING some_table AS s
WHERE
some_table.col1 = s.col1 AND
some_table.col2 = s.col2 AND
some_table.id < s.id;

But that's not an outer join

--
Sent via pgsql-sql mailing list (pgsql-sql (AT) postgresql (DOT) org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-sql

#7

On Fri, Jul 20, 2012 at 2:27 PM, Thomas Kellerer <spam_eater (AT) gmx (DOT) net> wrote:

Quote:

Now I was wondering if a DELETE statement could be rewritten with the
same "strategy":

Not at the moment. There have been discussions of allowing the same
table name to be respecified in USING, but there are complications.

However it works.

DELETE FROM some_table USING some_table AS s
WHERE
some_table.col1 = s.col1 AND
some_table.col2 = s.col2 AND
some_table.id < s.id;

But that's not an outer join

Oh, yes. I just lost the discussion line. Sorry.

--
Sergey Konoplev

a database architect, software developer at PostgreSQL-Consulting.com
http://www.postgresql-consulting.com

Jabber: gray.ru (AT) gmail (DOT) com Skype: gray-hemp Phone: +79160686204

--
Sent via pgsql-sql mailing list (pgsql-sql (AT) postgresql (DOT) org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-sql

#8

Sergey Konoplev <gray.ru (AT) gmail (DOT) com> writes:

Quote:

On Thu, Jul 19, 2012 at 6:52 PM, Tom Lane <tgl (AT) sss (DOT) pgh.pa.us> wrote:
Now I was wondering if a DELETE statement could be rewritten with the same "strategy":

Not at the moment. There have been discussions of allowing the same
table name to be respecified in USING, but there are complications.

However it works.

DELETE FROM some_table USING some_table AS s
WHERE
some_table.col1 = s.col1 AND
some_table.col2 = s.col2 AND
some_table.id < s.id;

No, that's a self-join, which isn't what the OP wanted. You can make it
work if you self-join on the primary key and then left join to the other
table, but that's pretty klugy and inefficient.

What was being discussed is allowing people to write directly

DELETE FROM some_table USING some_table LEFT JOIN other_table ...

where the respecification of the table in USING would be understood
to mean the target table. Right now this is an error case because
of duplicate table aliases.

regards, tom lane

--
Sent via pgsql-sql mailing list (pgsql-sql (AT) postgresql (DOT) org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-sql

#9

On Fri, Jul 20, 2012 at 5:51 PM, Tom Lane <tgl (AT) sss (DOT) pgh.pa.us> wrote:

Quote:

DELETE FROM some_table USING some_table AS s
WHERE
some_table.col1 = s.col1 AND
some_table.col2 = s.col2 AND
some_table.id < s.id;

No, that's a self-join, which isn't what the OP wanted. You can make it
work if you self-join on the primary key and then left join to the other
table, but that's pretty klugy and inefficient.

What was being discussed is allowing people to write directly

DELETE FROM some_table USING some_table LEFT JOIN other_table ...

where the respecification of the table in USING would be understood
to mean the target table. Right now this is an error case because
of duplicate table aliases.

Yes, the OP has already pointed me to it. Thank you for your explanation.

--
Sergey Konoplev

a database architect, software developer at PostgreSQL-Consulting.com
http://www.postgresql-consulting.com

Jabber: gray.ru (AT) gmail (DOT) com Skype: gray-hemp Phone: +79160686204

--
Sent via pgsql-sql mailing list (pgsql-sql (AT) postgresql (DOT) org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-sql

Thread Tools
Show Printable Version Email this Page
Display Modes
Linear Mode Switch to Hybrid Mode Switch to Threaded Mode

[SQL] DELETE using an outer join

mailing.database.pgsql-sql mailing.database.pgsql-sql

[SQL] DELETE using an outer join - 07-19-2012 , 07:43 AM

Re: [SQL] DELETE using an outer join - 07-19-2012 , 09:33 AM

Re: [SQL] DELETE using an outer join - 07-19-2012 , 09:52 AM

Re: [SQL] DELETE using an outer join - 07-20-2012 , 03:17 AM

Re: [SQL] DELETE using an outer join - 07-20-2012 , 03:21 AM

Re: [SQL] DELETE using an outer join - 07-20-2012 , 05:27 AM

Re: [SQL] DELETE using an outer join - 07-20-2012 , 05:47 AM

Re: [SQL] DELETE using an outer join - 07-20-2012 , 08:51 AM

Re: [SQL] DELETE using an outer join - 07-20-2012 , 09:13 AM