[postgis] SET ENABLE_SEQSCAN = OFF

[Paul Ramsey]

Frank Warmerdam wrote:
> 
> I would like to know a little more about the effects of setting ENABLE_SEQSCAN
> to OFF.  It seems like I do need to do this to have the GiST spatial index
> kick in.  (It takes a query on 14lines out of 12000 VMAP0 coastlines for a
> small area from about 9s to a fraction of a second!)

Totally. The speed on the 1Million record table is pretty cool too. Well
under a second.
 
> I had originally though this was something I would do once at the same
> time I applied the PostGIS types to a database, but I see it is specific
> to a particular connection to the database, not persistent.

Right.
 
> The documentation warns that leaving this set to OFF can cause sub-optimal
> performance for a variety of other query types.  How serious is this?

Serious enough. There are many times where a sequence scan is more
efficient than an index scan, particularly on small tables, but also in
cases where every record needs to be touched to get a result (imagine
travering a whole btree for *each* value in a field: ouch). Working in
the context of a moderately complex database with a bunch of different
tables and links and joins, having seqscan forced off all the time could
cause a reasonably significant decrease in speed. 

The good news (I think) is that setting the seqscans off in one session
does not turn them off in others. So from the point of view of our
pure-spatial queries of the database, we are not causing any trouble at
all. 

> Is there anyway we can force spatial indexes to be used but not affect
> decisions on other index types?  This whole thing seems to make use of
> spatial indexes substantially more problematic.

I think we are reaching the point where we go back to the [HACKERS] list
and ask what we can do about this. Fundamentally, this is a problem with
how the planner scores the likelihood of a query on our GiST index doing
better with the index than with a sequence scan, and that problem has to
do with the kind of statistics the planner is gathering on the table.
There does not seem to have been any thought given to having the planner
work right on anything except btrees, hence we have to force index scans
whenever we work with the GiST indexes. Without guidance from the
experts on HACKERS though, there is nothing we can do ourselves: it's
pretty deep voodoo.

> However, the good news is that I have modified OGR to pass attribute
> and spatial queries through to PostGIS, and the performance is great
> when selecting a small area out of a large dataset.

That's fantastic news!


-- 
      __
     /
     | Paul Ramsey
     | Refractions Research
     | Email: pramsey at refractions.net
     | Phone: (250) 885-0632
     \_

To unsubscribe from this group, send an email to:
postgis-unsubscribe at yahoogroups.com

 

Your use of Yahoo! Groups is subject to http://docs.yahoo.com/info/terms/