Thread: Indexes and inheritance

I have a "daughter" table that inherits from a "mother" table.  The mother table has an index on column A.  Would there be any point on creating an index on column A of the daughter?  If so, in what circumstances?

(Using PostgreSQL 8.1.3.)

On March 23, 2006 09:42 pm, "Pierre Thibaudeau" <pierdeux@gmail.com> wrote:
> I have a "daughter" table that inherits from a "mother" table.  The
> mother table has an index on column A.  Would there be any point on
> creating an index on column A of the daughter?  If so, in what
> circumstances?
>

Indexes are not inherited, so in fact you have to create the  index on
column A of the daughter if you want the expected behaviour.

--
Alan

On March 24, 2006 11:06 am, "Pierre Thibaudeau" <pierdeux@gmail.com> wrote:
> Ah, thank you.
>
> So I suppose, the new index on the daughter is akin to a partial index on
> the mother, although one that couldn't be used while searching the
> mother...

It will be used while searching column A on the parent table, which by
default will descend to child tables.  Well, in effect, 2 different
queries will be performed, and their results merged.  You should use
"explain" and "explain analyze" to understand how your queries will
execute, especially in inheritance situations.

You might also read the 8.1 documentation on partitioning if you desire
different behaviour, ie. how to create CHECK constraints that will control
which child tables are examined for particular queries of the parent, which
would effect more of the partial index behaviour.

--
They laughed at Columbus, they laughed at Fulton, they laughed at the
Wright brothers.  But they also laughed at Bozo the Clown." -- Carl Sagan