Message-ID: <3DCB8B69.78B247BB@rodos.fzk.de>
Date: Fri, 08 Nov 2002 11:01:13 +0100
From: Christoph Haller <ch@rodos.fzk.de>
MIME-Version: 1.0
To: terry@ashtonwoodshomes.com
Cc: pgsql-sql@postgresql.org
Subject: Re: query optimization question
Content-Type: text/plain; charset=iso-8859-2
Content-Transfer-Encoding: 7bit

>
> This is the final query, can anyone see anything wrong with it?:
> SELECT  projects.project_id, projects.marketing_name,
>       COUNT(lots.lot_id) AS def_count,
>       COUNT(CASE WHEN dt.days_old_start_date < {d '2002-10-08'}
>                               THEN lots.lot_id ELSE NULL END
>       ) AS def_count_less_30,
>               COUNT(CASE WHEN dt.days_old_start_date >= {d
'2002-10-08'}
>                                       AND dt.days_old_start_date < {d
'2002-09-08'}
>                               THEN lots.lot_id ELSE NULL END
>               ) AS def_count_30_60,
>       COUNT(CASE WHEN dt.days_old_start_date >= {d '2002-09-08'}
>                       THEN lots.lot_id ELSE NULL END
>       ) AS def_count_greater_60,
>       COUNT(DISTINCT(CASE WHEN dt.days_old_start_date < {d
'2002-10-08'}
>                                                       AND
dt.deficiency_status_id = ds.deficiency_status_id
>                                               THEN lots.lot_id ELSE
NULL END)
>       ) AS lot_count_less_30,
>               COUNT(DISTINCT(CASE WHEN dt.days_old_start_date >= {d
'2002-10-08'}
>                                                               AND
dt.days_old_start_date < {d '2002-09-08'}
>                                                       THEN lots.lot_id
ELSE NULL END)
>               ) AS lot_count_30_60,
>       COUNT(DISTINCT(CASE WHEN dt.days_old_start_date >= {d
'2002-09-08'}
>                                               THEN lots.lot_id ELSE
NULL END)
>       ) AS lot_count_greater_60,
>       COUNT(DISTINCT lots.lot_id) AS lot_count
> FROM
>       (SELECT * FROM deficiency_table) AS dt,
>       (SELECT * FROM deficiency_status WHERE is_outstanding) AS ds,
>       (SELECT * FROM projects WHERE division_id = 'GGH') AS proj,
>       (SELECT * FROM lots) AS lots
> WHERE   proj.division_id = 'GGH'
>     AND lots.division_id = proj.division_id
>     AND lots.project_id = proj.project_id
>       AND dt.lot_id = lots.lot_id
>       AND dt.deficiency_status_id = ds.deficiency_status_id
>       AND ds.is_outstanding
>       AND lots.project_id = 'EM16'
>       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')
> GROUP BY projects.project_id, projects.marketing_name
>
First thing I would try
change
> SELECT  projects.project_id, projects.marketing_name,
to
 SELECT  proj.project_id, proj.marketing_name,
and
> GROUP BY projects.project_id, projects.marketing_name
to
 GROUP BY proj.project_id, proj.marketing_name
because I think the sub-SELECT (SELECT * FROM projects WHERE division_id
= 'GGH') AS proj
should be referenced instead of the table projects.
If you still receive the startling result, I'd like to suggest another
approach.
Why not generate a view or sub-SELECT first which shows all the columns
you need to refer to resp. count, and then
SELECT project_id, marketing_name,
                  COUNT( ... ,
                   ... ,
FROM < the view or sub-SELECT>
 -- no WHERE-clauses at all
GROUP BY project_id, marketing_name ;

It should at least make it more easy to track down what's wrong.

Regards, Christoph