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