SQL query performance with OR clause

Question

This SQL query takes 10 second:

SELECT * FROM A a
JOIN B b on a.idB = b.idB
JOIN C c on b.idC = c.idC
JOIN D d on c.idD = d.idD
JOIN E e  ON((e.perimeterId = a.idA AND e.level = 3) 
          OR (e.perimeterId = b.idB AND e.level = 2) 
          OR (e.perimeterId = c.idC AND e.level = 1)
          OR (e.perimeterId = d.idD AND e.level = 0))

Changing OR clause with CASE, the query takes 3 second:

SELECT * FROM A a
JOIN B b on a.idB = b.idB
JOIN C c on b.idC = c.idC
JOIN D d on c.idD = d.idD
JOIN E e  ON (
               CASE e.level
                    when 3 then a.idA
                    when 2 then b.idB
                    when 1 then c.idC
                    when 0 then d.idD
               END
             ) = e.perimeterId 

If i execute the query with separate OR clause, the queries are executed instantly Withour OR clause :

SELECT * FROM A a
JOIN B b on a.idB = b.idB
JOIN C c on b.idC = c.idC
JOIN D d on c.idD = d.idD
JOIN E e  ON (e.perimeterId = a.idX AND e.level = X) 

How to rewrite my query to be execute instantly or with high performance possible?

Answer 1

You only want columns from e, so I would write this using exists:

SELECT e.*
FROM E e
WHERE EXISTS (SELECT 1
              FROM a
              WHERE e.perimeterId = a.idA AND e.level = 3
             ) OR
      EXISTS (SELECT 1
              FROM a JOIN
                   b
                   ON a.idB = b.idB
              WHERE e.perimeterId = b.idA AND e.level = 2
             ) OR
      EXISTS (SELECT 1
              FROM a JOIN
                   b
                   ON a.idB = b.idB JOIN
                   c
                   ON c.idC = b.idC
              WHERE e.perimeterId = c.idA AND e.level = 1
             ) OR
      EXISTS (SELECT 1
              FROM a JOIN
                   b
                   ON a.idB = b.idB JOIN
                   c
                   ON c.idC = b.idC JOIN
                   d
                   ON d.idD = c.idD
              WHERE e.perimeterId = d.idA AND e.level = 0
             );

I'm not 100% sure that the JOINs are needed in the subqueries, but I've left them in anyway.