Re: disabling seq scans

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
>>
>>
>
>
>
>
>
>