Обсуждение: 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>