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

On 09/17/2011 05:47 AM, Stefan Keller wrote:
> 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?

It's not so much that it's not allowed, as that it's not implemented and 
not very practical for non-materialized views.

A normal (non-materialized) view doesn't have any data of its own, it 
pulls it from one or more other tables on the fly during query 
execution. The execution of a view is kind of similar to a set-returning 
function or a subquery, almost as if you'd substituted the view 
definition into the original query.

That means that the view will use any indexes on the original table(s), 
but there isn't really even an opportunity to check for indexes on the 
view its self because the view's definition is effectively substituted 
into the query. If the view definition is complex enough that it does a 
lot of work where indexes on the original table(s) don't help, that work 
has to be done every time.

It only really makes sense to have indexes on materialized views. 
PostgreSQL doesn't have any native support for materialized views, so it 
doesn't support indexes on views.

What you *CAN* do is use triggers to maintain your own materialized 
views as regular tables, and have indexes on the tables you maintain 
using triggers. This is widely discussed on the mailing list and isn't 
hard to do, though it's tricky to make updates perform well with some 
kinds of materialized view query.

>
> 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

It sounds like they probably use a materialized view, possibly stored as 
an index-oriented table. That'd be a cool thing to support, but if done 
that way would require TWO new major features PostgreSQL doesn't have.

--
Craig Ringer