CREATE TABLE test1 (
  id SERIAL PRIMARY KEY,
  name TEXT NOT NULL
);

INSERT INTO test1 (name) VALUES ('test1_1');
INSERT INTO test1 (name) VALUES ('test1_2');
INSERT INTO test1 (name) VALUES ('test1_3');

CREATE TABLE test2 (
  id SERIAL PRIMARY KEY,
  type TEXT NOT NULL CHECK (type IN ('a','b','c')),
  test1_id INTEGER REFERENCES test1
);

INSERT INTO test2 (type,test1_id) VALUES ('a',1);
INSERT INTO test2 (type,test1_id) VALUES ('a',2);
INSERT INTO test2 (type,test1_id) VALUES ('a',3);
INSERT INTO test2 (type,test1_id) VALUES ('b',1);
INSERT INTO test2 (type,test1_id) VALUES ('b',2);
INSERT INTO test2 (type,test1_id) VALUES ('b',3);
INSERT INTO test2 (type,test1_id) VALUES ('c',1);
INSERT INTO test2 (type,test1_id) VALUES ('c',2);
INSERT INTO test2 (type,test1_id) VALUES ('c',3);

CREATE OR REPLACE VIEW test AS
  SELECT test2.*
  FROM test2
  LEFT JOIN test2 AS t2 ON
    test2.type IN ('c','b') AND
    t2.type = 'a';

SELECT * from test WHERE type = 'a';

CREATE INDEX index_a ON test2 (id) WHERE type = 'a';
CREATE INDEX index_b ON test2 (id) WHERE type = 'b';
CREATE INDEX index_c ON test2 (id) WHERE type = 'c';

SET enable_seqscan TO OFF;

SELECT * from test WHERE type = 'a';

