Re: Query plan and Inheritance. Weird behavior

Поиск
Список
Период
Сортировка
От Tom Lane
Тема Re: Query plan and Inheritance. Weird behavior
Дата
Msg-id 19547.1043816715@sss.pgh.pa.us
обсуждение исходный текст
Ответ на Re: Query plan and Inheritance. Weird behavior  (John Lange <lists@darkcore.net>)
Список pgsql-performance
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

В списке pgsql-performance по дате отправления:

Предыдущее
От: John Lange
Дата:
Сообщение: Re: Query plan and Inheritance. Weird behavior
Следующее
От: Matt Mello
Дата:
Сообщение: 1 char in the world