[postgis-users] invalid join selectivity: 179531936.000000 (and windows testers)

[Paragon Corporation]

Paul C.,

 

Given that you are on windows, if it's not too much trouble for you, can you try upgrading to PostGIS 2.2.0rc1.

 

The issue that Remi-C highlighted, looks like something that the other Paul (pramsey) committed to trunk (PostGIS 2.2)  but did not back-port to 2.1 so you testing on 2.2, would better confirm the fix or –non-fix and maybe he can back-port.

 

I just pushed out the binaries for those for PostgreSQL 9.4 and 9.3 (and 9.2) (both 32-bit and 64-bit) - http://postgis.net/windows_downloads (in the experimental section) – and on final release will push to Application Stackbuilder (though I won't be packaging 9.2 on stackbuilder).  So you just need to pick the relevant RC1 for your postgres and copy over the binaries)

 

And an ALTER EXTENSION postgis UPDATE;

 

As a selfish reason, I'd really like to get some windows people testing PostGIS 2.2 before final release next week or so.  It's been in incubation for quite some time (I think longer than 2.0 in fact) and has lots of new and shiny things (new proj, new GDAL, included SFCGAL for advanced 3D support )  (the stack-builder package will also have a pgRouting 2.1, ogr_fdw, (and pointcloud if I can get the damn thing to compile again))  that PostGIS 2.1 doesn't have.

 

Thanks,

Regina

http://www.postgis.us

http://postgis.net

 

PostGIS Windows package maintainer.

 

 

 

 

 

 

 

 

From: postgis-users-bounces at lists.osgeo.org [mailto:postgis-users-bounces at lists.osgeo.org] On Behalf Of Rémi Cura
Sent: Friday, September 25, 2015 4:20 AM
To: PostGIS Users Discussion <postgis-users at lists.osgeo.org>
Subject: Re: [postgis-users] invalid join selectivity: 179531936.000000

 

Yep,

I had the same problem
https://trac.osgeo.org/postgis/ticket/2543

The issue is that this bug is hard to reproduce.


As a workaround, you can try to drop and recreate the table :
DROP TABLE  
VACUUM FULL ANALYZE

CREATE TABLE 

CREATE INDEX

 

Cheers,

Rémi-C



 

2015-09-25 1:32 GMT+02:00 Paul J. Caritj <caritj at gmail.com <mailto:caritj at gmail.com> >:

Hello,

I am having trouble with what I expected to be a simple query. I have two tables, one of census tracts (with a multipolygon geometry column, srid 4269), and another of sites (with a point geometry column, srid 4269). I want to find the number of sites that are within a tract of more than a given population, so I've tried:

 

SELECT count(sites.id <http://sites.id> ) FROM sites, census_tracts WHERE AND census_tracts.population > 2000 AND ST_Contains(census_tracts.geometry, sites.location);

 

But when I do this I get: psycopg2.InternalError: invalid join selectivity: 179531936.000000.

 

Both of the relevant tables have been analyzed and vacuumed. 

 

The output of postgis_full_version() is 

 

POSTGIS="2.1.8 r13780" GEOS="3.4.2-CAPI-1.8.2 r3924" PROJ="Rel. 4.8.0, 6 March 2012" GDAL="GDAL 1.11.1, released 2014/09/24" LIBXML="2.7.8" LIBJSON="UNKNOWN" RASTER

 

I'm using Windows 7.

 

Does this ring a bell for anyone? 

 

Thanks very much!

 

-Paul


_______________________________________________
postgis-users mailing list
postgis-users at lists.osgeo.org <mailto:postgis-users at lists.osgeo.org> 
http://lists.osgeo.org/cgi-bin/mailman/listinfo/postgis-users

 

-------------- next part --------------
An HTML attachment was scrubbed...
URL: <http://lists.osgeo.org/pipermail/postgis-users/attachments/20150926/b92862fa/attachment.html>