Creating index on a view? - Mailing list pgsql-general

Hello,

First, let me apologize for my flurry of emails as of late... I'm working
on something which seems particularly difficult (at least to me)...

My question is regarding creating an index on a view, or perhaps another
way to accomplish this.  For example:

CREATE TABLE table1 (
   table1_id SERIAL,
   table2_id INTEGER
);

CREATE TABLE table2 (
  table2_id INTEGER,
  table2_desc VARCHAR,
  table3_id   INTEGER[]
);

This allows me, simply, to do queries like:

SELECT a.table1_id, b.table2_id, b.table2_desc FROM table1 a,table2 b
WHERE a.table2_id = b.table2_id;

But now I have another table with data which needs to be converted into
the format of table1:

CREATE TABLE table3 (
  table3_id INTEGER,
  table3_desc VARCHAR
);

CREATE VIEW view1 (table1_id, table2_id) AS
  SELECT a.table3_id, b.table2_id
  FROM table3 a,
       table2 b
  WHERE a.table3_id = ANY (b.table3_id)
;


With this method I can execute the exact same query against view1 as I do
table1, but when I need to do something like:

SELECT * FROM view1 WHERE table2_id=1;

.. What I really want to do is:

CREATE INDEX view1_table2_id_idx ON view1(table2_id);

.. I can't figure out how to make such a query use an index
efficiently....  What I am trying to do is sort-of do an on-the-fly data
conversion of table3 to table1, for purposes of doing a "UNION" on the
two of them.....  My join is between several more tables than this
example....

Any thoughts on how to accomplish something like this?...

Thanks as always!

- Greg