0

I'd like to disable nested loops for a specific JDBC connection string (batch java application), using the PostgreSQL community driver

Is it possible to do so?

I tried this way, in vain:

jdbc:postgresql://xxx.yyy.zzz:5432/mydb?options=enable_nestloop=off
Improve this question
3
  • 3
    Why do you think you need that? It would be much better to optimize the statements
    – user1822
    Feb 19, 2020 at 10:54
  • It's possible to run set enable_nestloop to false before executing the query, and setting it back to true before returning the connection to the pool. Far preferably to enabling it for everything!
    – Colin 't Hart
    Feb 19, 2020 at 13:51
  • @a_horse_with_no_name Sometimes this is how you optimize the statements. Maybe you could change (for example) random_page_cost instead, but what if there is no globally optimal setting for that? Now you revert to the same question, how to change that other setting just for one connection, or query, or user, or whatever.
    – jjanes
    Feb 19, 2020 at 16:07

1 Answer 1

Reset to default
3

You can use the options connection parameter, as explained here:

  • options = String

Specify 'options' connection initialization parameter.

The value of this property may contain spaces or other special characters, and it should be properly encoded if provided in the connection URL. Spaces are considered to separate command-line arguments, unless escaped with a backslash (\); \\ represents a literal backslash.

So that should look like

options=-c%20enable_nestloop%3Doff

But I share the horse's concern that this may be a bad idea, and you should rather tune your queries.

Improve this answer
2
  • Thank you @laurenz-albe (and a-horse-with no-name) for your answers. I will try your synthax! The problem that I encountered is that the query is running fine on all our environments, except 1! The query doesn't look ugly, and with non-DB constraints, it is hard to obtain a rewrite!
    – Thomas B
    Feb 19, 2020 at 13:08
  • 2
    @ThomasB: but this setting affects all queries you run, not just the problematic one
    – user1822
    Feb 19, 2020 at 13:28

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge that you have read and understand our privacy policy and code of conduct.

Not the answer you're looking for? Browse other questions tagged or ask your own question.