Re: Why is MySQL more chosen over PostgreSQL?

On Tue, 6 Aug 2002, Don Baccus wrote:

> So again relational theory can solve the problem but at a cost in
> efficiency.

If you're talking about theory, efficiency doesn't come into it.
The question is how and whether you can express the constratints
you need to express.

Note that I am not advocating removing anything that does not fit into
relational theory but does let us do things more efficiently. We live
in an imperfect world, after all.

In fact, why don't we split the dicussion into two separate parts:
relational theory vs. object-oriented theory, and practical use
with postgres, and never mix the two. Ok?

> So could a Turing machine.

Theory: Sure. But this is much harder to express in a turing machine
isn't it?

> The view would work, but of course you have to define the view.  Any
> time you have to do something manually, even something as simple as to
> define a view, the chance for casual error is introduced.

Theory: views should automatically make themselves as updatable as
possible, unless expressed otherwise. In fact, relationally, there
is no difference between a view and a base table; that's only part
of a storage model, which doesn't come into it in our perfect
theoretical world.

Practice: defining a non-updatable view is pretty trivial in
postgres.  Defining an updatable view is rather harder, and more
subject to error.  However, in this particular case it's a necessary
evil, since you can't use table inheritance to do what you want.

> > Oops, did I just replace your "object-oriented" system with a
> > relational one that does everything just as easily, and even does
> > something the object-oriented one can't do?
>
> You mean "waste space with meaningless extra data"?

No, I mean set up your database so that a card can be a network_card
or a sound_card, but not both.

You may also waste some space with meaningless data, if you have bugs
in your application, but a) that meaningless data is pretty easy to
clean up, and b) wasting a bit of space is a lot better than having
incorrect data.

> Me, too.  The relational model is extremely powerful but it's not the
> be-all and end-all of all things.

Theory: Never said it was. I said that table inheritance is an
unnecessary addition to a relational database; it offers no capabilities
you can't offer within the relational model, nor does it make things
easier to do than within the relational model. (Since we are talking
about theory, I hasten to add that it is possible to implement something
where the OO way is easier to use than the relational way, but you're
not forced to implement things this way.)

> You still haven't answered my earlier observation that the PG model,
> with all its flaws, can reduce the number of joins required.

Sorry. Let me deal with that now: that's an incorrect observation.

> For instance in your example card and network card need to be joined if
> you want to return network card.  That's what I see in the view.
>
> "FROM card, network_card"
>
> Using PG's inheritance no join is necessary.

But going the other way around:
   FROM card

Result  (cost=0.00..27.32 rows=6 width=36) ->  Append  (cost=0.00..27.32 rows=6 width=36)       ->  Index Scan using
ih_parent_pkeyon ih_parent  (cost=0.00..4.82 rows=1 width=36)       ->  Seq Scan on ih_child ih_parent
(cost=0.00..22.50rows=5 width=36)
 

Sure looks like a join to me.

> So ... assuming my assumption is true and that you've bothered to study
> the implementation, why should I prefer the join over the
> faster-executing single-table extraction if I use PG's type extension
> facility?

Well, it depends on what your more frequent queries are.

But anyway, I realized that some of the joins I've shown are
unnecessary; I've incorrectly implemented, relationally, the inheritance
model you've shown. Here's the explanation:

Given a parent with an ID field as the primary key, and two children
that inherit that field, you can have the same ID in child1 and child2,
resulting in the ID appearing twice in the parent table. In other
words, the PRIMARY KEY constraint on the parent is a lie. If I were
to implement that relationally (though I'm not sure why I'd want to),
I'd just implement the parent as a view of the children, and add
another table to hold the parent-only data. Now the joins under all
circumstances would be exactly the same as in the version implemented
with inheritance, and you'd have the added advantage that there would be
no lies in the database schema. (And I'm sure I've even seen complaints
about this before, and requests for hacks such as cross-table indexes to
get around this.)

If you feel that I'm missing something here, please send me a schema and
queries that you believe that inheritance does more efficiently than any
relational method can in postgres, and I'll implement it relationally
and test it. If it is indeed impossible to implement as efficiently
relationally as it is with inheritance, I will agree with you that, for
the moment, inheritance has some practical uses in postgres. (I'll also
submit a change request to fix the relational stuff so that it can be
implemented as efficiently.)

It could even happen that you will show me something that the relational
model just doesn't handle, in which case you'll have won the argument.

cjs
-- 
Curt Sampson  <cjs@cynic.net>   +81 90 7737 2974   http://www.netbsd.org   Don't you know, in this new Dark Age, we're
alllight.  --XTC