Seeking clarity on JOIN SPILL and Query Retry

JoshLitt

I'm unclear on how the per-query hint /*+set_vertica_options(EE, ENABLE_JOIN_SPILL)*/ actually works, and how it and JOIN SPILL in general relate to the MaxQueryRetries setting.

I had thought that the ENABLE_JOIN_SPILL hint would permit a retry of the query which encounters "JOIN Inner did not fit in memory" with an disk-based hash. But I've seen forum comments from 2013 and 2014 indicating that such spilling and retry is always enabled, and this hint actually forces the query to spill. Contradicting that is my experience running queries with that hint, which encounter the "did not fit in memory" error on the first try.

I'm further seeing these queries are not retried with spill, and just fail. I believe that is because I have MaxQueryRetries set to 0, but want to confirm. Clearly if ENABLE_JOIN_SPILL forces the spill, then I wouldn't need to retry.

I'm using Vertica 11.1.1 (and moving to 23.4).

Bryan_H

For current and global/default settings of EnableJoinSpill:
select * from configuration_parameters where parameter_name = 'EnableJoinSpill';
My understanding is that join spill replans the query and thus counts as a retry, so setting MaxQueryRetries = 0 should disable join spill regardless of enable setting. You can override EnableJoinSpill and MaxQueryRetries at session level if you expect join spill:
alter session set EnableJoinSpill = 1;
alter session set MaxQueryRetries = 1;

SruthiA

@JoshLitt : please check if you have EnableJoinSpill parameter set to 0. If so then join spill doesn't work by default.

JoshLitt

@SruthiA I don't understand your recommendation. Where would EnableJoinSpill be set to 0?
As I mentioned, we use a the per-query hint to the EE for ENABLE_JOIN_SPILL, but we don't seem to get that behavior. The vertica.log mentions retrying with spill, but then doesn't actually do it. I assume this is because we have MaxQueryRetries set to 0, but want to confirm how that setting affects JOIN SPILLs. I'm also not sure if the hint is supposed to enable spilling from the start, or enable spilling on retry.