It's pretty clear to me that an inherited index should be only one
index. There may be a case for optional non-inherited indexes (CREATE
INDEX ON ONLY foobar), but if the index is inherited, it is just one
index.
At the end of the day though, the reason is only performance. The
semantics should be the same no matter whether implemented as multiple
indexes or not. Performance is much better with one index though.(*)
(*) Assuming you use inheritance in the queries, which I have found is
the most common thing. That's reflected in the 7.1 defaults where
inheritance is the default.
Oliver Elphick wrote:
>
> Alfred Perlstein wrote:
> >* Oliver Elphick <olly@lfix.co.uk> [001018 04:59] wrote:
> >> Do you mean that inheriting tables should share a single index with their
> >> ancestors, or that each descendant should get a separate index on the
> >> same pattern as its ancestors'?
> >>
> >> With the former, the inherited index could be used to enforce a primary
> >> key over a whole inheritance hierarchy, and would presumable make it
> >> easier to implement RI against an inheritance hierarchy. Is this what
> >> you have in mind?
> >
> >Not really, it's more of a convience issue for me, a 'derived table'
> >should inherit the attributes of the 'base table' (including indecies),
> >having an index shared between two tables is an interesting idea but
> >not what I had in mind.
>
> Well then, what will happen if I do
>
> SELECT * FROM table* WHERE inherited_unique_indexed_field = some_value;
>
> would I expect to get back multiple rows? Are all the separate indexes
> candidates for use in the selection?
>
> I think you are highlighting the fact that we still haven't satisfactorily
> defined the semantics of inheritance in PostgreSQL; is it merely a
> template system or is it something more meaningful? What inheritance
> specifications are we going to work towards?
>
> --
> Oliver Elphick Oliver.Elphick@lfix.co.uk
> Isle of Wight http://www.lfix.co.uk/oliver
> PGP: 1024R/32B8FAA1: 97 EA 1D 47 72 3F 28 47 6B 7E 39 CC 56 E4 C1 47
> GPG: 1024D/3E1D0C1C: CA12 09E0 E8D5 8870 5839 932A 614D 4C34 3E1D 0C1C
> ========================================
> "Delight thyself also in the LORD; and he shall give
> thee the desires of thine heart." Psalms 37:4
--
Chris Bitmead
mailto:chris@bitmead.com