Обсуждение: 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

"stormbyte@gmail.com" <stormbyte@gmail.com> wrote:=0A=0A> [ Seq Scan is use=
d on empty relation, rather than Index Scan ]=0A=0A> This is not the expect=
ed result.=A0 [ ... ] it is still using=0A> sequential scan rather than wha=
t would be expected: Index Scan=0A=0AThis is not a bug.=0A=0AIf statistics =
indicate that all rows can be accessed with one page=0Aaccess (to the heap)=
 why should it use an index?=0A=0A--=0AKevin Grittner=0AEDB: http://www.ent=
erprisedb.com=0AThe Enterprise PostgreSQL Company