[postgis] SET ENABLE_SEQSCAN = OFF

classic Classic list List threaded Threaded
4 messages Options
Reply | Threaded
Open this post in threaded view
|

[postgis] SET ENABLE_SEQSCAN = OFF

Frank Warmerdam
Folks,

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

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.

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?

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.

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.

Best regards,

---------------------------------------+--------------------------------------
I set the clouds in motion - turn up   | Frank Warmerdam, [hidden email]
light and sound - activate the windows | http://pobox.com/~warmerdam
and watch the world go round - Rush    | Geospatial Programmer for Rent

To unsubscribe from this group, send an email to:
[hidden email]

 

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



Reply | Threaded
Open this post in threaded view
|

Re: [postgis] SET ENABLE_SEQSCAN = OFF

Paul Ramsey-2


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: [hidden email]
     | Phone: (250) 885-0632
     \_

To unsubscribe from this group, send an email to:
[hidden email]

 

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



Reply | Threaded
Open this post in threaded view
|

Re: [postgis] SET ENABLE_SEQSCAN = OFF

David Blasby-3
> 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.

There are functions that you can set that affect how the planner will
calculate search costs and, hence, whether or not an index is used.  We
may be able to just give a HUGE cost to the CPU time involved in
computing "&&" and it may always optimize by using the GiST index.

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

I second that!

dave

To unsubscribe from this group, send an email to:
[hidden email]

 

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



Reply | Threaded
Open this post in threaded view
|

[postgis] Distance on a Spheroid

David Blasby-3
In reply to this post by Paul Ramsey-2
We had requests to do length calculations on a spheroid.  They are now
available, thanks to Mark Sondheim and David Skae at Geographic Data BC.


New type, SPHEROID.  Specify in OpenGIS WKT format.

SPHEROID("name",<semi-major axis>, <inverse flattening)

ie. SPHEROID("GRS_1980",6378137,298.257222101)

as per the WKT spec, you can use "[]" instead of "()"

If your geometry is in LAT/LONG format you can use:

length3d_ellipsoid(GEOMETRY, SPHEROID)
        -- find the length of all the LINESTRINGs in GEOMETRY

length_ellipsoid  (GEOMETRY, SPHEROID)
        -- find the length of all the LINESTRINGs in GEOMETRY, ignoring
                Z values.

for example,
select length_spheroid('LINESTRING(-126 49,-126 49.011096139863)',
'SPHEROID["GRS_1980",6378137,298.257222101]');

        gives 1234.000

dave
ps. I'm using the X coordinate to mean Longitude and Y to mean Lattitude

To unsubscribe from this group, send an email to:
[hidden email]

 

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