Re: Linking tables and indexes

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.