Project

General

Profile

Bug #21047

Or'ing parameters doesn't work well for nova

Added by Robert Illingworth 11 months ago. Updated about 2 months ago.

Status:
New
Priority:
Normal
Assignee:
-
Target version:
Start date:
10/05/2018
Due date:
% Done:

0%

Estimated time:
Duration:

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 about 2 months ago

  • Target version changed from 3.1 to 3.2


Also available in: Atom PDF