From: <terry@ashtonwoodshomes.com>
To: <pgsql-sql@postgresql.org>
Subject: query optimization question
Date: Mon, 4 Nov 2002 19:19:46 -0500
Message-ID: <000701c28461$0caaa7c0$2766f30a@development.greatgulfhomes.com>
MIME-Version: 1.0
Content-Type: text/plain;
	charset="iso-8859-1"
Content-Transfer-Encoding: 7bit
Importance: Normal

The query below is slow because both the lots table and the deficiency_table
table have thousands of records.  Can anyone tell me how to do the second
subselect (lot_count) by some method of a join instead of a sub - subselect
OR any other method I can use to optimize this query to make it faster?

The objective of the query is:  Tell me for each project, the total number
of deficiencies in the project, and the total number of lots with 1 or more
deficiencies in the project.

SELECT  project_id, marketing_name,
		(SELECT count(lots.lot_id) AS lot_count
		 FROM deficiency_table AS dt, lots
		 WHERE dt.lot_id = lots.lot_id
		    AND lots.division_id = proj.division_id
		    AND lots.project_id = proj.project_id
		) AS def_count,
		(SELECT count(lots.lot_id) AS lot_counter
		 FROM lots
		 WHERE  lots.division_id = proj.division_id
		 	AND lots.project_id = proj.project_id
			AND EXISTS (SELECT 1 FROM deficiency_table AS dt WHERE dt.lot_id =
lots.lot_id)
		) AS lot_count
FROM    projects AS proj
WHERE   proj.division_id = '#variables.local_division_id#'
	AND NOT EXISTS (SELECT 1 FROM menu_group_projects WHERE menu_code = 'WA'
AND division_id = proj.division_id AND project_id = proj.project_id AND
status = 'I')
ORDER BY proj.project_id

Thanks in advance

Terry Fielder
Network Engineer
Great Gulf Homes / Ashton Woods Homes
terry@greatgulfhomes.com