Re: Forcing postgres NOT to use sequential scan, trough JDBC

On Mon, Mar 9, 2009 at 5:02 AM, Mario Splivalo <mario(dot)splivalo(at)megafon(dot)hr>wrote:

> Mario Splivalo wrote:
>
>> 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.

Dave

>
> Mike
>
> --
> Sent via pgsql-jdbc mailing list (pgsql-jdbc(at)postgresql(dot)org)
> To make changes to your subscription:
> http://www.postgresql.org/mailpref/pgsql-jdbc
>