Subject How to index?
Author Ales Smodis
Hello everyone,

I have a table with 16.2 million records:

create table item
site SmallInt not null,
order Integer not null,
sn SmallInt not null,
ean Decimal(13),
qty Decimal(9, 3),
price Decimal(14, 2),
constraint item_pk primary key (site, order, sn)

and I want to run the following query:

select ean, site, sum(qty), sum(price)
from item
group by ean, site;

This takes about 4.5 minutes, the plan being: plan sort ((item natural))
Then I want to be clever about it and do this:

create index item_ean_site on item (ean, site);

and rerun the query, now the plan being: plan (item order
item_ean_site). Now it takes a little over 14 minutes. Shouldn't now the
query actually run a bit faster, if it doesn't have any additional
sorting to do?
Should I create a different index? Should I use an index at all?

I'm using Firebird 1.5.2.rc1.