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
>
