Linking tables and indexes - Mailing list pgsql-general

Hi!


I'm not sure about the English terminology for that so I'm sorry if I made a
mistake on the subject and on this message.  I hope de code explains it better
if I missed it :-)

I have some tables that will have N:M relationships between themselves and for
that I created some linking tables such as:

     CREATE TABLE ged.documents_clients_cis (
         ci_id INT NOT NULL,
                 CONSTRAINT ci_id_exists
                 FOREIGN KEY (ci_id)
                 REFERENCES ged.cis (id) ,
         document_client_id INT NOT NULL,
                 CONSTRAINT document_client_id_exists
                 FOREIGN KEY (document_client_id)
                 REFERENCES ged.documents_clients (id),
         PRIMARY KEY (ci_id, document_client_id)
     );


Thinking about how PostgreSQL is able to use composed indices should I create
the reverse index ("CREATE INDEX something ON ged.documents_clients_cis
(document_client_id, ci_id)") or I'd only be wasting disk and processing?

The query can be done from either side (i.e. I might know either ci_id or
document_client_id only).


Thanks for your attention,
--
Jorge Godoy      <jgodoy@gmail.com>