pg.ddx.io pgsql-sql@postgresql.org mailing list archive
help / color / mirror / Atom feedquery optimization question
15+ messages / 6 participants
[nested] [flat]
* query optimization question
@ 2002-11-05 00:19 terry@ashtonwoodshomes.com
0 siblings, 0 replies; 15+ messages in thread
From: terry@ashtonwoodshomes.com @ 2002-11-05 00:19 UTC (permalink / raw)
To: pgsql-sql
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
^ permalink raw reply [nested|flat] 15+ messages in thread
* Re: query optimization question
@ 2002-11-06 13:56 terry@greatgulfhomes.com
0 siblings, 0 replies; 15+ messages in thread
From: terry@greatgulfhomes.com @ 2002-11-06 13:56 UTC (permalink / raw)
To: ch@rodos.fzk.de; pgsql-sql
No offence taken, however it is incorrect, my SQL is pretty good. I
received no other responses... And I later realized the solution to my
question:
(EXPERTS READ ON: If anyone can show me how to use a group by or otherwise
optimize I would be grateful)
This subquery:
SELECT project_id, marketing_name,
(SELECT count(lots.lot_id) AS lot_count
FROM deficiency_table AS dt, lots, deficiency_status AS ds
WHERE dt.lot_id = lots.lot_id
AND lots.division_id = proj.division_id
AND lots.project_id = proj.project_id
AND dt.deficiency_status_id = ds.deficiency_status_id
AND ds.is_outstanding
#PreserveSingleQuotes(variables.base_query)#
) AS def_count,
Actually does return a deficiency count, where there could be more then 1
deficiency per lot. In order to get my lot_count, (number of lots with 1 or
more deficiencies) I just needed to add a DISTINCT clause in my count()
aggregate, ie SELECT count(DISTINCT lots.lot_id)... I forgot one could do
that:
(SELECT count(DISTINCT lots.lot_id) AS lot_count
FROM deficiency_table AS dt, lots, deficiency_status AS ds
WHERE dt.lot_id = lots.lot_id
AND lots.division_id = proj.division_id
AND lots.project_id = proj.project_id
AND dt.days_old_start_date >= #CreateODBCDate(DateAdd("d", -
int(ListLast(variables.aging_breakdown_list, ",")), now() ))#
AND dt.deficiency_status_id = ds.deficiency_status_id
AND ds.is_outstanding
#PreserveSingleQuotes(variables.base_query)#
) AS lot_count_greater_#ListLast(variables.aging_breakdown_list, ",")#,
Note the #PreserveSingleQuotes(variables.base_query)# is dynamic code that
further selects deficiencies by various criteria, eg just for a particular
supplier.
This query is actually dynamic, if all I had to do was the above 2 clauses
then I most certainly COULD do a group by.
However, for the total deficiencies I am then splitting up the total into
aging groups, eg <30, 30-60, 60-90, and >90 days old. The query for that
looks like the below. But before I paste it in, I would like to optimize
it, if I could do so with a group by clause I most certainly would, but I
don't see how I can BECAUSE OF THE AGING BREAKDOWN:
SELECT project_id, marketing_name,
(SELECT count(lots.lot_id) AS lot_count
FROM deficiency_table AS dt, lots, deficiency_status AS ds
WHERE dt.lot_id = lots.lot_id
AND lots.division_id = proj.division_id
AND lots.project_id = proj.project_id
AND dt.deficiency_status_id = ds.deficiency_status_id
AND ds.is_outstanding
AND dt.assigned_supplier_id = '101690'
) AS def_count,
(SELECT count(lots.lot_id) AS lot_count
FROM deficiency_table AS dt, lots, deficiency_status AS ds
WHERE dt.lot_id = lots.lot_id
AND lots.division_id = proj.division_id
AND lots.project_id = proj.project_id
AND dt.days_old_start_date < {d '2002-10-07'}
AND dt.deficiency_status_id = ds.deficiency_status_id
AND ds.is_outstanding
AND dt.assigned_supplier_id = '101690'
) AS def_count_less_30,
(SELECT count(lots.lot_id) AS lot_count
FROM deficiency_table AS dt, lots, deficiency_status AS ds
WHERE dt.lot_id = lots.lot_id
AND lots.division_id = proj.division_id
AND lots.project_id = proj.project_id
AND dt.days_old_start_date >= {d '2002-10-07'}
AND dt.days_old_start_date < {d '2002-09-07'}
AND dt.deficiency_status_id = ds.deficiency_status_id
AND ds.is_outstanding
AND dt.assigned_supplier_id = '101690'
) AS def_count_30_60,
(SELECT count(lots.lot_id) AS lot_count
FROM deficiency_table AS dt, lots, deficiency_status AS ds
WHERE dt.lot_id = lots.lot_id
AND lots.division_id = proj.division_id
AND lots.project_id = proj.project_id
AND dt.days_old_start_date >= {d '2002-09-07'}
AND dt.days_old_start_date < {d '2002-08-08'}
AND dt.deficiency_status_id = ds.deficiency_status_id
AND ds.is_outstanding
AND dt.assigned_supplier_id = '101690'
) AS def_count_60_90,
(SELECT count(lots.lot_id) AS lot_count
FROM deficiency_table AS dt, lots, deficiency_status AS ds
WHERE dt.lot_id = lots.lot_id
AND lots.division_id = proj.division_id
AND lots.project_id = proj.project_id
AND dt.days_old_start_date >= {d '2002-08-08'}
AND dt.deficiency_status_id = ds.deficiency_status_id
AND ds.is_outstanding
AND dt.assigned_supplier_id = '101690'
) AS def_count_greater_90,
(SELECT count(DISTINCT lots.lot_id) AS lot_count
FROM deficiency_table AS dt, lots, deficiency_status AS ds
WHERE dt.lot_id = lots.lot_id
AND lots.division_id = proj.division_id
AND lots.project_id = proj.project_id
AND dt.days_old_start_date < {d '2002-10-07'}
AND dt.deficiency_status_id = ds.deficiency_status_id
AND ds.is_outstanding
AND dt.assigned_supplier_id = '101690'
) AS lot_count_less_30,
(SELECT count(DISTINCT lots.lot_id) AS lot_count
FROM deficiency_table AS dt, lots, deficiency_status AS ds
WHERE dt.lot_id = lots.lot_id
AND lots.division_id = proj.division_id
AND lots.project_id = proj.project_id
AND dt.days_old_start_date >= {d '2002-10-07'}
AND dt.days_old_start_date < {d '2002-09-07'}
AND dt.deficiency_status_id = ds.deficiency_status_id
AND ds.is_outstanding
AND dt.assigned_supplier_id = '101690'
) AS lot_count_30_60,
(SELECT count(DISTINCT lots.lot_id) AS lot_count
FROM deficiency_table AS dt, lots, deficiency_status AS ds
WHERE dt.lot_id = lots.lot_id
AND lots.division_id = proj.division_id
AND lots.project_id = proj.project_id
AND dt.days_old_start_date >= {d '2002-09-07'}
AND dt.days_old_start_date < {d '2002-08-08'}
AND dt.deficiency_status_id = ds.deficiency_status_id
AND ds.is_outstanding
AND dt.assigned_supplier_id = '101690'
) AS lot_count_60_90,
(SELECT count(DISTINCT lots.lot_id) AS lot_count
FROM deficiency_table AS dt, lots, deficiency_status AS ds
WHERE dt.lot_id = lots.lot_id
AND lots.division_id = proj.division_id
AND lots.project_id = proj.project_id
AND dt.days_old_start_date >= {d '2002-08-08'}
AND dt.deficiency_status_id = ds.deficiency_status_id
AND ds.is_outstanding
AND dt.assigned_supplier_id = '101690'
) AS lot_count_greater_90,
(SELECT count(DISTINCT lots.lot_id) AS lot_count
FROM deficiency_table AS dt, lots, deficiency_status AS ds
WHERE dt.lot_id = lots.lot_id
AND lots.division_id = proj.division_id
AND lots.project_id = proj.project_id
AND dt.deficiency_status_id = ds.deficiency_status_id
AND ds.is_outstanding
AND dt.assigned_supplier_id = '101690'
) AS lot_count
FROM projects AS proj
WHERE proj.division_id = 'GGH'
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
If anyone can see a way to do a group by to do this, then I will be happy to
hear about it, because currently the resultset has to do a separate
(sequential or index) scan of the deficiencies table. The only way I can
see to do a group by would be to break out the aging categories into
separate queries, but that wins me nothing because each query then does its
own scan...
The expected simplified output of this query looks like this:
Project <30 30-60 >=60 lot total <30 30-60 >=60 def total
X 1 2 1 4 5 10 5 20 (if X had 4 lots, each of 5 deficiencies)
Y 1 1 0 2 3 3 0 6 (each has eg 3 deficiencies in project Y)
Terry Fielder
Network Engineer
Great Gulf Homes / Ashton Woods Homes
terry@greatgulfhomes.com
> -----Original Message-----
> From: ch@rodos.fzk.de [mailto:ch@rodos.fzk.de]
> Sent: Wednesday, November 06, 2002 4:54 AM
> To: terry@ashtonwoodshomes.com
> Subject: Re: [SQL] query optimization question
>
>
>
> Dear Terry,
> When I was reading the objective of your query, I expected at
> least one
> GROUP BY clause within. I do not intend to be offensive - not at all,
> but your query very much looks like you're lacking in basic SQL
> knowledge
> (did you receive any other reply?).
> The clause
> WHERE ...
> AND division_id = proj.division_id AND project_id =
> proj.project_id ...
>
> is leading to a JOIN of your projects table to itself.
> I'm pretty sure that's the main reason why the query is slow.
> As I understand your database table design, there are relations about
> divisions, projects, lots, and deficiencies of lots. And you are
> running a master database for all of them.
> I've tried to write two queries (see below) to retrieve the
> information
> you want (BTW I think your first subquery counts the total number of
> lots within the project but not the total number of deficiencies).
> Both queries may still run slow because three tables have to be joined
> (Please try them within the 'psql' interactive terminal first).
> Also, they may not work at all (I could not verify them as I did not
> know about your CREATE TABLE statements and did not have data to put
> in).
> I'm willing to help, so if it's not working this information would be
> very useful to me. I am no SQL guru, so I cannot see any way to put
> these
> two into one. But this looks like an interesting task, maybe we should
> put this topic to the list again as soon as we make the
> single ones run.
>
> Probably, you'll need to create several indexes to speed up.
>
> -- for each project, the total number of deficiencies
> SELECT p.project_id, p.marketing_name, COUNT(d.lot_id) AS def_count
> FROM projects AS p,
> lots AS l LEFT JOIN deficiency_table AS d
> ON ( d.lot_id = l.lot_id )
> WHERE l.division_id = p.division_id
> AND p.division_id = '#variables.local_division_id#'
> GROUP BY p.project_id, p.marketing_name ;
>
> -- for each project, the total number of lots with 1 or more
> deficiencies
> SELECT p.project_id, p.marketing_name, COUNT(l.lot_id) AS
> def_lot_count
> FROM projects AS p,
> lots AS l LEFT JOIN deficiency_table AS d
> ON ( d.lot_id = l.lot_id )
> WHERE l.division_id = p.division_id
> AND p.division_id = '#variables.local_division_id#'
> GROUP BY p.project_id, p.marketing_name HAVING COUNT(d.lot_id) > 0 ;
>
> Once again, no offence intended, but I recommend to read a book on SQL
> soon.
>
> Regards, Christoph
>
> >
> > 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
> >
>
^ permalink raw reply [nested|flat] 15+ messages in thread
* FW: query optimization question
@ 2002-11-06 14:01 terry@ashtonwoodshomes.com
2002-11-06 15:11 ` Re: FW: query optimization question Richard Huxton <dev@archonet.com>
2002-11-06 16:22 ` Re: FW: query optimization question Stephan Szabo <sszabo@megazone23.bigpanda.com>
2002-11-06 16:44 ` Re: query optimization question Masaru Sugawara <rk73@sea.plala.or.jp>
0 siblings, 3 replies; 15+ messages in thread
From: terry@ashtonwoodshomes.com @ 2002-11-06 14:01 UTC (permalink / raw)
To: pgsql-sql
No offence taken, however it is incorrect, my SQL is pretty good. I
received no other responses... And I later realized the solution to my
question:
(EXPERTS READ ON: If anyone can show me how to use a group by or otherwise
optimize I would be grateful)
This subquery:
SELECT project_id, marketing_name,
(SELECT count(lots.lot_id) AS lot_count
FROM deficiency_table AS dt, lots, deficiency_status AS ds
WHERE dt.lot_id = lots.lot_id
AND lots.division_id = proj.division_id
AND lots.project_id = proj.project_id
AND dt.deficiency_status_id = ds.deficiency_status_id
AND ds.is_outstanding
#PreserveSingleQuotes(variables.base_query)#
) AS def_count,
Actually does return a deficiency count, where there could be more then 1
deficiency per lot. In order to get my lot_count, (number of lots with 1 or
more deficiencies) I just needed to add a DISTINCT clause in my count()
aggregate, ie SELECT count(DISTINCT lots.lot_id)... I forgot one could do
that:
(SELECT count(DISTINCT lots.lot_id) AS lot_count
FROM deficiency_table AS dt, lots, deficiency_status AS ds
WHERE dt.lot_id = lots.lot_id
AND lots.division_id = proj.division_id
AND lots.project_id = proj.project_id
AND dt.days_old_start_date >= #CreateODBCDate(DateAdd("d", -
int(ListLast(variables.aging_breakdown_list, ",")), now() ))#
AND dt.deficiency_status_id = ds.deficiency_status_id
AND ds.is_outstanding
#PreserveSingleQuotes(variables.base_query)#
) AS lot_count_greater_#ListLast(variables.aging_breakdown_list, ",")#,
Note the #PreserveSingleQuotes(variables.base_query)# is dynamic code that
further selects deficiencies by various criteria, eg just for a particular
supplier.
This query is actually dynamic, if all I had to do was the above 2 clauses
then I most certainly COULD do a group by.
However, for the total deficiencies I am then splitting up the total into
aging groups, eg <30, 30-60, 60-90, and >90 days old. The query for that
looks like the below. But before I paste it in, I would like to optimize
it, if I could do so with a group by clause I most certainly would, but I
don't see how I can BECAUSE OF THE AGING BREAKDOWN:
SELECT project_id, marketing_name,
(SELECT count(lots.lot_id) AS lot_count
FROM deficiency_table AS dt, lots, deficiency_status AS ds
WHERE dt.lot_id = lots.lot_id
AND lots.division_id = proj.division_id
AND lots.project_id = proj.project_id
AND dt.deficiency_status_id = ds.deficiency_status_id
AND ds.is_outstanding
AND dt.assigned_supplier_id = '101690'
) AS def_count,
(SELECT count(lots.lot_id) AS lot_count
FROM deficiency_table AS dt, lots, deficiency_status AS ds
WHERE dt.lot_id = lots.lot_id
AND lots.division_id = proj.division_id
AND lots.project_id = proj.project_id
AND dt.days_old_start_date < {d '2002-10-07'}
AND dt.deficiency_status_id = ds.deficiency_status_id
AND ds.is_outstanding
AND dt.assigned_supplier_id = '101690'
) AS def_count_less_30,
(SELECT count(lots.lot_id) AS lot_count
FROM deficiency_table AS dt, lots, deficiency_status AS ds
WHERE dt.lot_id = lots.lot_id
AND lots.division_id = proj.division_id
AND lots.project_id = proj.project_id
AND dt.days_old_start_date >= {d '2002-10-07'}
AND dt.days_old_start_date < {d '2002-09-07'}
AND dt.deficiency_status_id = ds.deficiency_status_id
AND ds.is_outstanding
AND dt.assigned_supplier_id = '101690'
) AS def_count_30_60,
(SELECT count(lots.lot_id) AS lot_count
FROM deficiency_table AS dt, lots, deficiency_status AS ds
WHERE dt.lot_id = lots.lot_id
AND lots.division_id = proj.division_id
AND lots.project_id = proj.project_id
AND dt.days_old_start_date >= {d '2002-09-07'}
AND dt.days_old_start_date < {d '2002-08-08'}
AND dt.deficiency_status_id = ds.deficiency_status_id
AND ds.is_outstanding
AND dt.assigned_supplier_id = '101690'
) AS def_count_60_90,
(SELECT count(lots.lot_id) AS lot_count
FROM deficiency_table AS dt, lots, deficiency_status AS ds
WHERE dt.lot_id = lots.lot_id
AND lots.division_id = proj.division_id
AND lots.project_id = proj.project_id
AND dt.days_old_start_date >= {d '2002-08-08'}
AND dt.deficiency_status_id = ds.deficiency_status_id
AND ds.is_outstanding
AND dt.assigned_supplier_id = '101690'
) AS def_count_greater_90,
(SELECT count(DISTINCT lots.lot_id) AS lot_count
FROM deficiency_table AS dt, lots, deficiency_status AS ds
WHERE dt.lot_id = lots.lot_id
AND lots.division_id = proj.division_id
AND lots.project_id = proj.project_id
AND dt.days_old_start_date < {d '2002-10-07'}
AND dt.deficiency_status_id = ds.deficiency_status_id
AND ds.is_outstanding
AND dt.assigned_supplier_id = '101690'
) AS lot_count_less_30,
(SELECT count(DISTINCT lots.lot_id) AS lot_count
FROM deficiency_table AS dt, lots, deficiency_status AS ds
WHERE dt.lot_id = lots.lot_id
AND lots.division_id = proj.division_id
AND lots.project_id = proj.project_id
AND dt.days_old_start_date >= {d '2002-10-07'}
AND dt.days_old_start_date < {d '2002-09-07'}
AND dt.deficiency_status_id = ds.deficiency_status_id
AND ds.is_outstanding
AND dt.assigned_supplier_id = '101690'
) AS lot_count_30_60,
(SELECT count(DISTINCT lots.lot_id) AS lot_count
FROM deficiency_table AS dt, lots, deficiency_status AS ds
WHERE dt.lot_id = lots.lot_id
AND lots.division_id = proj.division_id
AND lots.project_id = proj.project_id
AND dt.days_old_start_date >= {d '2002-09-07'}
AND dt.days_old_start_date < {d '2002-08-08'}
AND dt.deficiency_status_id = ds.deficiency_status_id
AND ds.is_outstanding
AND dt.assigned_supplier_id = '101690'
) AS lot_count_60_90,
(SELECT count(DISTINCT lots.lot_id) AS lot_count
FROM deficiency_table AS dt, lots, deficiency_status AS ds
WHERE dt.lot_id = lots.lot_id
AND lots.division_id = proj.division_id
AND lots.project_id = proj.project_id
AND dt.days_old_start_date >= {d '2002-08-08'}
AND dt.deficiency_status_id = ds.deficiency_status_id
AND ds.is_outstanding
AND dt.assigned_supplier_id = '101690'
) AS lot_count_greater_90,
(SELECT count(DISTINCT lots.lot_id) AS lot_count
FROM deficiency_table AS dt, lots, deficiency_status AS ds
WHERE dt.lot_id = lots.lot_id
AND lots.division_id = proj.division_id
AND lots.project_id = proj.project_id
AND dt.deficiency_status_id = ds.deficiency_status_id
AND ds.is_outstanding
AND dt.assigned_supplier_id = '101690'
) AS lot_count
FROM projects AS proj
WHERE proj.division_id = 'GGH'
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
If anyone can see a way to do a group by to do this, then I will be happy to
hear about it, because currently the resultset has to do a separate
(sequential or index) scan of the deficiencies table. The only way I can
see to do a group by would be to break out the aging categories into
separate queries, but that wins me nothing because each query then does its
own scan...
The expected simplified output of this query looks like this:
Project <30 30-60 >=60 lot total <30 30-60 >=60 def total
X 1 2 1 4 5 10 5 20 (if X had 4 lots, each of 5 deficiencies)
Y 1 1 0 2 3 3 0 6 (each has eg 3 deficiencies in project Y)
Terry Fielder
Network Engineer
Great Gulf Homes / Ashton Woods Homes
terry@greatgulfhomes.com
> -----Original Message-----
> From: ch@rodos.fzk.de [mailto:ch@rodos.fzk.de]
> Sent: Wednesday, November 06, 2002 4:54 AM
> To: terry@ashtonwoodshomes.com
> Subject: Re: [SQL] query optimization question
>
>
>
> Dear Terry,
> When I was reading the objective of your query, I expected at
> least one
> GROUP BY clause within. I do not intend to be offensive - not at all,
> but your query very much looks like you're lacking in basic SQL
> knowledge
> (did you receive any other reply?).
> The clause
> WHERE ...
> AND division_id = proj.division_id AND project_id =
> proj.project_id ...
>
> is leading to a JOIN of your projects table to itself.
> I'm pretty sure that's the main reason why the query is slow.
> As I understand your database table design, there are relations about
> divisions, projects, lots, and deficiencies of lots. And you are
> running a master database for all of them.
> I've tried to write two queries (see below) to retrieve the
> information
> you want (BTW I think your first subquery counts the total number of
> lots within the project but not the total number of deficiencies).
> Both queries may still run slow because three tables have to be joined
> (Please try them within the 'psql' interactive terminal first).
> Also, they may not work at all (I could not verify them as I did not
> know about your CREATE TABLE statements and did not have data to put
> in).
> I'm willing to help, so if it's not working this information would be
> very useful to me. I am no SQL guru, so I cannot see any way to put
> these
> two into one. But this looks like an interesting task, maybe we should
> put this topic to the list again as soon as we make the
> single ones run.
>
> Probably, you'll need to create several indexes to speed up.
>
> -- for each project, the total number of deficiencies
> SELECT p.project_id, p.marketing_name, COUNT(d.lot_id) AS def_count
> FROM projects AS p,
> lots AS l LEFT JOIN deficiency_table AS d
> ON ( d.lot_id = l.lot_id )
> WHERE l.division_id = p.division_id
> AND p.division_id = '#variables.local_division_id#'
> GROUP BY p.project_id, p.marketing_name ;
>
> -- for each project, the total number of lots with 1 or more
> deficiencies
> SELECT p.project_id, p.marketing_name, COUNT(l.lot_id) AS
> def_lot_count
> FROM projects AS p,
> lots AS l LEFT JOIN deficiency_table AS d
> ON ( d.lot_id = l.lot_id )
> WHERE l.division_id = p.division_id
> AND p.division_id = '#variables.local_division_id#'
> GROUP BY p.project_id, p.marketing_name HAVING COUNT(d.lot_id) > 0 ;
>
> Once again, no offence intended, but I recommend to read a book on SQL
> soon.
>
> Regards, Christoph
>
> >
> > 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
> >
>
^ permalink raw reply [nested|flat] 15+ messages in thread
* Re: FW: query optimization question
2002-11-06 14:01 FW: query optimization question terry@ashtonwoodshomes.com
@ 2002-11-06 15:11 ` Richard Huxton <dev@archonet.com>
2 siblings, 0 replies; 15+ messages in thread
From: Richard Huxton @ 2002-11-06 15:11 UTC (permalink / raw)
To: terry@ashtonwoodshomes.com; pgsql-sql
On Wednesday 06 Nov 2002 2:01 pm, terry@ashtonwoodshomes.com wrote:
> However, for the total deficiencies I am then splitting up the total into
> aging groups, eg <30, 30-60, 60-90, and >90 days old. The query for that
> looks like the below. But before I paste it in, I would like to optimize
> it, if I could do so with a group by clause I most certainly would, but I
> don't see how I can BECAUSE OF THE AGING BREAKDOWN:
[one sub-query per age-range]
> AND dt.days_old_start_date < {d '2002-10-07'}
> ) AS def_count_less_30,
> AND dt.days_old_start_date >= {d '2002-10-07'}
> AND dt.days_old_start_date < {d '2002-09-07'}
> ) AS def_count_30_60,
Could you not define a function age_range(date) to return the relevant range
text, then group on that text? I've used that before.
--
Richard Huxton
^ permalink raw reply [nested|flat] 15+ messages in thread
* Re: FW: query optimization question
2002-11-06 14:01 FW: query optimization question terry@ashtonwoodshomes.com
@ 2002-11-06 16:22 ` Stephan Szabo <sszabo@megazone23.bigpanda.com>
2002-11-06 16:36 ` Re: FW: query optimization question terry@ashtonwoodshomes.com
2 siblings, 1 reply; 15+ messages in thread
From: Stephan Szabo @ 2002-11-06 16:22 UTC (permalink / raw)
To: terry@ashtonwoodshomes.com; +Cc: pgsql-sql
On Wed, 6 Nov 2002 terry@ashtonwoodshomes.com wrote:
> However, for the total deficiencies I am then splitting up the total into
> aging groups, eg <30, 30-60, 60-90, and >90 days old. The query for that
> looks like the below. But before I paste it in, I would like to optimize
> it, if I could do so with a group by clause I most certainly would, but I
> don't see how I can BECAUSE OF THE AGING BREAKDOWN:
Well, as a first step, I'd suggest using an age function as already
suggested and a temporary table to hold the grouped by values temporarily
and then doing the subselects against that.
Maybe something like (untested):
create temp table defs as
select agefunc(dt.days_old_start_date) as ageval,
count(lots.lot_id) as lots from
deficiency_table as dt, lots, deficiency_status as ds
where dt.lot_id = lots.lot_id
and lots.dividion_id=proj.division_id
and lots.project_id=proj.project_id
and dt.deficiency_status_id=ds.deficiency_status_id
and ts.is_outstanding
and dt.assigned_supplier_id='101690'
group by ageval;
-- same general thing for other repeated queries
select project_id, marketing_name,
(select sum(lots) from defs) as def_count,
(select lots from defs where ageval=0) as def_count_less_30,
(select lots from defs where ageval=1) as def_count_30_60,
...
Since you want 0's instead of nulls, you'd probably need to do
a coalesce for the subselects, and this will go through the
probably 5 or so line temp table rather than the presumably large
other table.
I haven't spent much thought trying to force it down into a
single query, but that seems a bit harder.
^ permalink raw reply [nested|flat] 15+ messages in thread
* Re: FW: query optimization question
2002-11-06 14:01 FW: query optimization question terry@ashtonwoodshomes.com
2002-11-06 16:22 ` Re: FW: query optimization question Stephan Szabo <sszabo@megazone23.bigpanda.com>
@ 2002-11-06 16:36 ` terry@ashtonwoodshomes.com
2002-11-06 16:44 ` Re: FW: query optimization question Stephan Szabo <sszabo@megazone23.bigpanda.com>
0 siblings, 1 reply; 15+ messages in thread
From: terry@ashtonwoodshomes.com @ 2002-11-06 16:36 UTC (permalink / raw)
To: 'Stephan Szabo' <sszabo@megazone23.bigpanda.com>; +Cc: pgsql-sql
Actually, come to think of it, just the implementation of re-querying a
temporary table could alone significantly improve performance, because the
temp table would:
a) have fewer records to scan on the subselects
b) not require any joins
Thanks!
Terry Fielder
Network Engineer
Great Gulf Homes / Ashton Woods Homes
terry@greatgulfhomes.com
> -----Original Message-----
> From: pgsql-sql-owner@postgresql.org
> [mailto:pgsql-sql-owner@postgresql.org]On Behalf Of Stephan Szabo
> Sent: Wednesday, November 06, 2002 11:22 AM
> To: terry@ashtonwoodshomes.com
> Cc: Postgresql Sql Group (E-mail)
> Subject: Re: FW: [SQL] query optimization question
>
>
> On Wed, 6 Nov 2002 terry@ashtonwoodshomes.com wrote:
>
> > However, for the total deficiencies I am then splitting up
> the total into
> > aging groups, eg <30, 30-60, 60-90, and >90 days old. The
> query for that
> > looks like the below. But before I paste it in, I would
> like to optimize
> > it, if I could do so with a group by clause I most
> certainly would, but I
> > don't see how I can BECAUSE OF THE AGING BREAKDOWN:
>
> Well, as a first step, I'd suggest using an age function as already
> suggested and a temporary table to hold the grouped by values
> temporarily
> and then doing the subselects against that.
>
> Maybe something like (untested):
> create temp table defs as
> select agefunc(dt.days_old_start_date) as ageval,
> count(lots.lot_id) as lots from
> deficiency_table as dt, lots, deficiency_status as ds
> where dt.lot_id = lots.lot_id
> and lots.dividion_id=proj.division_id
> and lots.project_id=proj.project_id
> and dt.deficiency_status_id=ds.deficiency_status_id
> and ts.is_outstanding
> and dt.assigned_supplier_id='101690'
> group by ageval;
>
> -- same general thing for other repeated queries
>
> select project_id, marketing_name,
> (select sum(lots) from defs) as def_count,
> (select lots from defs where ageval=0) as def_count_less_30,
> (select lots from defs where ageval=1) as def_count_30_60,
> ...
>
> Since you want 0's instead of nulls, you'd probably need to do
> a coalesce for the subselects, and this will go through the
> probably 5 or so line temp table rather than the presumably large
> other table.
>
> I haven't spent much thought trying to force it down into a
> single query, but that seems a bit harder.
>
>
> ---------------------------(end of
> broadcast)---------------------------
> TIP 6: Have you searched our list archives?
>
> http://archives.postgresql.org
>
^ permalink raw reply [nested|flat] 15+ messages in thread
* Re: FW: query optimization question
2002-11-06 14:01 FW: query optimization question terry@ashtonwoodshomes.com
2002-11-06 16:22 ` Re: FW: query optimization question Stephan Szabo <sszabo@megazone23.bigpanda.com>
2002-11-06 16:36 ` Re: FW: query optimization question terry@ashtonwoodshomes.com
@ 2002-11-06 16:44 ` Stephan Szabo <sszabo@megazone23.bigpanda.com>
0 siblings, 0 replies; 15+ messages in thread
From: Stephan Szabo @ 2002-11-06 16:44 UTC (permalink / raw)
To: terry@ashtonwoodshomes.com; +Cc: pgsql-sql
On Wed, 6 Nov 2002 terry@ashtonwoodshomes.com wrote:
> Actually, come to think of it, just the implementation of re-querying a
> temporary table could alone significantly improve performance, because the
> temp table would:
> a) have fewer records to scan on the subselects
> b) not require any joins
Yeah, that's what I was thinking. However the example I gave was
bogus. I realized that I needed to do more, then forgot before sending.
> > Maybe something like (untested):
> > create temp table defs as
> > select agefunc(dt.days_old_start_date) as ageval,
> > count(lots.lot_id) as lots from
> > deficiency_table as dt, lots, deficiency_status as ds
> > where dt.lot_id = lots.lot_id
> > and lots.dividion_id=proj.division_id
> > and lots.project_id=proj.project_id
> > and dt.deficiency_status_id=ds.deficiency_status_id
> > and ts.is_outstanding
> > and dt.assigned_supplier_id='101690'
> > group by ageval;
You'll almost certainly need to add projects as proj in the from clause,
proj.project_id in the select clause and group by (and possibly
division_id - I can't quite tell if that's a composite key).
> > -- same general thing for other repeated queries
> >
> > select project_id, marketing_name,
> > (select sum(lots) from defs) as def_count,
> > (select lots from defs where ageval=0) as def_count_less_30,
In these you'd want to limit it to the appropriate rows from defs
by project_id (and possibly division_id).
^ permalink raw reply [nested|flat] 15+ messages in thread
* Re: query optimization question
2002-11-06 14:01 FW: query optimization question terry@ashtonwoodshomes.com
@ 2002-11-06 16:44 ` Masaru Sugawara <rk73@sea.plala.or.jp>
2002-11-06 17:10 ` Re: query optimization question terry@ashtonwoodshomes.com
2002-11-06 17:11 ` Re: query optimization question Masaru Sugawara <rk73@sea.plala.or.jp>
2 siblings, 2 replies; 15+ messages in thread
From: Masaru Sugawara @ 2002-11-06 16:44 UTC (permalink / raw)
To: terry@ashtonwoodshomes.com; +Cc: pgsql-sql
On Wed, 6 Nov 2002 09:01:49 -0500
<terry@ashtonwoodshomes.com> wrote:
> If anyone can see a way to do a group by to do this, then I will be happy to
> hear about it, because currently the resultset has to do a separate
> (sequential or index) scan of the deficiencies table. The only way I can
> see to do a group by would be to break out the aging categories into
> separate queries, but that wins me nothing because each query then does its
> own scan...
>
> The expected simplified output of this query looks like this:
> Project <30 30-60 >=60 lot total <30 30-60 >=60 def total
> X 1 2 1 4 5 10 5 20 (if X had 4 lots, each of 5 deficiencies)
> Y 1 1 0 2 3 3 0 6 (each has eg 3 deficiencies in project Y)
>
The following query may be one of the ways, but I cannot confirm whether
it goes well or not.
SELECT
project_id,
marketing_name,
COUNT(lots.lot_id) AS def_count,
COUNT(CASE WHEN dt.days_old_start_date < {d '2002-10-07'}
THEN lots.lot_id ELSE NULL END) AS def_count_less_30,
COUNT(CASE WHEN dt.days_old_start_date >= {d '2002-10-07'}
AND dt.days_old_start_date < {d '2002-09-07'}
THEN lots.lot_id ELSE NULL END) AS def_count_30_60,
COUNT(CASE WHEN dt.days_old_start_date >= {d '2002-09-07'}
AND dt.days_old_start_date < {d '2002-08-08'}
THEN lots.lot_id ELSE NULL END) AS def_count_60_90,
COUNT(CASE WHEN dt.days_old_start_date >= {d '2002-08-08'}
THEN lots.lot_id ELSE NULL END) AS def_count_greater_90,
COUNT(DISTINCT(CASE WHEN
dt.days_old_start_date < {d '2002-10-07'}
THEN lots.lot_id ELSE NULL END )) AS lot_count_less_30,
COUNT(DISTINCT(CASE WHEN
dt.days_old_start_date >= {d '2002-10-07'}
AND dt.days_old_start_date < {d '2002-09-07'}
THEN lots.lot_id ELSE NULL END )) AS lot_count_30_60,
COUNT(DISTINCT(CASE WHEN
dt.days_old_start_date >= {d '2002-09-07'}
AND dt.days_old_start_date < {d '2002-08-08'}
THEN lots.lot_id ELSE NULL END )) AS lot_count_60_90,
COUNT(DISTINCT(CASE WHEN
dt.days_old_start_date >= {d '2002-08-08'}
THEN lots.lot_id ELSE NULL END )) AS lot_count_greater_90,
COUNT(DISTINCT lots.lot_id) AS lot_count
FROM
(SELECT * FROM deficiency_table
WHERE assigned_supplier_id = '101690') AS dt,
(SELECT * FROM deficiency_status,
WHERE ds.is_outstanding) AS ds,
(SELECT * FROM projects
WHERE proj.division_id = 'GGH') AS proj
lots
WHERE
dt.lot_id = lots.lot_id
AND lots.division_id = proj.division_id
AND lots.project_id = proj.project_id
AND dt.deficiency_status_id = ds.deficiency_status_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
Regards,
Masaru Sugawara
^ permalink raw reply [nested|flat] 15+ messages in thread
* Re: query optimization question
2002-11-06 14:01 FW: query optimization question terry@ashtonwoodshomes.com
2002-11-06 16:44 ` Re: query optimization question Masaru Sugawara <rk73@sea.plala.or.jp>
@ 2002-11-06 17:10 ` terry@ashtonwoodshomes.com
1 sibling, 0 replies; 15+ messages in thread
From: terry@ashtonwoodshomes.com @ 2002-11-06 17:10 UTC (permalink / raw)
To: 'Masaru Sugawara' <rk73@sea.plala.or.jp>; +Cc: pgsql-sql
That looks really promising as a possibility, however I think you intended
to add a group by clause.
Terry Fielder
Network Engineer
Great Gulf Homes / Ashton Woods Homes
terry@greatgulfhomes.com
> -----Original Message-----
> From: Masaru Sugawara [mailto:rk73@sea.plala.or.jp]
> Sent: Wednesday, November 06, 2002 11:44 AM
> To: terry@ashtonwoodshomes.com
> Cc: pgsql-sql@postgresql.org
> Subject: Re: [SQL] query optimization question
>
>
> On Wed, 6 Nov 2002 09:01:49 -0500
> <terry@ashtonwoodshomes.com> wrote:
>
> > If anyone can see a way to do a group by to do this, then I
> will be happy to
> > hear about it, because currently the resultset has to do a separate
> > (sequential or index) scan of the deficiencies table. The
> only way I can
> > see to do a group by would be to break out the aging categories into
> > separate queries, but that wins me nothing because each
> query then does its
> > own scan...
> >
> > The expected simplified output of this query looks like this:
> > Project <30 30-60 >=60 lot total <30
> 30-60 >=60 def total
> > X 1 2 1 4 5 10 5
> 20 (if X had 4 lots, each of 5 deficiencies)
> > Y 1 1 0 2 3 3 0
> 6 (each has eg 3 deficiencies in project Y)
> >
>
>
> The following query may be one of the ways, but I cannot
> confirm whether
> it goes well or not.
>
>
> SELECT
> project_id,
> marketing_name,
> COUNT(lots.lot_id) AS def_count,
> COUNT(CASE WHEN dt.days_old_start_date < {d '2002-10-07'}
> THEN lots.lot_id ELSE NULL END) AS
> def_count_less_30,
> COUNT(CASE WHEN dt.days_old_start_date >= {d '2002-10-07'}
> AND dt.days_old_start_date < {d
> '2002-09-07'}
> THEN lots.lot_id ELSE NULL END) AS
> def_count_30_60,
> COUNT(CASE WHEN dt.days_old_start_date >= {d '2002-09-07'}
> AND dt.days_old_start_date < {d
> '2002-08-08'}
> THEN lots.lot_id ELSE NULL END) AS
> def_count_60_90,
> COUNT(CASE WHEN dt.days_old_start_date >= {d '2002-08-08'}
> THEN lots.lot_id ELSE NULL END) AS
> def_count_greater_90,
> COUNT(DISTINCT(CASE WHEN
> dt.days_old_start_date <
> {d '2002-10-07'}
> THEN lots.lot_id ELSE NULL END ))
> AS lot_count_less_30,
> COUNT(DISTINCT(CASE WHEN
> dt.days_old_start_date >=
> {d '2002-10-07'}
> AND dt.days_old_start_date < {d
> '2002-09-07'}
> THEN lots.lot_id ELSE NULL END ))
> AS lot_count_30_60,
> COUNT(DISTINCT(CASE WHEN
> dt.days_old_start_date >=
> {d '2002-09-07'}
> AND dt.days_old_start_date < {d
> '2002-08-08'}
> THEN lots.lot_id ELSE NULL END ))
> AS lot_count_60_90,
> COUNT(DISTINCT(CASE WHEN
> dt.days_old_start_date >=
> {d '2002-08-08'}
> THEN lots.lot_id ELSE NULL END ))
> AS lot_count_greater_90,
> COUNT(DISTINCT lots.lot_id) AS lot_count
> FROM
> (SELECT * FROM deficiency_table
> WHERE assigned_supplier_id = '101690') AS dt,
> (SELECT * FROM deficiency_status,
> WHERE ds.is_outstanding) AS ds,
> (SELECT * FROM projects
> WHERE proj.division_id = 'GGH') AS proj
> lots
> WHERE
> dt.lot_id = lots.lot_id
> AND lots.division_id = proj.division_id
> AND lots.project_id = proj.project_id
> AND dt.deficiency_status_id = ds.deficiency_status_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
>
>
>
>
> Regards,
> Masaru Sugawara
>
^ permalink raw reply [nested|flat] 15+ messages in thread
* Re: query optimization question
2002-11-06 14:01 FW: query optimization question terry@ashtonwoodshomes.com
2002-11-06 16:44 ` Re: query optimization question Masaru Sugawara <rk73@sea.plala.or.jp>
@ 2002-11-06 17:11 ` Masaru Sugawara <rk73@sea.plala.or.jp>
1 sibling, 0 replies; 15+ messages in thread
From: Masaru Sugawara @ 2002-11-06 17:11 UTC (permalink / raw)
To: Masaru Sugawara <rk73@sea.plala.or.jp>; +Cc: pgsql-sql
On Thu, 07 Nov 2002 01:44:25 +0900
I wrote <rk73@sea.plala.or.jp> wrote:
> On Wed, 6 Nov 2002 09:01:49 -0500
> <terry@ashtonwoodshomes.com> wrote:
>
> > If anyone can see a way to do a group by to do this, then I will be happy to
> > hear about it, because currently the resultset has to do a separate
> > (sequential or index) scan of the deficiencies table. The only way I can
> > see to do a group by would be to break out the aging categories into
> > separate queries, but that wins me nothing because each query then does its
> > own scan...
> >
> > The expected simplified output of this query looks like this:
> > Project <30 30-60 >=60 lot total <30 30-60 >=60 def total
> > X 1 2 1 4 5 10 5 20 (if X had 4 lots, each of 5 deficiencies)
> > Y 1 1 0 2 3 3 0 6 (each has eg 3 deficiencies in project Y)
> >
>
>
> The following query may be one of the ways, but I cannot confirm whether
> it goes well or not.
>
>
There are some misspelling in FROM clause. Now they are fixed.
SELECT
project_id,
marketing_name,
COUNT(lots.lot_id) AS def_count,
COUNT(CASE WHEN dt.days_old_start_date < {d '2002-10-07'}
THEN lots.lot_id ELSE NULL END) AS def_count_less_30,
COUNT(CASE WHEN dt.days_old_start_date >= {d '2002-10-07'}
AND dt.days_old_start_date < {d '2002-09-07'}
THEN lots.lot_id ELSE NULL END) AS def_count_30_60,
COUNT(CASE WHEN dt.days_old_start_date >= {d '2002-09-07'}
AND dt.days_old_start_date < {d '2002-08-08'}
THEN lots.lot_id ELSE NULL END) AS def_count_60_90,
COUNT(CASE WHEN dt.days_old_start_date >= {d '2002-08-08'}
THEN lots.lot_id ELSE NULL END) AS def_count_greater_90,
COUNT(DISTINCT(CASE WHEN
dt.days_old_start_date < {d '2002-10-07'}
THEN lots.lot_id ELSE NULL END )) AS lot_count_less_30,
COUNT(DISTINCT(CASE WHEN
dt.days_old_start_date >= {d '2002-10-07'}
AND dt.days_old_start_date < {d '2002-09-07'}
THEN lots.lot_id ELSE NULL END )) AS lot_count_30_60,
COUNT(DISTINCT(CASE WHEN
dt.days_old_start_date >= {d '2002-09-07'}
AND dt.days_old_start_date < {d '2002-08-08'}
THEN lots.lot_id ELSE NULL END )) AS lot_count_60_90,
COUNT(DISTINCT(CASE WHEN
dt.days_old_start_date >= {d '2002-08-08'}
THEN lots.lot_id ELSE NULL END )) AS lot_count_greater_90,
COUNT(DISTINCT lots.lot_id) AS lot_count
FROM
(SELECT * FROM deficiency_table
WHERE assigned_supplier_id = '101690') AS dt,
(SELECT * FROM deficiency_status
WHERE is_outstanding) AS ds,
(SELECT * FROM projects
WHERE division_id = 'GGH') AS proj,
lots
WHERE
dt.lot_id = lots.lot_id
AND lots.division_id = proj.division_id
AND lots.project_id = proj.project_id
AND dt.deficiency_status_id = ds.deficiency_status_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
Regards,
Masaru Sugawara
^ permalink raw reply [nested|flat] 15+ messages in thread
* Re: query optimization question
@ 2002-11-06 15:58 Christoph Haller <ch@rodos.fzk.de>
0 siblings, 0 replies; 15+ messages in thread
From: Christoph Haller @ 2002-11-06 15:58 UTC (permalink / raw)
To: terry@greatgulfhomes.com; +Cc: pgsql-sql
Now that I've given your problem more thoughts (and searched for similar
stuff),
I think what you need is generating a cross table resp. pivot table.
Related to this, I am thinking of a query using Conditional Expressions
like
COUNT ( CASE WHEN ... THEN 1 ELSE NULL) in order to use GROUP BY.
Together with Richard's idea of using a function age_range(date) it
seems
realizable. I'm not yet ready to make a more detailed proposal, but you
might
want to think about it in the meantime, too.
Regards, Christoph
^ permalink raw reply [nested|flat] 15+ messages in thread
* Re: query optimization question
@ 2002-11-07 08:57 Christoph Haller <ch@rodos.fzk.de>
2002-11-07 23:18 ` Re: query optimization question terry@ashtonwoodshomes.com
2002-11-08 14:22 ` Re: query optimization question Masaru Sugawara <rk73@sea.plala.or.jp>
0 siblings, 2 replies; 15+ messages in thread
From: Christoph Haller @ 2002-11-07 08:57 UTC (permalink / raw)
To: terry@ashtonwoodshomes.com; +Cc: pgsql-sql
> SELECT
> project_id,
> marketing_name,
> COUNT(lots.lot_id) AS def_count,
> COUNT(CASE WHEN dt.days_old_start_date < {d '2002-10-07'}
> THEN lots.lot_id ELSE NULL END) AS
def_count_less_30,
> COUNT(CASE WHEN dt.days_old_start_date >= {d '2002-10-07'}
> AND dt.days_old_start_date < {d '2002-09-07'}
> THEN lots.lot_id ELSE NULL END) AS
def_count_30_60,
> COUNT(CASE WHEN dt.days_old_start_date >= {d '2002-09-07'}
> AND dt.days_old_start_date < {d '2002-08-08'}
> THEN lots.lot_id ELSE NULL END) AS
def_count_60_90,
> COUNT(CASE WHEN dt.days_old_start_date >= {d '2002-08-08'}
> THEN lots.lot_id ELSE NULL END) AS
def_count_greater_90,
> COUNT(DISTINCT(CASE WHEN
> dt.days_old_start_date < {d '2002-10-07'}
> THEN lots.lot_id ELSE NULL END )) AS
lot_count_less_30,
> COUNT(DISTINCT(CASE WHEN
> dt.days_old_start_date >= {d '2002-10-07'}
> AND dt.days_old_start_date < {d '2002-09-07'}
> THEN lots.lot_id ELSE NULL END )) AS
lot_count_30_60,
> COUNT(DISTINCT(CASE WHEN
> dt.days_old_start_date >= {d '2002-09-07'}
> AND dt.days_old_start_date < {d '2002-08-08'}
> THEN lots.lot_id ELSE NULL END )) AS
lot_count_60_90,
> COUNT(DISTINCT(CASE WHEN
> dt.days_old_start_date >= {d '2002-08-08'}
> THEN lots.lot_id ELSE NULL END )) AS
lot_count_greater_90,
> COUNT(DISTINCT lots.lot_id) AS lot_count
> FROM
> (SELECT * FROM deficiency_table
> WHERE assigned_supplier_id = '101690') AS dt,
> (SELECT * FROM deficiency_status
> WHERE is_outstanding) AS ds,
> (SELECT * FROM projects
> WHERE division_id = 'GGH') AS proj,
> lots
> WHERE
> dt.lot_id = lots.lot_id
> AND lots.division_id = proj.division_id
> AND lots.project_id = proj.project_id
> AND dt.deficiency_status_id = ds.deficiency_status_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 ;
What about simply replacing ORDER BY proj.project_id ; by
GROUP BY project_id, marketing_name ;
Regards, Christoph
^ permalink raw reply [nested|flat] 15+ messages in thread
* Re: query optimization question
2002-11-07 08:57 Re: query optimization question Christoph Haller <ch@rodos.fzk.de>
@ 2002-11-07 23:18 ` terry@ashtonwoodshomes.com
1 sibling, 0 replies; 15+ messages in thread
From: terry@ashtonwoodshomes.com @ 2002-11-07 23:18 UTC (permalink / raw)
To: 'Christoph Haller' <ch@rodos.fzk.de>; terry@ashtonwoodshomes.com; +Cc: pgsql-sql
Actually, the ORDER BY *must* be replaced by GROUP BY since it is an
aggregate query.
I have implemented it, and the results are startling, I get the same value
repeated for all projects
i.e.:
AS1 AS1-AJAX/SALEM SIDE 3 0 6 7 30 0 216 240
AU3 AU3-RIVERIDGE/AURORA 3 0 6 7 30 0 216 240
AV1 AVALON 3 0 6 7 30 0 216 240
AW1 AW1-AJAX/WESTNEY SIDE 3 0 6 7 30 0 216 240
AWM AW MORTGAGE 3 0 6 7 30 0 216 240
AX1 AX1-ROSE PETAL VALLEY DEV INC 3 0 6 7 30 0 216 240
And this appears to be the correct data row for a row that is the first
(alphabetically) project that has non zero data in it.
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
Terry Fielder
Network Engineer
Great Gulf Homes / Ashton Woods Homes
terry@greatgulfhomes.com
> -----Original Message-----
> From: pgsql-sql-owner@postgresql.org
> [mailto:pgsql-sql-owner@postgresql.org]On Behalf Of Christoph Haller
> Sent: Thursday, November 07, 2002 3:57 AM
> To: terry@ashtonwoodshomes.com
> Cc: pgsql-sql@postgresql.org
> Subject: Re: [SQL] query optimization question
>
>
> > SELECT
> > project_id,
> > marketing_name,
> > COUNT(lots.lot_id) AS def_count,
> > COUNT(CASE WHEN dt.days_old_start_date < {d '2002-10-07'}
> > THEN lots.lot_id ELSE NULL END) AS
> def_count_less_30,
> > COUNT(CASE WHEN dt.days_old_start_date >= {d '2002-10-07'}
> > AND dt.days_old_start_date < {d '2002-09-07'}
> > THEN lots.lot_id ELSE NULL END) AS
> def_count_30_60,
> > COUNT(CASE WHEN dt.days_old_start_date >= {d '2002-09-07'}
> > AND dt.days_old_start_date < {d '2002-08-08'}
> > THEN lots.lot_id ELSE NULL END) AS
> def_count_60_90,
> > COUNT(CASE WHEN dt.days_old_start_date >= {d '2002-08-08'}
> > THEN lots.lot_id ELSE NULL END) AS
> def_count_greater_90,
> > COUNT(DISTINCT(CASE WHEN
> > dt.days_old_start_date < {d '2002-10-07'}
> > THEN lots.lot_id ELSE NULL END )) AS
> lot_count_less_30,
> > COUNT(DISTINCT(CASE WHEN
> > dt.days_old_start_date >= {d '2002-10-07'}
> > AND dt.days_old_start_date < {d '2002-09-07'}
> > THEN lots.lot_id ELSE NULL END )) AS
> lot_count_30_60,
> > COUNT(DISTINCT(CASE WHEN
> > dt.days_old_start_date >= {d '2002-09-07'}
> > AND dt.days_old_start_date < {d '2002-08-08'}
> > THEN lots.lot_id ELSE NULL END )) AS
> lot_count_60_90,
> > COUNT(DISTINCT(CASE WHEN
> > dt.days_old_start_date >= {d '2002-08-08'}
> > THEN lots.lot_id ELSE NULL END )) AS
> lot_count_greater_90,
> > COUNT(DISTINCT lots.lot_id) AS lot_count
> > FROM
> > (SELECT * FROM deficiency_table
> > WHERE assigned_supplier_id = '101690') AS dt,
> > (SELECT * FROM deficiency_status
> > WHERE is_outstanding) AS ds,
> > (SELECT * FROM projects
> > WHERE division_id = 'GGH') AS proj,
> > lots
> > WHERE
> > dt.lot_id = lots.lot_id
> > AND lots.division_id = proj.division_id
> > AND lots.project_id = proj.project_id
> > AND dt.deficiency_status_id = ds.deficiency_status_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 ;
>
> What about simply replacing ORDER BY proj.project_id ; by
> GROUP BY project_id, marketing_name ;
>
> Regards, Christoph
>
>
> ---------------------------(end of
> broadcast)---------------------------
> TIP 4: Don't 'kill -9' the postmaster
>
^ permalink raw reply [nested|flat] 15+ messages in thread
* Re: query optimization question
2002-11-07 08:57 Re: query optimization question Christoph Haller <ch@rodos.fzk.de>
@ 2002-11-08 14:22 ` Masaru Sugawara <rk73@sea.plala.or.jp>
1 sibling, 0 replies; 15+ messages in thread
From: Masaru Sugawara @ 2002-11-08 14:22 UTC (permalink / raw)
To: Christoph Haller <ch@rodos.fzk.de>; +Cc: pgsql-sql
On Thu, 07 Nov 2002 09:57:27 +0100
Christoph Haller <ch@rodos.fzk.de> wrote:
> > ORDER BY proj.project_id ;
>
> What about simply replacing ORDER BY proj.project_id ; by
> GROUP BY project_id, marketing_name ;
You're right. Thanks a lot.
Regards,
Masaru Sugawara
^ permalink raw reply [nested|flat] 15+ messages in thread
* Re: query optimization question
@ 2002-11-08 10:01 Christoph Haller <ch@rodos.fzk.de>
0 siblings, 0 replies; 15+ messages in thread
From: Christoph Haller @ 2002-11-08 10:01 UTC (permalink / raw)
To: terry@ashtonwoodshomes.com; +Cc: pgsql-sql
>
> 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
^ permalink raw reply [nested|flat] 15+ messages in thread
end of thread, other threads:[~2002-11-08 14:22 UTC | newest]
Thread overview: 15+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2002-11-05 00:19 query optimization question terry@ashtonwoodshomes.com
2002-11-06 13:56 Re: query optimization question terry@greatgulfhomes.com
2002-11-06 14:01 FW: query optimization question terry@ashtonwoodshomes.com
2002-11-06 15:11 ` Richard Huxton <dev@archonet.com>
2002-11-06 16:22 ` Stephan Szabo <sszabo@megazone23.bigpanda.com>
2002-11-06 16:36 ` terry@ashtonwoodshomes.com
2002-11-06 16:44 ` Stephan Szabo <sszabo@megazone23.bigpanda.com>
2002-11-06 16:44 ` Masaru Sugawara <rk73@sea.plala.or.jp>
2002-11-06 17:10 ` terry@ashtonwoodshomes.com
2002-11-06 17:11 ` Masaru Sugawara <rk73@sea.plala.or.jp>
2002-11-06 15:58 Re: query optimization question Christoph Haller <ch@rodos.fzk.de>
2002-11-07 08:57 Re: query optimization question Christoph Haller <ch@rodos.fzk.de>
2002-11-07 23:18 ` terry@ashtonwoodshomes.com
2002-11-08 14:22 ` Masaru Sugawara <rk73@sea.plala.or.jp>
2002-11-08 10:01 Re: query optimization question Christoph Haller <ch@rodos.fzk.de>
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