Обсуждение: View index question

I have several schemes, each containing the same two tables.

The two tables have the following structure:

create table dav_samplesetattribute
(
key int8,
poresulttype int4 not null,
calculationerrorcode int4,
causedbypo int4,
numberofsamples int4,
sampletype int4,
samplesize int4,
physicalunit int4,
physicalvalue int4,
constraint dav_samplesetattribute_pk primary key (key)
);

create table dav_sample
(
key int8,
samplenumber int4,
samplestate int4 not null,
sampleerrorcode int4 not null,
limitlevel int4,
variablesamplesize int4,
integersample int8,
realsample double precision,
charsample varchar(10485780),
constraint sample_pk primary key(key, samplenumber),
constraint sample_fk foreign key (key) references dav_samplesetattribute(key)
);

Some of the fields are meant to get a not unique index (for example the field
integersample).

Each of the tables in each scheme will store a lot rows. That is also why I
decided to split the tables over the schemes.


Now (for queries) I want to unite all the tables of all schemes via one view.
Therefore I first declared one view uniting all 'dav_samplesetattribute' tables
of all schemes and a second view uniting all 'dav_sample' tables.

The view definitions use the UNION-clause to unite the equal tables from all
schemes. One field is added to the view representing the name of the according
scheme. My first question is, if there exists a possibility to add an index on
the views new field, representing the name of the scheme?

After having declared these two views, I declare a third view, uniting the
former two views via OUTER JOIN.
Now, if I execute queries on this view, none of my indexes seem to be used.
Queries on the other two views, use my indexes though. Are indexes not used on
view queries, if a view is based on other views?

I'd also welcome any common tips on views-index usage (known problems for
example).


Thanks Andreas.

-------------------------------------------------
This mail sent through IMP: http://horde.org/imp/

u15074 writes:

> Each of the tables in each scheme will store a lot rows. That is also why I
> decided to split the tables over the schemes.

This is absolutely pointless.  Splitting a table over several schemas will
have no effect on performance or storage (except possibly making
everything slower because the planner gets confused).

> My first question is, if there exists a possibility to add an index on
> the views new field, representing the name of the scheme?

No, you cannot index a view.

> Now, if I execute queries on this view, none of my indexes seem to be
> used.  Queries on the other two views, use my indexes though. Are
> indexes not used on view queries, if a view is based on other views?

Queries on views may make use of indexes like any other query.  To examine
index usage, please read the following:

http://www.postgresql.org/docs/7.3/static/indexes-examine.html

--
Peter Eisentraut   peter_e@gmx.net

Zitat von Peter Eisentraut <peter_e@gmx.net>:

> u15074 writes:
>
> > Each of the tables in each scheme will store a lot rows. That is also why
> I
> > decided to split the tables over the schemes.
>
> This is absolutely pointless.  Splitting a table over several schemas will
> have no effect on performance or storage (except possibly making
> everything slower because the planner gets confused).
>
Yes, you are right. But I also splitted the tables for logical reasons.
Maybe I will change this. Than I also won't need the views.

-------------------------------------------------
This mail sent through IMP: http://horde.org/imp/