The query is like:

select count(*)
from test1 as r
WHERE r.testid in (select p.testid from test2 p where p.testname IS NULL)
OR r.testid > (SELECT MAX(p2.testid)FROM test2 p2);

but when I rewrite it as UNION it works. What is the problem of this query?

  • 3
    Please Edit your question and add the create table statements for the tables in question (including all indexes) and the execution plan generated using explain (analyze, verbose). Formatted text please, no screen shots – a_horse_with_no_name Apr 27 '17 at 12:08

This is a common issue - PostgreSQL planner cannot use indexes (it's not able to optimize OR, when OR is used for subselects) and rewriting with UNION helps. Last year Tom Lane wrote a patch, but as far as I know, this patch was not merged yet.

| improve this answer | |

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.