Skip site navigation (1) Skip section navigation (2)

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

From: Dave Cramer <pg(at)fastcrypt(dot)com>
To: Mario Splivalo <mario(dot)splivalo(at)megafon(dot)hr>
Cc: pgsql-jdbc(at)postgresql(dot)org
Subject: Re: Forcing postgres NOT to use sequential scan, trough JDBC
Date: 2009-03-09 12:37:00
Message-ID: 491f66a50903090537l181d9995r6841ec3071a74246@mail.gmail.com (view raw or flat)
Thread:
Lists: pgsql-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
>

In response to

pgsql-jdbc by date

Next:From: Mario SplivaloDate: 2009-03-09 13:36:32
Subject: Query much slower when run from postgres function
Previous:From: Mario SplivaloDate: 2009-03-09 09:02:09
Subject: Re: Forcing postgres NOT to use sequential scan, trough JDBC

Privacy Policy | About PostgreSQL
Copyright © 1996-2014 The PostgreSQL Global Development Group