How to force early evaluation of non-correlated subqueries

colin_loghin

Vertica transforms the following subquery in a join which degrades performance.
On the other hand, a filter on the fact table is much faster. In this case the difference is between a 53 sec response and a 7 sec response.

--slower 53 sec
select count(*) from big_fat_table a11
where a11.instance_date_sid between '20170101' and '20170131'
and a11.FK1||'-'|| a11.FK2 IN (select FK1||'-'|| FK2 from dim where column in ('value');

--fast option 7 sec
select count(*) from big_fat_table a11
where a11.instance_date_sid between '20170101' and '20170131'
and a11.FK1||'-'|| a11.FK2 IN ('127072400-3125', '127073399-278');

But I have not been able to find a way to tell the optimizer to evaluate my subquery first and then use a filter. Is there a way to achieve this ( optimizer hint? ) for the greater performance benefit?

Of course, programmatically i can achieve this in 2 steps , but let's say we have to use a BI tool like Microstrategy and we don't have the 2 steps option.

Thank you.

pdhokte

Any solution for this problem?
I noticed that sometimes the plan does apply filter before joining.
But most of the times it does not, resulting in poor performance.

SruthiA

@pdhokte Could you please open a support case and share explain plan?