Postgresql – Function in JOIN condition

functionsindexjoin;postgresql

I have big dynamically constructed query with

FROM main_table t
LEFT JOIN table1 t1 ON my_func(t.col1, 1) = t1.col1
...
LEFT JOIN table6 t6 ON my_func(t.col1, 6) = t6.col1

Query is very slow, but if I remove my_func() from JOIN condition, it works ok. Next variant has the same time:

FROM (
  SELECT *
    , my_func(col1, 1) AS join_cond1
    ...
    , my_func(col1, 6) AS join_cond6
  FROM main_table
) t
  LEFT JOIN table1 t1 ON join_cond1 = t1.col1
  ...
  LEFT JOIN table6 t6 ON join_cond6 = t6.col1

How I can speed up the query?

Update

I wrote that I can't create 6 indexes on all the table1...table6, but indexes are needed on main_table only. Sorry for my mistake.

PostgreSQL has functional indexes, and I tried dynamic generation like

EXECUTE 'CREATE INDEX ix_main_table_my_func_'||_number||' ON main_table (my_func(col1, '||_number||'))';

and it was much more quickly than CTE. But I have troubles with deadlock if function is called concurrently from multiple places since CREATE INDEX sets lock. PostgreSQL can CREATE INDEX CONCURRENTLY ..., but it does not work in transaction blocks (and functions because functions are executed in transaction blocks).

Best Answer

Yeah, that's going to be slow. Best would be to pre-chew the data by adding those JOIN fields as computed columns to MainTable. If you don't control that table, you can try rewriting your query with a CTE, though I'm not sure how much it will help:

WITH NewMainTable
AS (
    SELECT t.*
        , my_func(t.col1, 1) as MyJoinField1
        , = my_func(t.col1, 6) as MyJoinField6 
    FROM MainTable t
    )
SELECT *
FROM NewMainTable nmt
    JOIN table1 t1
        ON nmt.MyJoinField1 = t1.col1
    JOIN table6 t6
        ON nmt.MyJoinField6 = t6.col1;
Related Question