How to reduce the cost of this query?

Question

I have two tables, table1 and table2. table2 contains permissions for records in table1, i. e. one row in table1 can have multiple corresponding rows in table2.

Now I need to write a SQL statement that would retrieve those table1 records, which

have a particular primary key (table1.ID) and
do not have any of the specified permissions.

I came up with this query:

SELECT table1.id
FROM TABLE1 table1
WHERE table1.ID IN (<ID_List>) AND
(<Excluded_permission_List>) NOT IN (
    Select PERMISSION_NAME
    from TABLE2 table2 
    where table2.perm_owner_id = table1.id
);

where

<ID_List> is the list of all table1 primary keys to search for, and
<Excluded_permission_List> the list of all exluded permissions (i. e. if a table1 record has any of the permissions contained in <Excluded_permission_List>, it should not appear in the results).

Example:

SELECT table1.id
FROM TABLE1 table1
WHERE table1.ID IN (1, 2, 3, 4, 5, 6, 7, 8, 9, 10, 11, 12) AND
('SUPERUSER-1', 'MODERATOR-2') NOT IN (
    Select PERMISSION_NAME
    from TABLE2 table2 
    where table2.perm_owner_id = table1.id
);

This query is supposed to return those table1 records, which

have the IDs 1, 2, 3, 4, 5, 6, 7, 8, 9, 10, 11, 12 and
do not have neither the permission SUPERUSER-1, nor MODERATOR-2.

This query works, but according to EXPLAIN PLAN, the cost is 38 (see below).

How can reduce the cost of this query?

If I were you, I wouldn't focus on the cost. Does the query run quickly enough for your purposes? If not, how long does it take and how long should it take? — Boneist, Mar 25 '19 at 10:14
It runs quickly on my machine and the above case with 12 IDs is a bit worse than the worst possible case in production database (AFAIK, in real database the maximum number of IDs is 9). But if there is an easy way to make it faster, I'd like to do it now before it is shipped to production. — Glory to Russia, Mar 25 '19 at 10:21
Please learn how to post he execution plan as text (and not as an image) as described e.g. [here](https://stackoverflow.com/a/34975420/4808122). IMO if you want a very fast query (cost about 4 i.e. two index and two atable access) you need to redesing your permission table to a flat table with one row per ID and a column for each privilege. — Marmite Bomber, Mar 25 '19 at 10:31

score 1 · Answer 1 · answered Mar 25 '19 at 10:24

You have a correlated subquery on exists-clause as predicate which executes for each filtered parent row. Try this. CBO may rewrite predicate to anti-join (hash join or nested loops)

SELECT table1.id
FROM TABLE1 table1
WHERE table1.ID IN (1, 2, 3, 4, 5, 6, 7, 8, 9, 10, 11, 12) AND
(('SUPERUSER-1', table1.id), ('MODERATOR-2', table1.id)) NOT IN (
    select table2.permission_name, table2.perm_owner_id
    from TABLE2 table2 
    where table2.perm_owner_id is not null and table2.permission_name is not null
);

How to reduce the cost of this query?

1 Answers1