Inheritance and indexes

From: knizhnik <knizhnik(at)garret(dot)ru>
To: pgsql-hackers(at)postgresql(dot)org
Subject: Inheritance and indexes
Date: 2014-01-14 10:07:19
Message-ID: 52D50C57.80704@garret.ru
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-hackers

From PostgreSQL manual:
"A serious limitation of the inheritance feature is that indexes
(including unique constraints) and foreign key constraints only apply to
single tables, not to their inheritance children."

But is it possible to use index for derived table at all?
Why sequential search is used for derived table in the example below:

create table base_table (x integer primary key);
create table derived_table (y integer) inherits (base_table);
insert into base_table values (1);
insert into derived_table values (2,2);
create index derived_index on derived_table(x);
explain select * from base_table where x>=0;
QUERY PLAN
----------------------------------------------------------------------------------------------
Append (cost=0.14..4.56 rows=81 width=4)
-> Index Only Scan using base_table_pkey on base_table
(cost=0.14..3.55 rows=80 width=4)
Index Cond: (x >= 0)
-> Seq Scan on derived_table (cost=0.00..1.01 rows=1 width=4)
Filter: (x >= 0)
(5 rows)

In response to

Responses

Browse pgsql-hackers by date

  From Date Subject
Next Message Peter Geoghegan 2014-01-14 10:20:35 Re: INSERT...ON DUPLICATE KEY LOCK FOR UPDATE
Previous Message David Rowley 2014-01-14 10:06:24 Re: [PATCH] Negative Transition Aggregate Functions (WIP)