Re: disabling seq scans

On Thu, 1 Apr 2004, 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...

What's likely happening is that the queries that are choosing the right
plan (i.e. the other queries) are such obviously bad candidates for a seq
scan that they still get an index scan. It might be that as you increase
the % of the table being read by those other queries that they might
switch to a seq scan too soon for your setup.

I'm assuming you've read the tuning guide here:

http://www.varlena.com/varlena/GeneralBits/Tidbits/perf.html

And upped shared buffers, effect_cache_size, etc...

random page costs of 1.0 to 1.4 make sense for certain setups with lots of
RAM and medium to small datasets that can (mostly) fit in memory. On our
machine with 2G ram at work, that works out to about 1.2 to 1.3 as
optimal.