Subject Re: [firebird-support] Group By performance in large tables.
Author unordained
---------- Original Message -----------
From: "Liz Andrew" <rodent@...>
> Int_Values
> (
> PointID bigint,
> TransID bitint,
> TransTime timestamp,
> PointValue int
> )
> I have a descending index on TransTime and PointID.
> But I want to be able to read multiple or all tags in one go and when using
> Group By I find the performance drops dramatically:
> WHERE PointID in (1.... etc)
> AND TransTime < '2009-10-22 15:20:42'
> Group By PointID
------- End of Original Message -------

Is that a single DESC index on (transtime,pointid)? If so, there's no hope of FB
using that index for the group-by. A multi-column index can only be used if the
fields you're sorting by are the first few of the index definition.

How many IN() items do you usually search by? And on which version of FB? (The
optimizer rules about IN() changed at some point, as I recall, and could make a
big difference.)

Can you post the PLAN generated for your queries?