From: Christoph Haller <ch@rodos.fzk.de>
To: terry@ashtonwoodshomes.com
Cc: pgsql-sql@postgresql.org
Subject: Re: query optimization question
Date: Fri, 08 Nov 2002 11:01:13 +0100
Message-ID: <3DCB8B69.78B247BB@rodos.fzk.de> (raw)
>
> 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
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: pgsql-sql@postgresql.org
Cc: ch@rodos.fzk.de, terry@ashtonwoodshomes.com
Subject: Re: query optimization question
In-Reply-To: <3DCB8B69.78B247BB@rodos.fzk.de>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox