About inheritance

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 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?