Thread: Postgres inherited table, some questions...
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
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 >
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