Search Postgresql Archives

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

[Date Prev][Date Next][Thread Prev][Thread Next][Date Index][Thread Index]

 



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



[Date Prev][Date Next][Thread Prev][Thread Next][Date Index][Thread Index]
[Index of Archives]     [Postgresql Jobs]     [Postgresql Admin]     [Postgresql Performance]     [Linux Clusters]     [PHP Home]     [PHP on Windows]     [Kernel Newbies]     [PHP Classes]     [PHP Books]     [PHP Databases]     [Postgresql & PHP]     [Yosemite]
  Powered by Linux