Обсуждение: Indexes not allowed on (read-only) views: Why?
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
On Fri, Sep 16, 2011 at 4:47 PM, Stefan Keller <sfkeller@gmail.com> 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? > > 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 implementation across databases is different. in postgres, views are essentially macros, thus there is no data to index. merlin
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
Hi Craig, Thank you for the explanations. 2011/9/17 Craig Ringer <ringerc@ringerc.id.au>: > 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@ringerc.id.au>: > 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 >
Craig Ringer, 17.09.2011 02:28: > 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. AFAIK: "indexed view" is simply Microsoft's term for "materialized view" Thomas