legacy legacy - 3 months ago 15
SQL Question

PostgreSQL slow JOIN with CASE statement

In my database I have a table that contains ~3500 records and as a part of more complicated query I've tried to perform inner join on itself using "CASE" condition just as you can see below.

SELECT *
FROM some_table AS t1
JOIN some_table AS t2 ON t1.type = t2.type
AND CASE
WHEN t1.type = 'ab' THEN t1.first = t2.first
WHEN t1.type = 'cd' THEN t1.second = t2.second
-- Column type contains only one of 2 possible varchar values
END;


The problem is this query is performed for 3.2 - 4.5 seconds while next request is performed in 40 - 50 milliseconds.

SELECT *
FROM some_table AS t1
JOIN some_table AS t2 ON t1.type = t2.type
AND (t1.first = t2.first OR t1.second = t2.second)


Also according to the execution plan in first case database processes ~5.8 millions of records while table contains only ~3500. There are next indexes on this table: (id), (type), (type, first), (type, second).

We are using next version:
PostgreSQL 9.4.5 on x86_64-unknown-linux-gnu, compiled by gcc (GCC) 4.4.7 20120
313 (Red Hat 4.4.7-16), 64-bit

Any ideas why PostgreSQL works so weird in this case?

Answer

Test this one:

select *
from
    some_table as t1
    join
    some_table as t2 on
        t1.type = t2.type
        and
        (
            t1.type = 'ab' and t1.first = t2.first
            or
            t1.type = 'cd' and t1.second = t2.second
        )
Comments