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@xxxxxxxxx> wrote: > From: David Johnston <polobo@xxxxxxxxx> > Subject: RE: indexes and tables > To: "'amit sehas'" <cun23@xxxxxxxxx>, pgsql-general@xxxxxxxxxxxxxx > Date: Sunday, December 18, 2011, 1:59 PM > -----Original Message----- > From: pgsql-general-owner@xxxxxxxxxxxxxx > [mailto:pgsql-general-owner@xxxxxxxxxxxxxx] > On Behalf Of amit sehas > Sent: Thursday, December 15, 2011 9:22 PM > To: pgsql-general@xxxxxxxxxxxxxx > Subject: 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@xxxxxxxxxxxxxx) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
