Thread: Creating index on a view?
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
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/
Some time ago, an excellent tutorial on materialized views with PostgreSQL was pointed at this address.... http://jonathangardner.net/PostgreSQL/materialized_views/matviews.html Patrick > -------------------------------------------------------------------------- ----------------- > Patrick Fiche > email : patrick.fiche@aqsacom.com > tél : 01 69 29 36 18 > -------------------------------------------------------------------------- ----------------- > > > -----Original Message----- From: pgsql-general-owner@postgresql.org [mailto:pgsql-general-owner@postgresql.org]On Behalf Of Peter Eisentraut Sent: mercredi 24 novembre 2004 11:15 To: Net Virtual Mailing Lists Cc: pgsql-general@postgresql.org Subject: Re: [GENERAL] Creating index on a view? 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/ ---------------------------(end of broadcast)--------------------------- TIP 1: subscribe and unsubscribe commands go to majordomo@postgresql.org Protected by Polesoft Lockspam http://www.polesoft.com/refer.html
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/
>
Net Virtual Mailing Lists wrote:
>
> 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.
[snip]
> SELECT b.table3_id, b.table3_desc FROM table3 b WHERE b.table3_id = 7;
I don't think you want to use an array here. If you were to split your
tables:
table2 (t2_id, t2_desc);
table3 (t3_id, t3_desc);
table2_and_3 (t2_id, t3_id);
Then, you should find everything a lot easier.
Try not to use arrays as a set.
--
Richard Huxton
Archonet Ltd