Creating index on a view?
От | Net Virtual Mailing Lists |
---|---|
Тема | Creating index on a view? |
Дата | |
Msg-id | 20041124080048.5557@mail.net-virtual.com обсуждение исходный текст |
Ответы |
Re: Creating index on a view?
|
Список | 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
В списке pgsql-general по дате отправления: