Re: Postgres inherited table, some questions...

Well, considering that the EXPLAIN tables are different from
tablea/tableb, I can only assume that your jobdata tables follow the
same pattern.

Are you familiar with the ONLY syntax for SELECT when using
inheritance? It looks like you might need to SELECT ONLY ... FROM
tableb.

-tfo

On Sep 29, 2004, at 12:10 PM, Net Virtual Mailing Lists wrote:

> Hello,
>
> I hate to re-ask this again (sorry to be repetitive!)... I really could
> use some help with this, if anyone is familiar with table inheritance
> as
> it applies to my questions....
>
> Thanks!
>
> - Greg
>
>> Hello,
>>
>> I am playing with the "INHERITS" functionality of create table to
>> determine its suitability to my needs and I've hit a roadblock
>> (hopefully
>> minor)...
>>
>> If I have two tables like this:
>>
>> CREATE TABLE tablea (
>> id SERIAL
>> );
>>
>> CREATE TABLE tableb () INHERITS (tablea);
>>
>> #1. Now first of all, I notice that if I insert something into
>> tableb, it
>> appears in tablea and if I insert something in tablea, it does not
>> appear
>> in tableb... Is this correct and will the use of the "SERIAL" type
>> cause any confusion or can I insert rows in each table without being
>> concerned about any internal ramifications of this?
>>
>> #2. If I do "explain select id from tablea where id=4", I get
>> something
>> like this:
>>
>> QUERY PLAN
>> -------------------------------------------------------
>> Seq Scan on jobdata (cost=0.00..1.04 rows=1 width=4)
>> Filter: (id = 4)
>>
>>
>> ...If, however, I do "explain select id from tableb where id=4":
>>
>> -------------------------------------
>> ------------------------------------------------
>> Result (cost=0.00..2.08 rows=2 width=4)
>> -> Append (cost=0.00..2.08 rows=2 width=4)
>> -> Seq Scan on jobdata_revisions (cost=0.00..1.04 rows=1
>> width=4)
>> Filter: (id = 4)
>> -> Seq Scan on jobdata jobdata_revisions (cost=0.00..1.04
>> rows=1 width=4)
>> Filter: (id = 4)
>>
>>
>> I'm a bit mystified by the results of tableb, I don't understand what
>> the
>> "Append" and the second "Seq Scan" is for... More importantly, I'm not
>> sure how to optimize this properly...
>>
>> Any ideas what I can do here?...
>>
>> Thanks!
>>
>> - Greg
>>
>
>
>
> ---------------------------(end of
> broadcast)---------------------------
> TIP 7: don't forget to increase your free space map settings