Hi Craig, Thank you for the explanations. 2011/9/17 Craig Ringer <ringerc@xxxxxxxxxxxxx>: > 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. Just to understand you saying "two new major features": 1. native support for materialized views, 2. indexed (materialized) views, right? What do you mean by "index-oriented table"? (see my other thread about "Index containing data values instead of pointers to data?") Stefan 2011/9/17 Craig Ringer <ringerc@xxxxxxxxxxxxx>: > 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 > -- Sent via pgsql-general mailing list (pgsql-general@xxxxxxxxxxxxxx) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
