> The RegionID index has a lot of duplicates.
>
> With v1.x this makes for a lousy index for the optimizer.
>
> You have 3 choices:
>
> 1 - try to change your query to be WHERE REGIONID+0 IN (...) -- this
> will allow the optimizer to ignore the index. Yes, it will result in a
> NATURAL table scan but that might be better. If the change makes things
> worse, please forward the new PLAN -- you might need to re-write the
> query to SELECT on Sites and then JOIN from there.
>

PLAN JOIN (S NATURAL,CO INDEX (PK_COMPANY),C INDEX (FK1_CLIENT),V
INDEX (FK1_VACANCY))

------ Performance info ------
Prepare time = 0ms
Execute time = 8m 21s 15ms

(from startup, 1st query issued).

Thanks for the reply Sean. I'll look into your other recommendations.
New index isn't possible as these set of tables are adhoc-queried on
unknown columns (and to add further to the mix may additionally be
joined into child table(s) with ~ 900k rows).

Thanks,
Tom