Re: Query plan and Inheritance. Weird behavior

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?