Indexes not allowed on (read-only) views: Why?

From: Stefan Keller <sfkeller(at)gmail(dot)com>
To: pgsql-general <pgsql-general(at)postgresql(dot)org>
Subject: Indexes not allowed on (read-only) views: Why?
Date: 2011-09-16 21:47:02
Message-ID: CAFcOn2_bjLCgP_LDov8HMoDR6d309Nc5XJGgPUYbDWViZ_gRfQ@mail.gmail.com
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-general

A (read-only) view should behave like a table, right?

> CREATE INDEX t1_idx ON t1 (rem);
ERROR: »v1« not a table
SQL state: 42809

=> Why should'nt it be possible to create indexes on views in PG?

An index on a view can speed up access to the tuples underlying. And
"indexed views" could be a method of storing the result set of the
view in the database, thereby reducing the overhead of dynamically
building the result set. An "indexed view" should automatically adapt
modifications made to the data in the base tables. So, there is some
overhead here, but this is ok when speed in retrieving results
outweighs the cost...

And there is no practical reason since SQL Server can do it! See
"Creating Indexes on Views"
http://msdn.microsoft.com/en-us/library/aa933124%28v=sql.80%29.aspx

Stefan

Responses

Browse pgsql-general by date

  From Date Subject
Next Message Merlin Moncure 2011-09-16 21:48:47 Re: Indexes not allowed on (read-only) views: Why?
Previous Message Rich Shepard 2011-09-16 21:42:45 Re: Apparent Problem With NULL in Restoring pg_dump