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 [EMAIL PROTECTED]
----- Original Message ----- From: "John Arbash Meinel" <[EMAIL PROTECTED]>
To: "SpaceBallOne" <[EMAIL PROTECTED]>
Sent: Tuesday, January 25, 2005 9:56 AM
Subject: Re: [PERFORM] poor performance of db?
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 [EMAIL PROTECTED]
---------------------------(end of broadcast)--------------------------- TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]