Re: Query plan and Inheritance. Weird behavior - Mailing list pgsql-performance

> This gets any rows in tbl_objects that have id=1 and any rows in any
> subtables that have id=1.  Is that the intended effect?

It is the intended result, but not the expected implementation.

Doing more investigation I think I figured out why Postgres does what it
does.

Creating child tables by inheriting from another table doesn't really do
what I consider to be 'true' inheritance, at least not in the way I
expected as a programmer.

Postgres seems to create "child" tables by first fully duplicating the
parent table and then adding the new columns to it. It then links the
tables internally some how so that a query on a parent table also
queries the child tables.

IHO this seems like inheritance by 'brute force' and a parent table that
has many children will cause a significant performance hit.

When I say "as a programmer" what I mean is I had expected it to be done
entirely the opposite way. In other words, child tables would simply be
linked internally to the parent table and a new table created which only
contains the new columns.

In this way the parent table would not need to know, nor would it care
about child tables in any way (just like inheritance in most programming
languages). If done this way a select on a parent table would only
require the retrieval of a single row and a select on a child table
would only require the retrieval of two rows (one in the child table and
one in the parent table).

I don't pretend to know the intricacies of Postgres performance but this
is the way I'm interpreting the data from the explains.

At this time, now that I (think I) understand how the inheritance is
implemented I'm considering abandoning it in Postgres and solving the
issue entirely pragmatically.

I hoping someone on the list will tell me where I'm going wrong here or
what wrong assumptions I'm making.

John Lange

On Wed, 2003-01-22 at 18:59, Stephan Szabo wrote:
>
> On 22 Jan 2003, John Lange wrote:
>
> > I have a database that makes fairly extensive use of table Inheritance.
> >
> > Structure is one parent table and 5 child tables as follows:
> >
> > tbl_objects (parent table)
> >   -> tbl_viewers
> >   -> tbl_documents
> >     -> tbl_icons
> >   -> tbl_massemails
> >   -> tbl_formats
> >
> > I have two questions:
> >
> > First, if I create an index on the parent table will queries to the
> > child tables use that index?
>
> AFAIK no since indices aren't inherited.
>
> > Secondly, I tried to use explain to find out but I got very strange
> > results. It appears to read all the child tables even when you specify
> > only the parent table. In this case this appears to make the select do 6
> > queries instead of only 1. Obviously a huge performance hit. And none of
> > them uses the index though the table only has 420 rows at the moment so
> > that might be why its just doing a scan (though IMHO 'explain' should
> > explain that it isn't using the available index and why).
>
> It seems reasonable to me since given the # of rows and the estimated
> row width the table is probably only like 5 or 6 pages.  Reading the index
> is unlikely to make life much better given an index read, seek in heap
> file, read heap file page.
>
> > I can't say that I'm reading these results properly but here they are:
> >
> > "EXPLAIN select * from tbl_objects where id = 1;"
>
> This gets any rows in tbl_objects that have id=1 and any rows in any
> subtables that have id=1.  Is that the intended effect?
>
>