Re: Determining scan types

On Wed, Jul 04, 2001 at 07:31:53AM +0100, Richard Huxton wrote:
: Philip Molter wrote:
:
: > : For the purposes of setting SEQSCAN try something like:
: > :
: > : SET ENABLE_SEQSCAN TO OFF;
: >
: > Well, I turned it off for the entire database (since right now, we're
: > only using the db for this one application), but I lose the benefit of
: > seqscans in situations where it's appropriate.  That's why I was
: > wondering if there's anyway to tell the optimizer to prefer index scans
: > over sequential scans when it has a choice.
:
: AFAIK that's exactly what ENABLE_SEQSCAN does.

It appears that ENABLE_SEQSCAN prevents the optimizer from using
seqscans at all.  That's not really the behavior I want.  I just don't
want it to be using them when it should be using index scans instead.

: Try wrapping the one or two key queries with ...OFF and ...ON - should
: give you the best of both worlds.

Yeah, except I'm trying to write generally database agnostic (SQL92)
code.  It's rather idiotic that I have to tell the optimizer not only
in which order to join the tables (that I can live with), but also what
the best method is to join them with.

: I've always managed to persuade PG to make the "right" choices, but
: there are a whole set of parameters you can tweak if absolutely
: necessary. The ENABLE_SEQSCAN is a pretty blunt tool but is the easiest
: for (me :-) to understand. See the runtime configuration chapter for
: details and check the mail archives for examples of use.

Yeah, I don't mind tweaking, but the query optimizer, even with as many
tables as this join has, should be able to recognize when unique
indices are available and use them.  I mean, it chops the aggregate row
count from 3000+ down to 11 (that's just shutting off seq scans).
That's pretty significant and symbolizes a problem with the optimizer
when it can't figure it out.  Furthermore, it *was* using indices and
something happened to make it stop.  I still have no clue what that
was.

It's rather irrelevant anyway.  Turning off ENABLE_SEQSCAN appears to
make the optimizer work much more like I intended, so off it stays.

* Philip Molter
* DataFoundry.net
* http://www.datafoundry.net/
* philip@datafoundry.net