> But you wrote that in both cases the query is:
that was indeed yet another tipo, hope to do better in the future.
> I'm willing to continue to investigate, but only if you prepare a
> reproducer,
Thanks for your starter script. Please find attached 2 scripts which
now illustrates two troubles.
repro1.sql is a slight evolution of yours. When I play with david size
(as described in the comments) you will see plan going from nested loop
to sequential scan. Also note that the partition wise join is likely
working. This illustrate my initial problem: the sequential scan is not
going to work fine on my workload (david too large). How to suggest
postgres to use a nested loop here ? (suspect playing with costs should
help)
repro2.sql now I changed the table layout (similar to my setup) to
reproduce the partition wise join which does not triggers. I added a
partition column, and a unique index to be able to mimic a primary key.
Now partition wise (in my local docker vanilla postgres 15.3) does not
work. Eventually, if I do small batch, then the merge is working fast.
That's an other, unrelated problem.
> I suggest you do that directly, not through JDBC. Perhaps the JDBC
> connection pool does something funny (like connection pooling and
> resetting settings).
I can tell jdbc was working, and likely the reason might be in my
current table setup.