Thread: Indexes on inheriting tables

Hi,
I'm using Pg 9.0 and inheritance to do table partitioning.

A simple example would be:
CREATE TABLE foo (
   id INTEGER PRIMARY KEY,
   thing VARCHAR(32)
);
CREATE INDEX foo_thing_idx ON foo(thing);
CREATE TABLE foo_1 () INHERITS (foo);


I read that foreign key constraints wouldn't be inherited. However I am
also finding that indexes aren't inherited either.

eg. EXPLAIN SELECT id FROM foo WHERE thing='something';
will indicate that an indexed scan will be done over foo, followed by a
sequential scan over foo_1.

Do I need to make sure I re-create every index on every child table I
create?
That would be.. annoying, at best.

Is there a way to enable inheritance of indexes too?

Cheers,
Toby

On 25/08/11 11:34, Shoaib Mir wrote:
> On Thu, Aug 25, 2011 at 11:17 AM, Toby Corkindale
> <toby.corkindale@strategicdata.com.au
> <mailto:toby.corkindale@strategicdata.com.au>> wrote:
>
>
>     Do I need to make sure I re-create every index on every child table
>     I create?
>     That would be.. annoying, at best.
>
>     Is there a way to enable inheritance of indexes too?
>
>
> You do not need an index on the master table so create indexes on child
> tables only. This way you are avoiding big index scans and only looking
> at the index on the data you are interested in (child table).

Ah, but I'd rather not maintain all those extra indexes!
Say I want to add a new index - now I'd need to go through and add it to
a hundred tables! I guess I can script it.. but it doesn't seem good.

It seems messy to inherit the columns but not the indexes or checks upon
them :(

Toby

On 25/08/11 12:10, Shoaib Mir wrote:
> On Thu, Aug 25, 2011 at 11:57 AM, Toby Corkindale
> <toby.corkindale@strategicdata.com.au
> <mailto:toby.corkindale@strategicdata.com.au>> wrote:
>
>
>     It seems messy to inherit the columns but not the indexes or checks
>     upon them :(
>
>
>
> Yes it can be a bit annoying at timse but you can try to automate the
> whole process as well. Like I found this blog entry -->
> http://blog.inovia.fr/auto-partitioning-on-postgresql-part-1/


As far as I could tell, that isn't automating anything to do with
indexes or checks on the child tables?

Although using a trigger to automate the creation of the child table
itself is still nifty. I hadn't thought of that.

thanks,
Toby

Hi,

On 25 August 2011 11:17, Toby Corkindale
<toby.corkindale@strategicdata.com.au> wrote:
> Do I need to make sure I re-create every index on every child table I
> create?
> That would be.. annoying, at best.

Yes, it is little bit annoying but I like it. You don't need any index
on parent table but you have to create them "manually". I wrote simple
python script which creates partitions and required indexes in advance
(tables are partitioned by date).

I like the flexibility because you can have different indexex on
different partitions. For example, I discovered that adding index will
improve several queries. In the production I can't afford exclusive
lock (build index concurrently takes ages) so I updated and re-run the
script which re-created future partitions.

--
Ondrej Ivanic
(ondrej.ivanic@gmail.com)