>I have a query with an aggregate function (AVG) that I believe is the cause for the very slow
>execution times on the the first instance. The first execution will take around 2.5 minutes and
>subsequent queries will take around 6 seconds. I am using FB 2.5.2 in SuperServer mode with a
>page size of 8192. My configuration shows my buffers at 4000, but gstat reports 0, and if so
>then the buffers would be the default of 2048.
>
>Here's the query:
>select bidprices.contkey, avg(bidprices.price) AvgPrice
>from bidprices
>join master on master.jobid=bidprices.jobid
>join biditems on (biditems.jobid=bidprices.jobid and biditems.sequence=bidprices.sequence)
>left outer join payitems on (payitems.payid=biditems.payid and
> (payitems.payid in (select payid from biditems where biditems.jobid=32829)))
>join bidders on (bidders.jobid=bidprices.jobid and bidders.contkey=bidprices.contkey)
>where (master.biddate>='9/17/2006') and (master.distnum=10) and
> (payitems.itemnum is not null) and (bidprices.contkey=4426) and
> (bidprices.price<>0)
>group by bidprices.contkey, biditems.payid
>order by bidprices.contkey, biditems.payid
>
>The stats for the 1st execution are:
>5769887 fetches, 4 marks, 61392 reads, 4 writes.
>0 inserts, 0 updates, 0 deletes, 1592970 index, 0 seq.
>Delta memory: 1637868 bytes
>Total execution time: 0:02:41 (hh:mm:ss)
>
>2nd time:
>5769887 fetches, 2 marks, 61338 reads, 2 writes.
>0 inserts, 0 updates, 0 deletes, 1592757 index, 0 seq.
>Delta memory: -148 bytes
>Total execution time: 5.168s
>
>Here are the generated plan(s):
>PLAN (BIDITEMS INDEX (BIDITEMS_PAYID, BIDITEMS_JOBID))
>PLAN SORT (JOIN (JOIN (JOIN (BIDPRICES INDEX (BIDPRICES_CONTKEY2), MASTER INDEX (RDB$PRIMARY4),
>BIDITEMS INDEX (RDB$PRIMARY12)), PAYITEMS INDEX (RDB$PRIMARY11)), BIDDERS INDEX >(RDB$PRIMARY13)))
>
>If anybody has any ideas on how to speed up the initial query, I'd be most grateful.

Well, Eric,
your query confuses me:

1) Why do you group on something not an output field? I didn't think that was allowed.
2) LEFT JOIN followed by referencing to a field in the table being NOT NULL makes the LEFT JOIN in reality become a (inner) JOIN, sometimes that's OK for optimization, but you haven't mentioned that being the case here.
3) IN <subselect> is a potential slowdown

Try changing to something like:

with biditems32829 as
(select distinct payid from biditems where jobid=32829)
select bp.contkey, avg(bp.price) AvgPrice
from bidprices bp
join master m on m.jobid=bp.jobid
join biditems bi on bi.jobid=bp.jobid
and bi.sequence=bp.sequence
join biditems32829 bi32829 on bi32829.payid = bi.payid
join payitems pi on pi.payid=bi.payid
join bidders b on b.jobid=bp.jobid
and b.contkey=bp.contkey
where m.biddate>='9/17/2006'
and m.distnum=10
and pi.itemnum is not null
and bp.contkey=4426
and bi.price<>0
group by bp.contkey
order by bp.contkey

Please report back whether this helps or not (gets the right result and is quick). As I said, your query confuses me, so I'm uncertain whether my query gets the right result.

HTH,
Set