From: Joe <svn@freedomcircle.net>
To: Jim C. Nasby <jnasby@pervasive.com>
Cc: Gavin Sherry <swm@alcove.com.au>
Cc: Magnus Hagander <mha@sollentuna.net>
Cc: pgsql-performance@postgresql.org
Subject: Re: Comparative performance
Date: Tue, 04 Oct 2005 17:37:30 -0400
Message-ID: <4342F61A.1010903@freedomcircle.net> (raw)
In-Reply-To: <20051004203102.GU40138@pervasive.com>
References: <6BCB9D8A16AC4241919521715F4D8BCE92E6D6@algol.sollentuna.se>
<433BDB0B.8010002@freedomcircle.net>
<Pine.LNX.4.58.0509292230310.26600@linuxworld.com.au>
<433BE1A0.5000807@freedomcircle.net>
<20051004203102.GU40138@pervasive.com>
Jim C. Nasby wrote:
> Make sure these indexes exist if you'll be updating or inserting into
> entry:
>
> CREATE INDEX topic__subject_id ON topic(subject_id);
> CREATE INDEX topic__actor_id ON topic(actor_id);
Actually, topic's primary key is topic_id.
> Also, the fact that subject and actor both point to topic along with
> subject_type and actor_type make me suspect that your design is
> de-normalized. Of course there's no way to know without more info.
Yes, the design is denormalized. The reason is that a book or article is
usually by a single author (an "actor" topic) and it will be listed under one
main topic (a "subject" topic). There's a topic_entry table where additional
actors and subjects can be added.
It's somewhat ironic because I used to teach and/or preach normalization and the
"goodness" of a 3NF+ design (also about having the database do aggregation and
sorting as you mentioned in your other email).
> FWIW, I usually use timestamptz for both created and updated fields.
IIRC 'created' ended up as a DATE because MySQL 4 has a restriction about a
single TIMESTAMP column per table taking the default value of current_timestamp.
Joe
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 using the --to and --cc options:
reply via email
To: pgsql-performance@postgresql.org
Cc: svn@freedomcircle.net, jnasby@pervasive.com, swm@alcove.com.au, mha@sollentuna.net
Subject: Re: Comparative performance
In-Reply-To: <4342F61A.1010903@freedomcircle.net>
* 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