Hints with pre-ANSI joins

kxu

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

ChuckB

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.