Hints with pre-ANSI joins

In Vertica 9.1, very few of these hints work with pre-ANSI joins, like placing predicates in WHERE clauses. Has this or will this change? Unfortunately a reporting tool used by my company doesn't generate ANSI joins and the lack of hints for these queries make tuning difficult. Thanks.

Optimizing Query Performance and Resource Pool Tuning
@rbankula @bat

Answers

  • ChuckBChuckB Employee

    So there's more than one good question in here. The first being about non-ANSI joins (just multiple tables in the WHERE clause), and the second is how to apply hints if you don't control how the SQL is generated. I'd suggest starting with a quick read about how "directed queries" work. Maybe this blog:
    https://www.vertica.com/blog/tell-the-optimizer-you-have-a-better-planba-p234038/

    Here's an example to get you started. See how the optimizer can generate the ANSI joins for you, and put an initial set of hints in place?
    => CREATE DIRECTED QUERY OPTIMIZER "nonANSI" SELECT * FROM fact, dim WHERE fact.fk = dim.pk;
    => SELECT annotated_query FROM v_catalog.directed_queries WHERE query_name = 'nonANSI';
    SELECT /*+syntactic_join,verbatim*/ fact.fk AS fk, fact.c0 AS c0, fact.c1 AS c1, dim.pk AS pk, dim.c2 AS c2 FROM (public.fact AS fact/*+projs('public.fact')*/ JOIN /*+Distrib(L,L),JType(M)*/ public.dim AS dim/*+projs('public.dim')*/ ON (fact.fk = dim.pk))

    From there, what you can do if you can't change the SQL coming out of the tool, is override the SQL that gets executed to get the plan you want.

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.