Bug #21047
Or'ing parameters doesn't work well for nova
Start date:
10/05/2018
Due date:
% Done:
0%
Estimated time:
Description
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.
History
#1 Updated by Robert Illingworth 6 months ago
- Target version changed from 3.1 to 3.2