pg.ddx.io  pgsql-performance@postgresql.org mailing list archive  
help / color / mirror / Atom feed
From: Tom Lane <tgl@sss.pgh.pa.us>
To: John Lange <lists@darkcore.net>
Cc: Andras Kadinger <bandit@surfnonstop.com>
Cc: PostgreSQL <pgsql-performance@postgresql.org>
Subject: Re: Query plan and Inheritance. Weird behavior
Date: Wed, 29 Jan 2003 00:05:15 -0500
Message-ID: <19547.1043816715@sss.pgh.pa.us> (raw)
In-Reply-To: <1043810501.3719.28.camel@johnlaptop.darkcore.net>
References: <3E31A255.5250DCF1@surfnonstop.com>
	<1043770598.2045.23.camel@johnlaptop.darkcore.net>
	<3E3717A4.CAD034EC@surfnonstop.com>
	<1043810501.3719.28.camel@johnlaptop.darkcore.net>

John Lange <lists@darkcore.net> writes:
> Firstly, from the simple standpoint of database normalization you
> shouldn't have tables that have the same columns. The way it is
> implemented, child tables are copies of parent tables.

There is no copied data though.  Or are you saying that if any table
in the database has, say, a timestamp column, then it's a failure of
normalization for any other one to have a timestamp column?  Don't think
I buy that.

> But more importantly it is bad for performance because selecting from a
> parent table causes the same select to be done on all the child tables.

So?  The same amount of data gets scanned either way.  To the extent
that the planner fails to generate an optimal plan in such cases, we
have a performance problem --- but that's just an implementation
shortcoming, not a fundamental limitation AFAICS.

The only real disadvantage I can see to the current storage scheme is
that we can't easily make an index that covers both a parent and all its
children; the index would have to include a table pointer as well as a
row pointer.  This creates problems for foreign keys and unique constraints.
But there is more than one way to attack that.

			regards, tom lane



view thread (11+ messages)  latest in thread

Message-ID: <19547.1043816715@sss.pgh.pa.us>
Permalink:  ../19547.1043816715@sss.pgh.pa.us/
Also on:    postgresql.org/message-id/19547.1043816715@sss.pgh.pa.us

 · 

reply

Reply instructions:

You may reply publicly to this message via plain-text email
using any one of the following methods:

* Reply to all the recipients from your mail client; the link fills in
  the headers below: reply via email
  Some clients, webmail especially, drop In-Reply-To and References from
  a link, and the reply then starts a new thread: use the mbox below.

  To: pgsql-performance@postgresql.org
  Cc: tgl@sss.pgh.pa.us, lists@darkcore.net, bandit@surfnonstop.com
  Subject: Re: Query plan and Inheritance. Weird behavior
  In-Reply-To: <19547.1043816715@sss.pgh.pa.us>
  References: <3E31A255.5250DCF1@surfnonstop.com> <1043770598.2045.23.camel@johnlaptop.darkcore.net> <3E3717A4.CAD034EC@surfnonstop.com> <1043810501.3719.28.camel@johnlaptop.darkcore.net> <19547.1043816715@sss.pgh.pa.us>

* Save the following mbox file, import it into your mail client,
  and reply-to-all from there: mbox

This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox