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

SruthiA

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