Re: indexes and tables

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