Re: Comparative performance

On Thu, Sep 29, 2005 at 08:44:16AM -0400, Joe wrote:
> CREATE TABLE entry (
> entry_id serial PRIMARY KEY,
> title VARCHAR(128) NOT NULL,
> subtitle VARCHAR(128),
> subject_type SMALLINT,
> subject_id INTEGER REFERENCES topic,
> actor_type SMALLINT,
> actor_id INTEGER REFERENCES topic,
> actor VARCHAR(64),
> actor_role VARCHAR(64),
> rel_entry_id INTEGER,
> rel_entry VARCHAR(64),
> description VARCHAR(255),
> quote text,
> url VARCHAR(255),
> entry_date CHAR(10),
> created DATE NOT NULL DEFAULT CURRENT_DATE,
> updated TIMESTAMP WITH TIME ZONE NOT NULL DEFAULT CURRENT_TIMESTAMP)
> WITHOUT OIDS;
> CREATE INDEX entry_actor_id ON entry (actor_id);
> CREATE INDEX entry_subject_id ON entry (subject_id);

A few tips...

Fields in PostgreSQL have alignment requirements, so the smallints
aren't saving you anything right now. If you put both of them together
though, you'll save 4 bytes on most hardware.

You'll also get some minor gains from putting all the variable-length
fields at the end, as well as nullable fields. If you search the
archives for 'field order' you should be able to find some useful info.

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);

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.

FWIW, I usually use timestamptz for both created and updated fields.
--
Jim C. Nasby, Sr. Engineering Consultant jnasby(at)pervasive(dot)com
Pervasive Software http://pervasive.com work: 512-231-6117
vcard: http://jim.nasby.net/pervasive.vcf cell: 512-569-9461