Postgres inherited table, some questions... - Mailing list pgsql-general

From Net Virtual Mailing Lists
Subject Postgres inherited table, some questions...
Date
Msg-id 20040924133950.9558@mail.net-virtual.com
Whole thread Raw
List pgsql-general
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




pgsql-general by date:

Previous
From: "Magnus Hagander"
Date:
Subject: Re: PostgreSQL 8 on Win2003 AD server
Next
From: Tom Lane
Date:
Subject: Re: How do I disable: Adding missing FROM-clause