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