Re: Creating index on a view?

The problem in my case is that the view does a join between table2 and
table3 and I want to do a select on a value from table2.  So at the point
the expansion happens, I am actually doing a query on a column that does
not exist in table3 - it only exists in table2.

Given what you said, perhaps a better way of me explaining it would be
without the use of a view, since it seems irrelevant:



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

CREATE TABLE table3 (
  table3_id INTEGER,
  table3_desc VARCHAR
);


What I need is an "indirect index" (for lack of a better phrase) that
allows me to do:

SELECT b.table3_id, b.table3_desc FROM table2 a, table 3 b WHERE
a.table2_id = 4 AND b.table3_id = ANY (a.table3_id);

.. in the above example, the "4" is the variable component in the
query...  THe table3_id in table2 has the value of '{7}' - so when I do
the above select, it is actually retrieving records from table3 where
table3_id is equal to 7.

.. assuming tables where table2 is very small and table3 is very large it
does not seem yield good performance by creating an index on
table3(table3_id).  (In fact, I can't get it to use the index at all in
this case no matter what I do).  To be more precise, if table3 has 24,000
rows and selecting table2_id of "4" using the above query 800 rows would
be returned, it always does a sequential scan on table3.  Comparing this
with doing:

SELECT b.table3_id, b.table3_desc FROM table3 b WHERE b.table3_id = 7;

.. when there is an index on table3(table3_id) - an index scan is
performed instead of a table scan.


As for why I want to do this it is because there is another table
(table1) where the schema is different from table3 and I'm trying to use
table2 as a means of doing an "on the fly conversion" (via a view) so
that a "SELECT ... FROM table1 UNION select ... FROM view1" will work.

I thought about the materialized view, but I'm concerned that with the
number of records I would essentially be doubling my disk usage.

I hope this clarifies... I think I'm confused just trying to explain it!

- Greg

>Net Virtual Mailing Lists wrote:
>> My question is regarding creating an index on a view, or perhaps
>> another way to accomplish this.
>
>Views are just macro expansions of queries (in a manner of speaking).
>To make queries on views use indexes, you create the indexes on the
>underlying tables in the same way as if you had typed in the expanded
>view query yourself.  (In your example, you'd just need the usual
>indexes on the primary keys.)
>
>If what you want is that the creation of an index on a view
>automatically materializes that view (don't other database systems do
>that?), then you will have to implement that manually in PostgreSQL,
>with triggers and a bunch of code.
>
>--
>Peter Eisentraut
>http://developer.postgresql.org/~petere/
>