Re: Indexes on inheriting tables

2011/8/24 Ondrej Ivanič <ondrej(dot)ivanic(at)gmail(dot)com>

> Hi,
>
> On 25 August 2011 11:17, Toby Corkindale
> <toby(dot)corkindale(at)strategicdata(dot)com(dot)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.
>

My Personal favorite is the LIKE syntax:

CREATE TABLE foo_1 (LIKE foo including indexes ) inherits (foo);

It doesn't help you change children after the fact, but your new
partitions automatically get whatever indexes you've stuck on the master
table.

--Scott

>
> --
> Ondrej Ivanic
> (ondrej(dot)ivanic(at)gmail(dot)com)
>
> --
> Sent via pgsql-general mailing list (pgsql-general(at)postgresql(dot)org)
> To make changes to your subscription:
> http://www.postgresql.org/mailpref/pgsql-general
>