BUG #8447: With table inheritance, indexes seems to be ignored when looking over indexed fields in base table

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

--
Sent via pgsql-bugs mailing list (pgsql-bugs@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-bugs