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 [EMAIL PROTECTED] > -----Original Message----- > From: [EMAIL PROTECTED] > [mailto:pgsql-sql-owner@;postgresql.org]On Behalf Of Stephan Szabo > Sent: Wednesday, November 06, 2002 11:22 AM > To: [EMAIL PROTECTED] > Cc: Postgresql Sql Group (E-mail) > Subject: Re: FW: [SQL] query optimization question > > > On Wed, 6 Nov 2002 [EMAIL PROTECTED] 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 > ---------------------------(end of broadcast)--------------------------- TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
