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.
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.