Skip site navigation (1) Skip section navigation (2)

Re: How does PG Inheritance work?

From: "Announce" <truthhurts(at)insightbb(dot)com>
To: <pgsql-novice(at)postgresql(dot)org>
Subject: Re: How does PG Inheritance work?
Date: 2005-11-28 16:58:45
Message-ID: KBEKKNMFLELKGIADDEPEKEBOCDAA.truthhurts@insightbb.com (view raw or flat)
Thread:
Lists: pgsql-novice
Thanks for all of your help on this forum, Tom. This really helps to clear
things up.

I guess I thought that the child tables weren't 'concrete' because it
appeared that operations such as primary key uniqueness across the related
tables was being managed by the parent table for both the parent and all of
its children.

Unless what you said is in the docs under inheritance (I could have missed
it), it would be useful to give scenarios such as that and go into that
amount of detail when describing exactly how inheritance works in Postgres.

-Aaron
-----Original Message-----
From: pgsql-novice-owner(at)postgresql(dot)org
[mailto:pgsql-novice-owner(at)postgresql(dot)org]On Behalf Of Tom Lane
Sent: Monday, November 28, 2005 12:01 AM
To: Announce
Cc: pgsql-novice(at)postgresql(dot)org
Subject: Re: [NOVICE] How does PG Inheritance work?


"Announce" <truthhurts(at)insightbb(dot)com> writes:
> How does Postgres internally handle inheritance under the following
> scenario?
> Using sample tables similar to a previous post:

>  CREATE TABLE employee(id primary key, name varchar, salary numeric(6,2));
>  CREATE TABLE programmer(language varchar, project varchar) INHERITS
> (employee);
>  CREATE TABLE representative (region varchar) INHERITS (employee);

> Let's say for example's sake, there are 10 million rows of PROGRAMMER data
> but only 100 rows of representative data.  Will a query (select, update,
> insert, etc) on the REPRESENTATIVE table take a performance hit because of
> this?

No.

> It seems like the child-table is really not concrete.

What makes you think that?

In this example, queries against EMPLOYEE take a performance hit due to
the existence of the child tables, because they end up scanning all
three tables.  Queries directly against a child table do not notice the
inheritance relationship at all.

			regards, tom lane

---------------------------(end of broadcast)---------------------------
TIP 2: Don't 'kill -9' the postmaster


--
No virus found in this incoming message.
Checked by AVG Free Edition.
Version: 7.1.362 / Virus Database: 267.13.8/184 - Release Date: 11/27/2005


--
No virus found in this incoming message.
Checked by AVG Free Edition.
Version: 7.1.362 / Virus Database: 267.13.8/184 - Release Date: 11/27/2005


In response to

pgsql-novice by date

Next:From: Ilias GoudaropoulosDate: 2005-11-28 22:00:25
Subject: Re: PostgreSQL 8.0.1-2 WinXP Services
Previous:From: operationsengineer1Date: 2005-11-28 16:26:15
Subject: Re: PostgreSQL 8.0.1-2 WinXP Services

Privacy Policy | About PostgreSQL
Copyright © 1996-2014 The PostgreSQL Global Development Group