Re: Indexed views?

I meant indexes on fields from multiple tables, or equivaltently
indexes on a view spanning fields from multiple tables.

For example, consider the view
CREATE VIEW vw_lot AS
 SELECT productid, lotid, parentlotid, lottype, lotname, productname
   FROM lot
NATURAL JOIN product;

where productname is in the product table, and lotname in the lot
table. I would be interested in creating an index such as

CREATE INDEX ix_vw_lot ON vw_lot (lotname, productname);

for performance reasons, since both my lot and product tables are very
large. The index would be enough to cover 90% of the queries against
lot the lot and inventory tables.

-Tiago

On Mon, 06 Sep 2004 10:17:24 -0700, Joshua D. Drake
<jd(at)commandprompt(dot)com> wrote:
>
>
> Tiago Wright wrote:
>
> >Are there any plans to support indexed views, or cross-table indexes,
> >or any form of "materialized views" in postgresql? How complex would
> >the implementation be? Indexed views are sometimes the best way to
> >improve the performance of complex queries.
> >
> >
> The planner will use an index across multiple tables, even when called
> from a view as long as the comparing
> tables are of the same type...
>
> As far as materialized views, you can use triggers to create them.
>
> Sincerely,
>
> Joshua D. Drake
>
> >-Tiago
> >
> >---------------------------(end of broadcast)---------------------------
> >TIP 4: Don't 'kill -9' the postmaster
> >
> >
>
> --
> Command Prompt, Inc., home of Mammoth PostgreSQL - S/ODBC and S/JDBC
> Postgresql support, programming shared hosting and dedicated hosting.
> +1-503-667-4564 - jd(at)commandprompt(dot)com - http://www.commandprompt.com
> PostgreSQL Replicator -- production quality replication for PostgreSQL
>
>