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