Thread: Forcing postgres NOT to use sequential scan, trough JDBC
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.
Mike
I would suggest to you that something else is wrong. In order for postgresql to use the most efficient method all of the tuning parameters have to be configured properly. The performance list is a better place to discuss this.
On Fri, Mar 6, 2009 at 5:50 AM, Mario Splivalo <mario.splivalo@megafon.hr> 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.
Mike
--
Sent via pgsql-jdbc mailing list (pgsql-jdbc@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-jdbc
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.
Mike
On Mon, Mar 9, 2009 at 5:02 AM, Mario Splivalo <mario.splivalo@megafon.hr> wrote:
Mario Splivalo wrote: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.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 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
Dave
Mike
--
Sent via pgsql-jdbc mailing list (pgsql-jdbc@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-jdbc