A structure like yours should probably be solved with:
- a multi-column primary key constraint on the m-table (
- a foreign key constraint referencing the primary key of the 1-table.
An optimal index for looking up values in one direction is provided automatically by the primary key of the
CREATE TABLE ticket (
ticket_id integer PRIMARY KEY -- possibly serial instead of integer
, stuff text
CREATE TABLE lookup (
, ticket_id integer REFERENCES ticket(ticket_id)
-- ON UPDATE CASCADE -- optional
-- ON DELETE CASCADE -- optional
, CONSTRAINT lookup_pkey PRIMARY KEY (outside_data_id, ticket_id)
Never use the non-descriptive column name
id. Some not-so-smart ORMs do it, but it's generally unhelpful - an anti-pattern. Use a descriptive, distinct name like
If you ..
just need to know if the association exists
... then a good old foreign key might be all you need. It takes care of that automatically and delivers the additional bonus of relational integrity being enforced no matter what. Plus more options.
It also insists on a related item for each and every
filter_id, so you may not be able to use it.
Actual indexes needed depend on your workload, which is still unclear to me after reading your question multiple times. In particular, the order of columns in a multi-column index (or a primary key constraint for that matter) is relevant - as we have discussed in depth under this related question.
For optimal performance (neglecting costs for index maintenance, assuming the table isn't updated much) and if your queries go both ways, you would create another index in addition to the primary key as defined above:
CREATE INDEX lookup_reverse_idx(ticket_id, outside_data_id);
While you could cover most additional use-cases with just a single-column index on
ticket_id, due to data alignment in PostgreSQL storage, two integer columns in your index result in the same size on disk as just one. So, hardly any cost for some additional gain.
Experience gathered with different RDBMS'es is not always applicable across platforms.
PostgreSQL doesn't have a
CLUSTERED INDEX like SQL server.
CLUSTER command is loosely related, but works differently. It's a one-time operation and does not keep the table clustered. It also completely rewrites the table with all effects of a
VACUUM FULL (in modern versions since at least 9.0).
Depending on your actual workload
CLUSTER may or may not be useful. It can be for looking up multiple related rows (
ticket_id), especially for tables that are not updated a lot.
FILLFACTOR is good, though, especially if you use
CLUSTER - which may otherwise actually be hurting performance if you
UPDATE a lot.