Re: indexes and tables

Hi Amit,

Have you maybe tested what David J has suggested?

In other words it is:
• Create 10 tables with their columns and indexes on each
t1(a1,a2,a3)
t2(b1,b2,b3)
.
.
.
t10(n1,n2,n3)
•Create VIEW with union all 10 tables
SELECT a1 AS f1, a2 as f2, a3 as f3 from t1
Union
SELECT b1 as f1, b2 as f2, b3 as f3 from t2
UNION
(next 8 tables)

Select (star) from myview where f1 (equal) 'something'
(sorry, i just found out i do not have "star" and "equal" keys on my
wp7)

Should use index on each table for its corresponding column..( a1 to n1)

Sent from my Windows Phone From: amit sehas
Sent: 19 December 2011 06:17
To: pgsql-general(at)postgresql(dot)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(at)yahoo(dot)com> wrote:

> From: David Johnston <polobo(at)yahoo(dot)com>
> Subject: RE: [GENERAL] indexes and tables
> To: "'amit sehas'" <cun23(at)yahoo(dot)com>, pgsql-general(at)postgresql(dot)org
> Date: Sunday, December 18, 2011, 1:59 PM
> -----Original Message-----
> From: pgsql-general-owner(at)postgresql(dot)org
> [mailto:pgsql-general-owner(at)postgresql(dot)org]
> On Behalf Of amit sehas
> Sent: Thursday, December 15, 2011 9:22 PM
> To: pgsql-general(at)postgresql(dot)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.
>
>
>