When I try the flattened (where 'SELECT c2.cid FROM c2' subquery is changed
into a join), I get a decent plan.
It executes in 0.04 secs (my laptop, debug build) and reads 2766 rows.
Playing with semi-join opimization flags in optimizer switch allows one to get
several query plans which all do 1600-2700 reads and are done in 0.03-0.04
sec.
To re-iterate:
When I try the flattened (where 'SELECT c2.cid FROM c2' subquery is changed
into a join), I get a decent plan.
It executes in 0.04 secs (my laptop, debug build) and reads 2766 rows.
Playing with semi-join opimization flags in optimizer switch allows one to get
several query plans which all do 1600-2700 reads and are done in 0.03-0.04
sec.