Scientific Data Management » SAM » SAM-MAIN » SAM Web services

Or'ing together parameters doesn't work very well for NOvA: data_files_param_values has been partitioned, and the query logic ends up something like

select ... from data_files left join data_files_param_values dfpv1 on (data_files.file_id = dfpv1.file_id) left join data_files_param_values dfpv2 on (data_files.file_id = dfpv2.file_id)
where ... and ((dfpv1.param_type_id = X1 and dfpv1.param_value_id = Y1) or (dfpv2.param_type_id = X2 and dfpv2.param_value_id = Y2)

This causes postgres to scan all partitions

If instead we change the query builder to do

select .. from data_files left join (select file_id, param_value_id from data_files_param_values where param_type_id = X1 ) as dfpv1 on (data_files.file_id = dfpv1.file_id)
left join (select file_id, param_value_id from data_files_param_values where param_type_id = X2) as dfpv2 on (data_files.file_id = dfpv2.file_id)
where ... and ( dfpv1.param_value_id = Y1 or dfpv2.param_value_id = Y2 )

then the partition constraint elimination gets rid of the other partitions inside the subqueries, and the final result is much simpler and faster. As far as I can see this makes no difference for non-partitioned tables; they end up with the same execution plan.