From: Stephan Szabo <sszabo@megazone23.bigpanda.com>
To: terry@ashtonwoodshomes.com
Cc: 'Postgresql Sql Group (E-mail)' <pgsql-sql@postgresql.org>
Subject: Re: FW: query optimization question
Date: Wed, 6 Nov 2002 08:44:16 -0800 (PST)
Message-ID: <20021106084019.I82713-100000@megazone23.bigpanda.com> (raw)
In-Reply-To: <003c01c285b2$a6b13760$2766f30a@development.greatgulfhomes.com>
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).
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: sszabo@megazone23.bigpanda.com, terry@ashtonwoodshomes.com
Subject: Re: FW: query optimization question
In-Reply-To: <20021106084019.I82713-100000@megazone23.bigpanda.com>
* 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