Re: poor performance of db? - Mailing list pgsql-performance

Thanks for the reply John,

There are approximately 800 rows total in our job table (which stays
approximately the same because 'completed' jobs get moved to a 'job_archive'
table).The other jobs not shown by the specific query could be on backorder
status, temporary deleted status, etc etc.

You are correct in assuming the _id and _no (stands for 'number') fields are
unique - this was one of the first pages I built when I started learning
postgres, so not knowing how to set up primary and foriegn keys at the time,
I did it that way ... it is normalised to a point (probably rather sloppy,
but its a juggling act between learning on the fly, what I'd like to have,
and time constraints of being the only I.T. guy in the company!)...

I think I will definitely focus on converting my database and php pages to
using proper primary keys in postgres - especially if they automatically
index themselves. I didn't do a vacuum analyse on them so that may explain
why they didn't seem to do much.

Thanks,
Dave
space_ball_one@hotmail.com



----- Original Message -----
From: "John Arbash Meinel" <john@arbash-meinel.com>
To: "SpaceBallOne" <space_ball_one@hotmail.com>
Sent: Tuesday, January 25, 2005 9:56 AM
Subject: Re: [PERFORM] poor performance of db?

SpaceBallOne wrote:

>
>
> I tried setting up 10-15 indexes yesterday, but couldn't see they were
> doing anything. I have since deleted them (on the premise that I
> didn't have a clue what I was doing).

Did you VACUUM ANALYZE after you created the indexes? It really depends
on how many rows you need vs how many rows are in the table. If you are
trying to show everything in the tables, then it won't help.

I can tell that your query is returning 353 rows.  How many rows total
do you have? I think the rule is that indexes help when you need < 10%
of your data.

 From what I can see, it looks like all of the *_no columns, and *_id
columns (which are basically your keys), would be helped by having an
index on them.

>
> I'm not actually running any keys in this database... would that be a
> simpler way of running my queries? I only learnt postgres / unix from
> scratch a year ago so my db setup and queries is probably pretty
> messy    :)
>
I would probably think that you would want a "primary key" on every
table, and this would be your column for references. This way you can
get referential integrity, *and* it automatically creates an index.

For instance, the job table could be:

create table job (
    id serial primary key,
    surveyor_id integer references surveyor(id),
    draftor_id integer references draftor(id),
    ...
);

Then your other tables would also need an id field. I can't say much
more without looking deeper, but from the looks of it, all of your "_no"
and "_id" references should probably be referencing a primary key on the
other table. Personally, I always name it "id" and "_id", but if "_no"
means something to you, then you certainly could keep it.

If these entries are not unique, then probably your database isn't
properly normalized.
John
=:->

> Thanks,
> Dave
> space_ball_one@hotmail.com
>