On Wed, 6 Nov 2002 [EMAIL PROTECTED] 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). ---------------------------(end of broadcast)--------------------------- TIP 5: Have you checked our extensive FAQ? http://www.postgresql.org/users-lounge/docs/faq.html
