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

Re: disabling seq scans

From: Sean Shanny <shannyconsulting(at)earthlink(dot)net>
To: "Marcus Andree S(dot) Magalhaes" <marcus(dot)magalhaes(at)vlinfo(dot)com(dot)br>
Cc: pgsql-jdbc(at)postgresql(dot)org
Subject: Re: disabling seq scans
Date: 2004-04-01 21:21:54
Message-ID: 406C87F2.7020509@earthlink.net (view raw or flat)
Thread:
Lists: pgsql-jdbc
See answers in-line....

Marcus Andree S. Magalhaes wrote:

>Yes, that is what I thought 1st...
>But, since we're using pooled connections, we must be extra
>careful and ensure all connections have the enable_seqscan
>reset to its default.
>So, the main question is:
>
>Do the pooling system resets the "enable_seqscan" variable
>when the connections are delivered to the clients or we
>*must* do it ourselves?
>  
>
We use pooling as well and there is NO logic in the pool that would 
reset anything on a connection.  It is up to the user of the connection 
to set it back to an acceptable state, hence the call to set it back to 
true prior to returning the connection back to the pool.

>Another issue: when enable_seqscan is false, I found that
>the sequential scanning is performed normally (as in
>"select count (*) from <table>") but have them any performance
>issues?
>  
>
Not sure I understand you question.  I set enable_indexscan=false in my 
code as I am working with a 350GB DB with some tables having 100 million 
rows.  With the hardware we have it is sometimes faster to do a full 
table scan the bounce around with indexes.

>Last one... This time, specifically to the java side: what
>do you think about adding new methods, say, setEnableSeqScan(boolean)
>and getEnableSeqScan() to our (by our I mean
>postgres) drivers?
>  
>
This would be up the folks that write the code, I am just a user. :-)

>  
>
>>I do the following in several reports I run.....
>>
>>statement = m_conn.createStatement();
>>statement.executeUpdate( "set enable_seqscan = false" );
>>do your thing....
>>statement.executeUpdate( "set enable_seqscan = true" );
>>
>>--sean
>>
>>Marcus Andree S. Magalhaes wrote:
>>
>>    
>>
>>>Hi, guys,
>>>
>>>We're experiencing a little problem with one of our queries.
>>>It isn't using an index specially created for it. When we
>>>disable seq scans with psql, we can ensure the query finishes
>>>much faster than without using index, as it should be.
>>>
>>>So, whats the best procedure in this case, but when have a
>>>JDBC based client? Do we mess around with planner
>>>settings even when all other queries are using the best
>>>index for them?
>>>
>>>Is it safe (but some may find ugly) to issue a command to
>>>disable seq scanning from the java side?
>>>
>>>Since we're using the pooled connection classes that comes
>>>with the JDBC3 driver, once a connection is got from the pool,
>>>do we need to explicitly set seq scanning to true? This is
>>>assuming the later option is the more recommended one...
>>>
>>>TIA
>>>
>>>
>>>
>>>---------------------------(end of
>>>broadcast)--------------------------- TIP 8: explain analyze is your
>>>friend
>>>
>>>
>>>
>>>      
>>>
>>---------------------------(end of broadcast)---------------------------
>>TIP 9: the planner will ignore your desire to choose an index scan if
>>your
>>      joining column's datatypes do not match
>>    
>>
>
>
>
>
>  
>

In response to

Responses

pgsql-jdbc by date

Next:From: Kris JurkaDate: 2004-04-01 22:11:08
Subject: Re: disabling seq scans
Previous:From: João Paulo F. DinizDate: 2004-04-01 21:15:50
Subject: problems with datestyle

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