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 '18 at 14:52
  • Please show the definition of the function - specifically whether it's marked immutable or stable – a_horse_with_no_name Jul 9 '18 at 19:55

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;
| improve this answer | |
  • Specifially, PERSISTED computed columns. – Russell Fox Jul 9 '18 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 – a_horse_with_no_name Jul 9 '18 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?). – Russell Fox Jul 9 '18 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. – Evgeny Nozdrev Jul 10 '18 at 7:49
  • w00t! Glad it helped. And many thanks to @horse for Postgres-ifying the code. – Russell Fox Jul 10 '18 at 16:18

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.