Please take this survey to help us learn more about how you use third party tools. Your input is greatly appreciated!

How to force early evaluation of non-correlated subqueries

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.

Comments

  • 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.

  • SruthiASruthiA Employee

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

Leave a Comment

BoldItalicStrikethroughOrdered listUnordered list
Emoji
Image
Align leftAlign centerAlign rightToggle HTML viewToggle full pageToggle lights
Drop image/file

Can't find what you're looking for? Search the Vertica Documentation, Knowledge Base, or Blog for more information.