X-Original-To: pgsql-performance-postgresql.org@localhost.postgresql.org Received: from localhost (unknown [200.46.204.144]) by svr1.postgresql.org (Postfix) with ESMTP id E07AE3A1A38 for ; Tue, 25 Jan 2005 03:15:39 +0000 (GMT) Received: from svr1.postgresql.org ([200.46.204.71]) by localhost (av.hub.org [200.46.204.144]) (amavisd-new, port 10024) with ESMTP id 61858-06 for ; Tue, 25 Jan 2005 03:15:33 +0000 (GMT) Received: from server07.icaen.uiowa.edu (server07.icaen.uiowa.edu [128.255.17.47]) by svr1.postgresql.org (Postfix) with ESMTP id C14173A56A9 for ; Tue, 25 Jan 2005 03:15:34 +0000 (GMT) Received: from server11.icaen.uiowa.edu (server11.icaen.uiowa.edu [128.255.17.51]) by server07.icaen.uiowa.edu (8.13.2/8.12.9) with ESMTP id j0P3FWFs007404; (envelope-from ) Mon, 24 Jan 2005 21:15:32 -0600 (CST) Received: from [192.168.1.101] (67-41-94-194.cdrr.qwest.net [67.41.94.194]) (authenticated user=jfmeinel) by server11.icaen.uiowa.edu (8.13.2/smtp-serv-1.7) with ESMTP id j0P3FThv009728 (version=TLSv1/SSLv3 cipher=DHE-RSA-AES256-SHA bits=256); (envelope-from ) Mon, 24 Jan 2005 21:15:30 -0600 (CST) Message-ID: <41F5B9D0.2010803@arbash-meinel.com> Date: Mon, 24 Jan 2005 21:15:28 -0600 From: John Arbash Meinel User-Agent: Mozilla Thunderbird 1.0 (Macintosh/20041206) X-Accept-Language: en-us, en MIME-Version: 1.0 To: SpaceBallOne Cc: pgsql-performance@postgresql.org Subject: Re: poor performance of db? References: <41F4BE45.4040402@uptime.ee> <41F5A740.4060404@arbash-meinel.com> In-Reply-To: X-Enigmail-Version: 0.89.6.0 X-Enigmail-Supports: pgp-inline, pgp-mime Content-Type: multipart/signed; micalg=pgp-sha1; protocol="application/pgp-signature"; boundary="------------enig82CD3C31C08974FDE64E27C7" X-Virus-Scanned: ClamAV 0.80/640/Thu Dec 23 12:48:27 2004 clamav-milter version 0.80j on clamav.icaen.uiowa.edu X-Virus-Scanned: ClamAV 0.80/655/Fri Jan 7 07:54:13 2005, clamav-milter version 0.75 on clamav.icaen.uiowa.edu X-Virus-Status: Clean X-Virus-Status: Clean X-Virus-Scanned: by amavisd-new at hub.org X-Spam-Status: No, hits=0 tagged_above=0 required=5 tests= X-Spam-Level: X-Archive-Number: 200501/450 X-Sequence-Number: 10140 This is an OpenPGP/MIME signed message (RFC 2440 and 3156) --------------enig82CD3C31C08974FDE64E27C7 Content-Type: text/plain; charset=ISO-8859-1; format=flowed Content-Transfer-Encoding: 7bit SpaceBallOne wrote: > 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. You probably can add them now if you don't want to do a lot of redesign. ALTER TABLE job ADD PRIMARY KEY (id); If they are not unique this will cause problems, but as they should be unique, I think it will work. I'm not sure how much help indexes will be if you only have 800 rows, and your queries use 300+ of them. You might need re-think the query/table design. You might try doing nested queries, or explicit joins, rather than one big query with a WHERE clause. Meaning do stuff like: SELECT (job JOIN customer ON job.customer_no = customer.customer_no) as jc JOIN location on jc.location_no = location.location_no ... I also see that the planner seems to mis-estimate the number of rows in some cases. Like here: > -> Hash (cost=14.53..14.53 rows=853 width=19) (actual > time=9.79..9.79 rows=0 loops=1) > -> Seq Scan on street (cost=0.00..14.53 rows=853 > width=19) (actual time=0.01..5.12 rows=853 loops=1) > -> Hash (cost=9.91..9.91 rows=491 width=20) (actual > time=5.73..5.73 rows=0 loops=1) > -> Seq Scan on ubd (cost=0.00..9.91 rows=491 width=20) > (actual time=0.02..2.98 rows=491 Where it thinks the hash will return all of the rows from the sequential scan, when in reality it returns none. I think problems with the planner fall into 3 categories. 1. You didn't VACUUM ANALYZE. 2. You did, but the planner doesn't keep sufficient statistics (ALTER TABLE job ALTER COLUMN no SET STATISTICS ) 3. You're join needs cross column statistics, which postgres doesn't support (yet). If you only have 800 rows, I don't think you have to worry about statistics, so that leaves things at 1 or 3. If you did do 1, then I don't know what to tell you. John =:-> PS> I'm not a guru at this stuff, so some of what I say may be wrong. But hopefully I point you in the right direction. > > Thanks, > Dave > space_ball_one@hotmail.com > --------------enig82CD3C31C08974FDE64E27C7 Content-Type: application/pgp-signature; name="signature.asc" Content-Description: OpenPGP digital signature Content-Disposition: attachment; filename="signature.asc" -----BEGIN PGP SIGNATURE----- Version: GnuPG v1.2.4 (Darwin) Comment: Using GnuPG with Thunderbird - http://enigmail.mozdev.org iD8DBQFB9bnQJdeBCYSNAAMRAntsAJ9FvH7bcFZm+ZraNeR8/dF3+Hj6UACfYdPY PPclQb8As+8QZhEaA+V+pkI= =Wi5q -----END PGP SIGNATURE----- --------------enig82CD3C31C08974FDE64E27C7--