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

> I don't see how performance would be significantly better if you stored
> the common columns of all rows (parent and children) in the parent
> table, in contrast with how it is done now, storing entire rows of child
> tables in the child table and omitting them from the parent table.

Well there are a couple of points.

Firstly, from the simple standpoint of database normalization you
shouldn't have tables that have the same columns. The way it is
implemented, child tables are copies of parent tables.

But more importantly it is bad for performance because selecting from a
parent table causes the same select to be done on all the child tables.
In my case selecting from the parent causes six selects to be done (one
for every child table).

I would have assumed that child tables only contained the new columns
unique to it, not duplicates of the columns already in the parent table.

An insert to a child table would actually cause two inserts to be done
(assuming only one level of inheritance), one to the parent, and then
one to the child.

Therefore, selects from the parent table would only require a single
select (because the common data is all stored in the parent table).

Selects to a child would require two selects to get the entire row (one
to the parent, one to the child). Similar to a view.

As I said previously, performance would depend on what operation you
were mostly doing.

I think I have more or less covered this in my previous postings.

John Lange

On Tue, 2003-01-28 at 17:52, Andras Kadinger wrote:
> I see.
>
> I don't see how performance would be significantly better if you stored
> the common columns of all rows (parent and children) in the parent
> table, in contrast with how it is done now, storing entire rows of child
> tables in the child table and omitting them from the parent table.
>
> Hmm, reviewing your posts to pgsql-performance, I must admit I cannot
> really see what you feel you are losing performance-wise.
>
> As the discussion on pgsql-performance seems to have died off, would you
> be willing to explain to me?
>
> Regards,
> Andras
>
> John Lange wrote:
> >
> > No, the keyword ONLY will limit selects to that table ONLY. I need to
> > return the rows which are common to all tables. Postgres is doing the
> > work in the correct way, however, the issue is the underlaying design
> > which is terribly inefficient requiring a separate table scan for every
> > child table.
> >
> > Thanks for the suggestion.
> >
> > John Lange
> >
> > On Fri, 2003-01-24 at 14:30, Andras Kadinger wrote:
> > > Hi John,
> > >
> > > Isn't the keyword ONLY is what you are after?
> > >
> > > "EXPLAIN select * from tbl_objects where id = 1;" - this will select
> > > from table tbl_objects and all it's descendant tables.
> > >
> > > "EXPLAIN select * from tbl_objects ONLY where id = 1;" - this will
> > > select from table tbl_objects only.
> > >
> > > Regards,
> > > Andras Kadinger