Re: FW: query optimization question

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
>