Обсуждение: Linking tables and indexes
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>
Jorge Godoy wrote: > 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). If you're in 8.1 or better, I'd suggest defining only two indexes, one on (ci_id) and other on (document_client_id), and let the system deal with mixing them using the bitmap scan technique when appropriate. OTOH since the columns are probably not separately unique, you'll need the primary key anyway, in which case leave the PK alone and create another index on (document_client_id). -- Alvaro Herrera http://www.CommandPrompt.com/ The PostgreSQL Company - Command Prompt, Inc.
Alvaro Herrera <alvherre@commandprompt.com> writes: > If you're in 8.1 or better, I'd suggest defining only two indexes, one I'm on 8.1. Waiting for SuSE to update to 8.2... ;-) > on (ci_id) and other on (document_client_id), and let the system deal > with mixing them using the bitmap scan technique when appropriate. I thought about that but then I'd loose the UNIQUE constraint on this set. I've also thought about creating a third index to specify the UNIQUE constraint but ... > OTOH since the columns are probably not separately unique, you'll need > the primary key anyway, in which case leave the PK alone and create > another index on (document_client_id). ... I haven't thought on this and it is much better :-) I played with some possibilities and I forgot mixing a composed index with a simple one... :-) This is what I went with. Thanks! -- Jorge Godoy <jgodoy@gmail.com>