Re: indexes and tables - Mailing list pgsql-general

Yes i was trying to determine how to make a View work in this situation.
From reading the details on PostgreSQL Views are not persistent, ie they
are just a SQL query short hand rather than actually creating any physical entity backing it (i would imagine that
creatingsuch a physical backing would be inordinately difficult to keep updated given the arbitrary original query that
wasutilized to define it...)... 

Is there some way inheritence can play a part in this such as

a) define a base table with 3 fields and place indexes on each one of the
   3 fields

b) define the 10 types in which we just override the name of each one of the 3 fields (i do not know if there is such a
conceptas overriding the name of a field...) perhaps a computed field which depends upon the field inherited... 

that is as close as i could think of how to solve such as issue...but i am
not sure how inheritance works in POstgres...

any help is greatly appreciated...

thanks

--- On Sun, 12/18/11, David Johnston <polobo@yahoo.com> wrote:

> From: David Johnston <polobo@yahoo.com>
> Subject: RE: [GENERAL] indexes and tables
> To: "'amit sehas'" <cun23@yahoo.com>, pgsql-general@postgresql.org
> Date: Sunday, December 18, 2011, 1:59 PM
> -----Original Message-----
> From: pgsql-general-owner@postgresql.org
> [mailto:pgsql-general-owner@postgresql.org]
> On Behalf Of amit sehas
> Sent: Thursday, December 15, 2011 9:22 PM
> To: pgsql-general@postgresql.org
> Subject: [GENERAL] indexes and tables
>
> HI,
>
> we have a schema related question. We have 10 types of
> resource records.
> Each one of these resource records has 3 fields
> (attributes) (lets say f1,
> f2, f3)...these fields have similar meaning to the
> corresponding 3 fields in
> each resource record although they be named slightly
> differently in each
> resource record type.
>
> We want to view these 10 resource record types uniformly
> with respect to
> these 3 fields and place indexes across all the types on
> each one of these
> fields, so that all resource records regardless of type
> would appear in
> these indexes.
>
> We want these indexes to be stored persistently in the
> physical database
> because we have 10 million objects and it is not practical
> to keep
> recreating the indexes every time we need them.
>
> Is there some way to accomplish this in PostgreSQL?
>
> thanks
>
> ---------------------------------------------------------
>
> Your description is incomplete or otherwise unclear but
> I'll take a stab.
>
> First assumption: "10 types of resource records" means you
> have 10 tables
> each containing three fields that have similar
> semantics/meanings.
>
> The main thing to keep in mind that an index is always
> associated with a
> single table.  Thus, the direct answer to your
> question is that you cannot
> have a single index covering all 10 tables.  You will
> want to create indexes
> on each table and then create a VIEW that encapsulates each
> of the 10 tables
> with a "UNION".
>
> CREATE VIEW consolidated_attributes AS
> SELECT f1, f2, f3 FROM table1
> UNION ALL
> SELECT f1, f2, f3 FROM table1
> Etc....;
>
> If you then issue: [ SELECT * FROM consolidated_attributes
> WHERE f1 =
> 'something' ] each table will have its corresponding f1
> index scanned.
>
> Hopefully this helps/answers your question but if not I
> think you need to
> provide additional detail as to what you want to accomplish
> at a higher
> level and not focus on whether you can create a specific
> kind of index.  As
> index are non-logical in nature if you can provide the
> logical structure of
> your schema, along with the kinds of queries you wish to
> write, more
> specific advice can be given regarding optimization.
>
> David J.
>
>
>