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
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?
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.
CREATE INDEX CONCURRENTLY ..., but it does not work in transaction blocks (and functions because functions are executed in transaction blocks).