I have a table A in my main-db (primary key ID) and table B in a proxy-db. From my table A I can access table B via proxy. The performance is ok.
If I make a joined select using these two tables I get a strange performance issue:
Select a.id, a.number, b.description from a,b where a.id = b.id order by a.id
run very, very slow (in my case up to 60 sec!!!)
Select a.id, a.number, b.description from a,b where a.id = b.id order by a.id, b.description
runs "normal" fast (less then 1 sec)
This means: If I order via the primary key of table A, it is slow, if I order by another field (or add another field to the primary key) it is much faster
Does anybody else has this issue? Is there something wrong with by select-statement? Or is it a known bug?
Finaly I found the solution. By preparing testdata and comparing the DBs I found one difference between a new created DB and my production DB:
I don't now why, but in my production DB there was this option (perhaps from older updates):
SET OPTION "PUBLIC"."optimization_goal"='Response-time';
After the change to (the DEFAULT value):
SET OPTION "PUBLIC"."optimization_goal"='All-rows';
Now my DB is fast again :-)
answered 17 Dec '13, 08:24