2

I have a table with two important columns: entity and enabled. The idea is that entities are "unique groups" where all rows with the same entity belong to the same "item".

I wish to find all rows, which belong to an entity that contains no enabled row. So entities where all lines are "disabled".

There are no foreign keys defined. entity is just an arbitrary identifier but can also be null where they belong to the main group. So there is no main element to point to.

The following query works:

SELECT * 
FROM public.my_list
WHERE id not in (
    SELECT l.id
    FROM public.my_list as l
    INNER JOIN public.my_list as ol 
    ON ol.entity=l.entity 
    AND ol.enabled=true
);

This uses an inner join inside a subquery, which feels like a lot of redundancy. Could this be optimized? Considering the table has close to a million entries, and the returned set will be sizeable (like few 100 000).

(I know the select by itself is terrible, but this is further filtered in the customer dataset, and leads to a delete.)

5
  • 1
    Don't use NOT IN. select * from my_list ml where not exists(select * from my_list ml2 where ml.entity = ml2.entity and ml2.enabled = true) or select * from (select id, entity, first_value(enabled) over (partition by entity order by enabled desc) as enabled from my_list) q where enabled = false Mar 24 at 15:22
  • This looks like the dreaded entity-attribute-value design, so there is probably no way to query it efficiently. Mar 24 at 15:45
  • a join is only noticeable faster, of the table is big, else ou will not notice it
    – nbk
    Mar 25 at 1:00
  • @LaurenzAlbe just for my own understanding (I can't redesign the whole company's application by myself...), it is used as a rudimentary "version control"/"audit", each update instead gives a new entry and makes the old entry "inactive". That's different from eav right?
    – paul23
    Mar 25 at 10:48
  • That doesn't sound like classical EAV, so that may be fine - I don't know the data model. It was the strange self-join that made me wonder about the data model. Mar 25 at 11:03

1 Answer 1

3

Your current solution performs terribly for two reasons:

  1. NOT IN is the worst option when paired with a subquery.
    Plus, it introduces traps for null values:

    • Your subquery excludes all rows with a null value in one of the columns (entity, enabled). Thereby including the entity in the result.
    • Also, if the subquery returns any null values the entity is also not excluded, i.e. included.

    Don't go there.

  2. Your subquery joins every row with every row (incl. itself) in the set of rows with the same entity, basically a Cartesian product scaling with O(N²). With more than a few rows for the same entity, performance deteriorates quickly.

Instead, use one of the more efficient and predictable query techniques. See:

With NOT EXISTS, similar to what Andrey commented:

SELECT *
FROM   my_list l
WHERE  NOT EXISTS (
   SELECT FROM my_list l2
   WHERE  l2.entity = l.entity
   AND    l2.enabled
   );

Or with LEFT JOIN ... IS NULL:

SELECT l.*
FROM   my_list l
LEFT   JOIN my_list l2 ON l2.entity = l.entity
                      AND l2.enabled
WHERE  l2.entity IS NULL;

If Laurenz is right, and this is an EAV design, you are in a tight spot to begin with.

Foreign key constraints enforce referential integrity, but have no influence on the performance of this SELECT query.

11
  • "NOT IN is the worst option when paired with a subquery" why? Apart from the null problem, it should optimize the same as a NOT EXISTS Mar 25 at 17:23
  • @Charlieface No, there is a logical difference, that (among other things) leads to worse performance. Mar 25 at 19:01
  • I fail to see how there can be a logical difference if neither side is nullable. Mar 25 at 21:22
  • But there is if either side is nullable. Detailed rationale here. Mar 25 at 21:58
  • I know that, that wasn't what I asked. I asked if there were any other issues. You said "NOT IN is the worst option when paired with a subquery. Plus, it introduces traps for null values." which implies there is a difference aside from the null issue. Please explain. Mar 25 at 22:00

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.