CODEWITHSUNDEEP
postgresqlindexingforeign-keys
alortimor
alortimor

Reputation: 355

postgres prefixed index usage

Setting a primary key, a composite of 3 columns, generates an index, which can be viewed with:

select t.relname as tbl, i.relname as idx, a.attname as col
from  pg_class t, pg_class i, pg_index ix, pg_attribute a
where  t.oid = ix.indrelid
and i.oid = ix.indexrelid
and a.attrelid = t.oid
and a.attnum = any(ix.indkey)
and t.relkind = 'r'
and t.relname not like 'pg%'
order by  t.relname, i.relname;

The table is "customer" as defined in the TPC-C benchmarking guide. The question I have is, when creating the foreign key on the table, as necessitated by the guide, does one need to create a corresponding index. Given that the 2 columns for the foreign key match the first two columns of the primary key, would the index generated as part of the primary key constraint suffice?

Table & key DDL:

create table customer (c_id numeric,c_d_id numeric,c_w_id numeric ..);

alter table customer add constraint pk_customer 
primary key (c_w_id, c_d_id, c_id) ;

alter table customer add constraint fk_cust_district
foreign key (c_w_id, c_id) references district (d_w_id, d_id);

The reason for the question is that in Oracle and SQL Anywhere one need not create an index and the respective optimizers would make use of whichever index assisted with improved referral performance, in this case, the 2 column prefix of the index generated as part of the primary key constraint.

Upvotes: 0

Views: 403

Answers (1)

Laurenz Albe
Laurenz Albe

Reputation: 247235

The way your tables are created, the primary key index cannot support the foreign key constraint well because the columns of the foreign key definition are not at the beginning of the primary key constraint definition.

The primary key index is better than nothing, though: at least it can be scanned for c_w_id (with c_id as a filter), but not for both columns at the same time, as would be most efficient.

So PostgreSQL will make use of the index at hand, but it will still not be very efficient.

Unless there is a good reason that the primary key columns are defined in this order, I suggest that you swap the second and third column in the primary key definition. Then the index is a perfect fit for the foreign key constraint.

If that is not feasible, create a second index on (c_w_id, c_id).

(This would be just the same on Oracle, by the way, except that they have an index skip scan of – in my opinion – questionable merits.)

Upvotes: 1

Related Questions