I've encoutered similar issues myself (with UNION so far), so I tried
to build a simple test case, which may or may not cover Clemens's
case.
Test case 1 and 2 illustrates the issue, and case 3-9 are variations.
My observation: Looks like the optimizer cannot be close friends with
both UNION and
Ah, forgot one query:
WHERE IN is of course fast when we supply id's directly, but not when
they are wrapped as array and UNNEST'ed in query 6. (previous post
from me)
-- Test 6b: Fast. WHERE IN(explicit id list)
SELECT * FROM (
SELECT * FROM table1
UNION
SELECT * FROM table1
