News for hackers. Stuff that matters.
Re:PostgreSQL indexing in Rails by rosser on Friday December 27, 2013 @02:53PM (#6972294)
A side effect of creating these explicit foreign key relationships is that any needed indices are created on both sides of the key.

This is simply not true.

Foreign key relationships typically use the primary key of the referenced table, which is implicitly indexed, by virtue of its being a primary key, not its being a foreign key. The referring column is never implicitly indexed, though it often should be explicitly indexed.

  rosser=> create table referenced (id int primary key, blah text);
  NOTICE:  CREATE TABLE / PRIMARY KEY will create implicit index "referenced_pkey" for table "referenced"
  CREATE TABLE
  rosser=> create table referencing (id int primary key, referenced_id int references referenced(id), stuff text);
  NOTICE:  CREATE TABLE / PRIMARY KEY will create implicit index "referencing_pkey" for table "referencing"
  CREATE TABLE
  rosser=> \d referenced
    Table "public.referenced"
   Column |  Type   | Modifiers 
  --------+---------+-----------
   id     | integer | not null
   blah   | text    | 
  Indexes:
      "referenced_pkey" PRIMARY KEY, btree (id)
  Referenced by:
      TABLE "referencing" CONSTRAINT "referencing_referenced_id_fkey" FOREIGN KEY (referenced_id) REFERENCES referenced(id)

  rosser=> \d referencing
       Table "public.referencing"
      Column     |  Type   | Modifiers 
  ---------------+---------+-----------
   id            | integer | not null
   referenced_id | integer | 
   stuff         | text    | 
  Indexes:
      "referencing_pkey" PRIMARY KEY, btree (id)
  Foreign-key constraints:
      "referencing_referenced_id_fkey" FOREIGN KEY (referenced_id) REFERENCES referenced(id)
Re:PostgreSQL indexing in Rails by mapgrep on Friday December 27, 2013 @03:04PM (#6972348)
I stand corrected! Fair point. While I wish the original author had not sidestepped the data integrity issue, I can see why he'd want to address the index issue independently.