Using LEFT JOIN instead of NOT IN in PostgreSQL

Question

I'm debugging a possible performance bug in django-reversion (a django library). The issue that I'm experiencing is that every time I run django-reversion's createinitialrevisions, my DB will take huge amount of time to process whatever is going on.

I have enabled the Performance Insights in RDS and I see that the query that is killing my DB looks like this:

SELECT "table_a"."id"
FROM "table_a"
WHERE NOT (CAST("table_a"."id" as text) IN (
        SELECT U0."object_id"
        FROM "reversion_version" U0
        WHERE (U0."content_type_id" = 49 AND U0."db" = 'default')
))

If I understand correctly what I read here https://explainextended.com/2009/09/16/not-in-vs-not-exists-vs-left-join-is-null-postgresql/ it turns out that PostgreSQL isn't able to optimize the same way a NOT IN than it would a LEFT JOIN. That's why I decided to rewrite this query and see whether it would take the same amount of time to run.

This is the result after rewriting it:

SELECT "table_a"."id"
FROM "table_a"
LEFT JOIN 
        "reversion_version" U0
ON U0."object_id" = "table_a"."id"::text
WHERE U0."object_id" IS NULL AND U0."content_type_id" = 49 AND U0."db" = 'default'

I must have done something wrong, because I'm getting different results. My query (the rewritten one) isn't returning anything at all.

What did I miss?

Your version of Postgres please and table definitions (`CREATE TABLE` statements) for `table_a` and `reversion_version`. — Erwin Brandstetter, Mar 16 '19 at 01:23
If there is no matching record in the right-hand table (U0) then U0."content_type_id" and U0."db" will contain NULL — barrowc, Mar 16 '19 at 01:26
A warning: the article you found was written 10 years ago for Postgres 8.4, that's ancient. While some basics are still true, much isn't any more. Unless you are using that ancient version of Postgres. Which you shouldn't. — Erwin Brandstetter, Mar 16 '19 at 01:47
Correct me if I'm wrong, but shouldn't you use something like a `MINUS` operator here? — MetaColon, Mar 16 '19 at 01:54
Learn what left join on returns: inner join on rows plus unmatched left table rows extended by nulls. Always know what inner join you want as part of a left join. An inner join on or where that requires a right table column to be not null after a left join on removes any rows extended by nulls, ie leaves only inner join on rows, ie "turns outer join into inner join". You have that. Besides asking for rows with U0.object_id both not null & null. PS Please in code questions give a [mcve]. — philipxy, Mar 16 '19 at 02:09
Note: `CAST("table_a"."id" as text) IN (` you are attempting an (ANTI-) join on a textfield<-->CASTed((int?)field). Don't expect this to be fast, indexes cannot be used here. Also: there is an extra`)` in your first query. It will fail (very fast!) — wildplasser, Mar 16 '19 at 13:28

Erwin Brandstetter · Accepted Answer · 2019-03-16T01:39:34.667

The correctly rewritten query needs the WHERE conditions of the former subquery as join conditions to the LEFT JOIN like:

SELECT table_a.id
FROM   table_a
LEFT   JOIN  reversion_version U0 ON U0.object_id = table_a.id::text
                                 AND U0.content_type_id = 49
                                 AND U0.db = 'default'
WHERE  U0.object_id IS NULL;

The way you tried was a logical contradiction: it would ask for rows in table_a with no matching row in reversion_version and then impose additional conditions on the non-existent rows. That can never return any rows.

It must be the other way round: find rows in table_a with no matching row in reversion_version that would fulfill said conditions. Hence move those conditions from the WHERE clause to the join clause of the LEFT JOIN. Subtle, but fundamental difference.

See:

There might be more to be said about performance, but not without the necessary details of your setup ...

Using LEFT JOIN instead of NOT IN in PostgreSQL

1 Answers1