sql - postgres - How to efficiently find if a matching pair exists for the given tables

Question

items
id     | name                           | group
-----------------------------------------------------------
1      | /foo/                          | app1
2      | /foo/1                         | app1
3      | /bar/2                         | app1
4      | /foo/abc/def                   | app1
5      | /foo3/                         | app2
6      | /bar3/                         | app2


mapping
id_a | id_b 
--------------
1    | 2
3    | 4

mapping.id_a and mapping.id_b have foreign key references to item.id

I need to find if association exists between id_a and id_b in mapping table for a given input 'name' and 'group' using gino sqlalchemy.

Example:

1. for input '/foo/', '/foo/1', 'app1' --> match success.
2. for input '/bar/2', '/foo/abc/def', 'app1' --> match success.
3. for input '/foo3/', '/bar3/', 'app2' --> match fails.

question from:https://stackoverflow.com/questions/65843813/postgres-how-to-efficiently-find-if-a-matching-pair-exists-for-the-given-table

Answer 1

That would be trivial with a join:

SELECT TRUE
FROM items AS i1
   JOIN mapping AS m ON i1.id = m.id_a
   JOIN items AS i2 ON m.id_b = i2.id
WHERE i1.name = '/foo/'
  AND i2.name = '/foo/1' AND i2.group = 'app1';

If this returns a row, there is a match.

For good performance, create these tow indexes (if they don't already exist):

CREATE INDEX ON item (name, group);
CREATE INDEX ON mapping (id_b);

Then you should get an efficient nested loop join.