Query scenario

DROP TABLE inner_tab_2;
DROP TABLE inner_tab_1;
DROP TABLE outer_tab CASCADE;

CREATE TABLE outer_tab (outer_key INTEGER,
                        CONSTRAINT outer_tab_pk PRIMARY KEY (outer_key));

CREATE TABLE inner_tab_1(key_to INTEGER, key_from INTEGER, type INTEGER,
                        CONSTRAINT inner_tab_1_pk PRIMARY KEY (type, key_to, key_from),
            CONSTRAINT outer_from_fk FOREIGN KEY (key_from)
            REFERENCES outer_tab (outer_key) MATCH SIMPLE
            ON UPDATE CASCADE ON DELETE CASCADE,
            CONSTRAINT outer_to_fk FOREIGN KEY (key_to)
            REFERENCES outer_tab (outer_key) MATCH SIMPLE
            ON UPDATE CASCADE ON DELETE CASCADE);

CREATE TABLE inner_tab_2(outer_key INTEGER, group_id INTEGER,
            CONSTRAINT outer_fk FOREIGN KEY (outer_key)
            REFERENCES outer_tab (outer_key) MATCH SIMPLE
            ON UPDATE CASCADE ON DELETE CASCADE);

DO $$DECLARE rnd_outer_key integer; rnd_group_key integer;
BEGIN
    FOR idx IN 0..5000 LOOP
    INSERT INTO outer_tab (outer_key) VALUES (idx);
    END LOOP;

    FOR idx IN 0..16000 LOOP
        SELECT INTO rnd_outer_key trunc(random() * 5000);
        SELECT INTO rnd_group_key trunc(random() * 8);
    INSERT INTO inner_tab_2 (outer_key, group_id) VALUES (rnd_outer_key, rnd_group_key);
    END LOOP;

    FOR idx IN 0..1000 LOOP
    INSERT INTO inner_tab_1 (key_to, key_from, type) VALUES (idx, idx, 1);
    END LOOP;

END$$;



ANALYSE inner_tab_2;
ANALYSE inner_tab_1;
ANALYSE outer_tab;


\timing
--First the slow query with a group_id that is not in the inner_tab_2 table.
SELECT outer_tab.outer_key
  FROM outer_tab
 WHERE outer_tab.outer_key IN (
           SELECT inner_tab_1.key_to
             FROM inner_tab_2
            INNER JOIN inner_tab_1 ON (inner_tab_2.outer_key = inner_tab_1.key_from AND type = 2)
            WHERE outer_tab.outer_key = inner_tab_1.key_to AND inner_tab_2.group_id = 9
            );
\echo Slow execution group id not in table
\echo ===
\echo
--Fast because it can drive of an index scan of inner_tab_1
SELECT outer_tab.outer_key
  FROM outer_tab
 WHERE outer_tab.outer_key IN (
           SELECT inner_tab_1.key_to
             FROM inner_tab_1
            INNER JOIN inner_tab_2 ON (inner_tab_2.outer_key = inner_tab_1.key_from AND type = 2)
            WHERE outer_tab.outer_key = inner_tab_1.key_to AND inner_tab_2.group_id = 9
            );
\echo Fast execution group id not in table but tables in from and join swapped
\echo ===
\echo

--Now fast execution because the group id is in inner_tab_2
SELECT outer_tab.outer_key
  FROM outer_tab
 WHERE outer_tab.outer_key IN (
           SELECT inner_tab_1.key_to
             FROM inner_tab_2
            INNER JOIN inner_tab_1 ON (inner_tab_2.outer_key = inner_tab_1.key_from AND type = 2)
            WHERE outer_tab.outer_key = inner_tab_1.key_to AND inner_tab_2.group_id = 1
            );
\echo Fast execution group id in table
\echo ===
\echo
\timing