Hi all,
Maybe the direction this thread has taken is a bit out of the scope
of this mailing list, but I think it's very interesting and can be
useful for newbie users.
The usual cause of slow deletes is that (a) the table is the target of
some foreign key references from other large tables, and (b) the
referencing columns in those tables aren't indexed.
This is a thing I don't understand, as far as I know the foreign keys
references to primary keys and postgresql creates itself and index over
the primary key, so those columns always should be indexed. Taking into
account Tom's observation I'm missing something, could you explain it to
all of us :)
The referencED column is forced to have an index. The referencING
column is not. The cases where you need an index on the latter are
precisely updates/deletes of the referencED column.
In the old version you are using you can also get burnt by datatype
mismatches --- the foreign key mechanism will allow that as long as
it can find an equality operator for the two types, but that equality
operator might not be indexable.
Lets put an example
CREATE TABLE departments
(
id INT2
CONSTRAINT pk_dept_id PRIMARY KEY,
name VARCHAR(50)
CONSTRAINT nn_dept_name NOT NULL
);
CREATE TABLE users
(
id INT8
CONSTRAINT pk_users_id PRIMARY KEY,
name VARCHAR(50)
CONSTRAINT nn_users_name NOT NULL,
department_id INT2
CONSTRAINT fk_users_deptid REFERENCES departments(id)
CONSTRAINT nn_users_deptid NOT NULL
)
Do we should create the following index?
CREATE INDEX idx_users_deptid ON users(department_id)
Could we say as rule of thumb the following: "Create an index for
each table's foreign key"?
Regards
--
Arnau
