The following bug has been logged on the website:
Bug reference: 8447
Logged by: David Carlos Manuelda
Email address: stormbyte@gmail.com
PostgreSQL version: 9.3.0
Operating system: Gentoo Linux
Description:
In a simple table inheritance model, indexes seem to be ignored when looking
at base table, this is a testcase:
Table schema:
CREATE TABLE base (
id SERIAL,
email TEXT NOT NULL,
PRIMARY KEY (id)
);
CREATE TABLE derived1 (
otherData INTEGER,
PRIMARY KEY (id) -- It is not inherited
) INHERITS (base);
CREATE TABLE derived2 (
otherData2 BIGINT,
PRIMARY KEY (id) -- It is not inherited
) INHERITS (base);
Without any indexes the result is as expected:
EXPLAIN SELECT id FROM base WHERE email='foo@foo.com';
QUERY PLAN
---------------------------------------------------------------
Append (cost=0.00..48.25 rows=13 width=4)
-> Seq Scan on base (cost=0.00..0.00 rows=1 width=4)
Filter: (email = 'foo@foo.com'::text)
-> Seq Scan on derived1 (cost=0.00..24.50 rows=6 width=4)
Filter: (email = 'foo@foo.com'::text)
-> Seq Scan on derived2 (cost=0.00..23.75 rows=6 width=4)
Filter: (email = 'foo@foo.com'::text)
Now I create triggers on each table (since it is a common field), to
demonstrate how it is ignored:
CREATE UNIQUE INDEX email_base_idx ON base(email);
CREATE UNIQUE INDEX email_derived1_idx ON derived1(email);
CREATE UNIQUE INDEX email_derived2_idx ON derived2(email);
And now, see what the same select does:
EXPLAIN SELECT id FROM base WHERE email='foo@foo.com';
QUERY PLAN
-----------------------------------------------------------------------------------------
Append (cost=0.00..16.34 rows=3 width=4)
-> Seq Scan on base (cost=0.00..0.00 rows=1 width=4)
Filter: (email = 'foo@foo.com'::text)
-> Index Scan using email_derived1_idx on derived1 (cost=0.15..8.17
rows=1 width=4)
Index Cond: (email = 'foo@foo.com'::text)
-> Index Scan using email_derived2_idx on derived2 (cost=0.15..8.17
rows=1 width=4)
Index Cond: (email = 'foo@foo.com'::text)
This is not the expected result. It can be seen that on base table, it is
still using sequential scan rather than what would be expected: Index Scan
