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:

    , 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?


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).

  • What's the question?
    – mustaccio
    Jul 9, 2018 at 14:52
  • Please show the definition of the function - specifically whether it's marked immutable or stable
    – user1822
    Jul 9, 2018 at 19:55

1 Answer 1


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
FROM NewMainTable nmt
    JOIN table1 t1
        ON nmt.MyJoinField1 = t1.col1
    JOIN table6 t6
        ON nmt.MyJoinField6 = t6.col1;
  • Specifially, PERSISTED computed columns. Jul 9, 2018 at 17:52
  • Postgres has neither computed columns, nor persisted computed columns. Turning the derived table into a CTE won't improve the performance either
    – user1822
    Jul 9, 2018 at 19:55
  • My bad, I didn't even see this was postgres - it showed up in my SQL filter. Best bet then is to create actual fields and fill them in your INSERT code (trigger, maybe?). Jul 9, 2018 at 20:03
  • Rewriting subquery (as I showed in question) to the CTE gave me an acceleration of 4 times, thanks! Using of temp tables has about the same speed in Postgres, even slightly slower. Jul 10, 2018 at 7:49
  • w00t! Glad it helped. And many thanks to @horse for Postgres-ifying the code. Jul 10, 2018 at 16:18

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

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