Re: indexes and tables

I've shown you how to make a proper VIEW that will accomplish what you want
(i.e., 9  "UNION ALL" between 10 tables).  Yes, the VIEW is simply a
re-write RULE but the indexes on the corresponding tables are still very
much real and "should" be used to when you make use of the VIEW over one of
the tables.  It may be that such a VIEW cannot use the indexes but unless
you or someone else tells me it cannot then I am going to assume that it
does (I do not have time to test it myself at the moment).

You need to provide more details on what you are logically trying to
accomplish, and the restrictions you face, and forget about "indexes" for
the time being.  If you can provide a functional, self-contained,
description then maybe someone will be able to provide performance
improvement suggestions.  At the moment it is still unclear what your actual
requirements are.

I am unsure if/how inheritance would fit in but I do know that it requires
that you have the same field name(s) in ALL child tables.  Column names are
fundamental (i.e., non-dynamic) and so if you truly need to deal with
multiple names for the "same column" you will need to deal with multiple
views/queries.

David J.

-----Original Message-----
From: amit sehas [mailto:cun23@yahoo.com]
Sent: Sunday, December 18, 2011 7:00 PM
To: pgsql-general@postgresql.org; David Johnston
Subject: RE: [GENERAL] indexes and tables

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 creating such a physical backing
would be inordinately difficult to keep updated given the arbitrary original
query that was utilized 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 concept as 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.
>
>
>