471,049 Members | 1,503 Online
Bytes | Software Development & Data Engineering Community
Post +

Home Posts Topics Members FAQ

Join Bytes to post your question to a community of 471,049 software developers and data experts.

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


---------------------------(end of broadcast)---------------------------
TIP 9: the planner will ignore your desire to choose an index scan if your
joining column's datatypes do not match

Nov 23 '05 #1
0 899

This discussion thread is closed

Replies have been disabled for this discussion.

Similar topics

reply views Thread by zerobearing2 | last post: by
3 posts views Thread by Darkcamel | last post: by
1 post views Thread by Jack Orenstein | last post: by
reply views Thread by leo001 | last post: by

By using Bytes.com and it's services, you agree to our Privacy Policy and Terms of Use.

To disable or enable advertisements and analytics tracking please visit the manage ads & tracking page.