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.
