FWIW, the attached is the dusted-off version of a part of a stalled development of mine, which unconditionally(!) creates on-the-fly statistics on VALUES list. It seems to work for certain cases, although the planning time increases significantly.
=$ CREATE TABLE t1 AS SELECT a, a * 2 AS b FROM generate_series(0, 99999) a; =$ CREATE INDEX ON t1 (a); > perl q.pl(*) | psql
*: q.pl: > print "explain analyze select b from t1 join (values "; > foreach $i (0..10000) { > print ", " if ($i > 0); > printf("(%d)", $i/10 + 1000); > } > print ") as v(v) on (v.v = t1.a);";
We end up abusing the option of creating temp tables and analyzing them to get around the pain of queries going off the rails because of bad stats or lack of stats on values. I believe most/all of the core team and perhaps most contributors are against query hints in general (with some very good reasons) but it might be amazing to have the option to incur the planning time cost in some cases at least.
For my case, I think the changes coming in PG v13 or maybe v14 for joins being helped by multivariate statistics will mitigate the pain point.