How do I tell postgres not to use sequential scan, trough jdbc?
I tried to include 'SET enable_seqscan TO false;' at the begining of the plpgsql function, but that seems to work only if I call the function trough the psql, and only for the subsequent calls in the current session. I 'know' that postgres is using sequential scan because function call takes cca 400ms. When I force postgres not to use sequential scan, the function takes around 5-10ms.
I even tried, on the same connection, to issue 'SET enable_seqscan TO false;' trough jdbc before I call my function, but execution time is still around 400ms.
As it turns out, one can say 'SET senable_seqscan TO false;' at the begining of the plpgsql function. The problem was that I had setup Tomcat so that it keeps only 10 persistent connections. When I set it up to keep 100 persistent connections the load was minimal.
As I said this is not the best way to do this. There is a postgresql list specifically to address these issues; the performance list. The problem with what you are doing is: 1 this affects all queries, and 2 is indicative of not having the database in an optimal state of tune. I strongly suggest you post your problem to the performance list and solve this properly, as this solution will likely cause other problems.
