Thread: About inheritance

Hi,

i have 3 tables calling father, child1, child2:

create table father(att0 int4);
create table child1() inherits(father);
create table child2() inherits(father);


i want to get all the instances of the hierarchy:
select * from father;

the explain analyze gives:

 Result
   ->  Append
         ->  Seq Scan on father
         ->  Seq Scan on child1 father



Now i drop the tables and i create them aggain without using the inherits
relationship:

create table father(att0 int4);
create table child1(att0 int4);
create table child2(att0 int4);

again i want to get all the instances of the hierarchy:
(select * from father) UNION ALL (select * from child1) UNION ALL
(select * from child2);

the explain analyze gives:

 Append
   ->  Subquery Scan "*SELECT* 1"
         ->  Seq Scan on father
   ->  Subquery Scan "*SELECT* 2"
         ->  Seq Scan on child1
   ->  Subquery Scan "*SELECT* 3"
         ->  Seq Scan on child2


Can anyone explain me the difference between these two plans?

I expekt to find the same plans because in both cases there is a union to
be done, but i see that in second case there is an additional call to a
routine. I meen the 'Subquery Scan "*SELECT* X"'

Ioannis Theoharis <theohari@ics.forth.gr> writes:
> I expekt to find the same plans because in both cases there is a union to
> be done, but i see that in second case there is an additional call to a
> routine. I meen the 'Subquery Scan "*SELECT* X"'

The subquery scan step is in there because in a UNION construct, there
may be a need to do transformations on the data before it can be
unioned.  For instance you are allowed to UNION an int4 and an int8
column, in which case the int4 values have to be promoted to int8 after
they come out of the subplan.

In the particular case you are showing, the subquery scan steps aren't
really doing anything, but AFAIR the planner does not bother to optimize
them out.  I'd be pretty surprised if they chew up any meaningful amount
of runtime.

            regards, tom lane

Thanks.

Time is little but visible affected for big chierarhies.


Let me do an other question.

I have again a Root table and a hierarchie of tables, all created with the
inherits relationship like:

create table father(att0 int4);
create table child1() inherits(father);
create table child2() inherits(father);
create table child11() inherits(child1);
create table child12() inherits(child1);
create table child21() inherits(child2);
create table child22() inherits(child2);



First i insert 1000 tuples into father table, and then i delete them and i
insert them into child22

I expekt explain analyze to give the same response time at both cases. But
i found that time increases as where as the level, where data are located,
increases.

Can anybody explain me the reason?





On Sun, 22 Aug 2004, Tom Lane wrote:

> Ioannis Theoharis <theohari@ics.forth.gr> writes:
> > I expekt to find the same plans because in both cases there is a union to
> > be done, but i see that in second case there is an additional call to a
> > routine. I meen the 'Subquery Scan "*SELECT* X"'
>
> The subquery scan step is in there because in a UNION construct, there
> may be a need to do transformations on the data before it can be
> unioned.  For instance you are allowed to UNION an int4 and an int8
> column, in which case the int4 values have to be promoted to int8 after
> they come out of the subplan.
>
> In the particular case you are showing, the subquery scan steps aren't
> really doing anything, but AFAIR the planner does not bother to optimize
> them out.  I'd be pretty surprised if they chew up any meaningful amount
> of runtime.
>
>             regards, tom lane
>
> ---------------------------(end of broadcast)---------------------------
> TIP 5: Have you checked our extensive FAQ?
>
>                http://www.postgresql.org/docs/faqs/FAQ.html
>
>

Ioannis Theoharis <theohari@ics.forth.gr> writes:
> I expekt explain analyze to give the same response time at both cases. But
> i found that time increases as where as the level, where data are located,
> increases.

I see no such effect.

            regards, tom lane

I'd like to ask you,
if postgres prefetch child instances in memory ,
whenever a parent table is sequentially scanned,
in order to have them in there
for the possibility the next query to ask for them.

On Sat, 2004-09-18 at 07:42, Ioannis Theoharis wrote:
> I'd like to ask you,
> if postgres prefetch child instances in memory ,
> whenever a parent table is sequentially scanned,
> in order to have them in there
> for the possibility the next query to ask for them.

I'm not sure exactly what you're asking (what do you mean by "child
instances?"), but in any case, PostgreSQL doesn't do any prefetching
("readahead") -- we rely on the kernel to do that if and when it's
appropriate.

-Neil

On Mon, 20 Sep 2004, Neil Conway wrote:

> On Sat, 2004-09-18 at 07:42, Ioannis Theoharis wrote:
> > I'd like to ask you,
> > if postgres prefetch child instances in memory ,
> > whenever a parent table is sequentially scanned,
> > in order to have them in there
> > for the possibility the next query to ask for them.
>
> I'm not sure exactly what you're asking (what do you mean by "child
> instances?"), but in any case, PostgreSQL doesn't do any prefetching
> ("readahead") -- we rely on the kernel to do that if and when it's
> appropriate.
>
> -Neil


I mean that i have a "tree" of tables, that has been created using
'inherits' relationship of postgress.


            0
    1                2
3        4        5        6


Consider this tree. In each node imagine a table. table no 1 inherits
table no 0, table no 3 inherits table 1 ...

The question is, if the table no 0 (root) is secuentially scanned, then
postgress, except from the contents of this table, loads in memory the
contents of tale no 1 or no 2 ?

If the answer is no, then what do you meen "we rely on the kernel to do
that if and when it's appropriate" ?
It's appropriate in my case?