Thread: indexes and tables

indexes and tables

From
amit sehas
Date:
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
tothe 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

Re: indexes and tables

From
"David Johnston"
Date:
-----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.



Re: indexes and tables

From
"David Johnston"
Date:
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.
>
>
>


Re: indexes and tables

From
amit sehas
Date:
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.
>
>
>

Re: indexes and tables

From
Misa Simic
Date:
Hi Amit,

Have you maybe tested what David J has suggested?

In other words it is:
=E2=80=A2 Create 10 tables with their columns and indexes on each
t1(a1,a2,a3)
t2(b1,b2,b3)
=2E
=2E
=2E
t10(n1,n2,n3)
=E2=80=A2Create 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@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.
=46rom 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.=C2=A0 Thus, the direct answer to your
> question is that you cannot
> have a single index covering all 10 tables.=C2=A0 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 =3D
> '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.=C2=A0 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.
>
>
>

--=20
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

Re: indexes and tables

From
Alan Hodgson
Date:
On Sunday, December 18, 2011 04:00:14 PM amit sehas wrote:
> 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...)...
>

The views are not materialized. But the SQL they execute does make use of
existing indexes.

> 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...

Inheritance will not help you with columns with different names. And indexes
aren't inherited anyway.

Re: indexes and tables

From
Misa Simic
Date:
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@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.
>
>
>

--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general