PostgresSQL default join_collapse_limit=8 / from_collapse_limit=8 causes sub-optimal performance of queries with many joins

[gunterze]

example a query introduced with #4587:

select *
from series s1_0 
join study s2_0 on s2_0.pk=s1_0.study_fk 
join patient p1_0 on p1_0.pk=s2_0.patient_fk 
join dicomattrs ab1_0 on ab1_0.pk=p1_0.dicomattrs_fk 
left join study_query_attrs qa1_0 on s2_0.pk=qa1_0.study_fk and qa1_0.view_id='hideRejected'
join dicomattrs ab2_0 on ab2_0.pk=s2_0.dicomattrs_fk 
left join metadata m1_0 on m1_0.pk=s1_0.metadata_fk 
left join series_query_attrs qa2_0 on s1_0.pk=qa2_0.series_fk and qa2_0.view_id='hideRejected'
join dicomattrs ab3_0 on ab3_0.pk=s1_0.dicomattrs_fk 
where exists(select pi1_0.pk from patient_id pi1_0 where (pi1_0.pat_id='MY_PAT_ID') and pi1_0.patient_fk=p1_0.pk);

executes i.e. 40 seconds with join_collapse_limit=8 and sub-second with join_collapse_limit=12. It is also possible to use

SET join_collapse_limit = 12;
select xxx join yyy...;

to test effect without configuring globally.

Resolution:

• in postgresql.conf configure

from_collapse_limit = 16
join_collapse_limit = 16

- restart the DB

- added `/docker-entrypoint-initdb.d/35_set-collapse-limit.sh`

#!/bin/sh

sed -i -e "s/^#join_collapse_limit = 8/join_collapse_limit = 16/" /var/lib/postgresql/data/postgresql.conf
sed -i -e "s/^#from_collapse_limit = 8/from_collapse_limit = 16/" /var/lib/postgresql/data/postgresql.conf
``

will ensure that configuration on DB initialization with version 5.33.1+