Received: from localhost (postgresql.org [64.49.215.8]) by postgresql.org (Postfix) with ESMTP id 29D70479EA8 for ; Wed, 29 Jan 2003 00:54:44 -0500 (EST) Received: from sss.pgh.pa.us (unknown [192.204.191.242]) by postgresql.org (Postfix) with ESMTP id 33BB44776AE for ; Wed, 29 Jan 2003 00:05:12 -0500 (EST) Received: from sss2.sss.pgh.pa.us (tgl@localhost [127.0.0.1]) by sss.pgh.pa.us (8.12.6/8.12.6) with ESMTP id h0T55G5u019548; Wed, 29 Jan 2003 00:05:16 -0500 (EST) To: John Lange Cc: Andras Kadinger , PostgreSQL Subject: Re: Query plan and Inheritance. Weird behavior 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> Comments: In-reply-to John Lange message dated "28 Jan 2003 21:21:41 -0600" Date: Wed, 29 Jan 2003 00:05:15 -0500 Message-ID: <19547.1043816715@sss.pgh.pa.us> From: Tom Lane X-Virus-Scanned: by AMaViS new-20020517 X-Archive-Number: 200301/376 X-Sequence-Number: 1023 John Lange 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