Re: disabling seq scans - Mailing list pgsql-jdbc

<snip>

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

Ok. So we should set the connections to a default, accepted state
before using it.

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

Let me try to explain better. I understand that, in some circumstances,
a seq scan can be faster than using indexes, so I'm talking about a
more general, or theoretical point of view.

Consider two queries, identical and causing a sequential scan on a
more or less large table (we have about ~150k records). The system
they're running differ
only in a single setting: enable_indexscan is false in one system and
true in the other. Which one is faster? Or they should _not_ be
affected by this setting?

Sorry if it sounded like a 4th grade question, but I believe I
made my point clearer now. ;-)


>
>>
>>
>>>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
>>>
>>>
>>
>>
>>
>>
>>
>>
>
> ---------------------------(end of broadcast)---------------------------
> TIP 8: explain analyze is your friend