id,summary,reporter,owner,description,type,status,priority,milestone,component,version,resolution,keywords,cc 139,"Default selectivity returned when geometry used for restricted join comes from another table, but accurate when from WKT",mose.andre,mcayland," '''What steps will reproduce the problem?''' -- TEST CASE CREATE TABLE points(point_id bigint, point geometry); -- Some points in the unit square INSERT INTO points (point_id, point) VALUES (generate_series(1, 100000), ST_MakePoint(random(), random())); CREATE INDEX points_point_index ON points USING gist (point); ANALYZE points; CREATE TABLE shapes(shape_id bigint, shape geometry); -- A shape covering a bit of the unit square INSERT INTO shapes (shape_id, shape) VALUES (1, GeomFromText('POLYGON((0 0, 0 .1, .1 .1, .1 0, 0 0))')); -- Restricted join (1) EXPLAIN ANALYZE SELECT count(*) FROM points INNER JOIN shapes ON ST_Contains(shape, point) WHERE shape_id=1; -- Constant restriction (2) EXPLAIN ANALYZE SELECT count(*) FROM points WHERE ST_Contains(GeomFromText('POLYGON((0 0, 0 .1, .1 .1, .1 0, 0 0))'), point); '''What is the expected output? What do you see instead?''' In case (1) I expected to see an accurate estimate of the rows returned but see 1 instead of something ~1000. For more information, please see: [http://postgis.refractions.net/pipermail/postgis-devel/2008-September/003547.html http://postgis.refractions.net/pipermail/postgis-devel/2008-September/003547.html] '''What version of the product are you using? On what operating system?''' postgis_full_version | POSTGIS='1.3.3' GEOS='3.0.0-CAPI-1.4.1' PROJ='Rel. 4.5.0, 22 Oct 2006' USE_STATS version | PostgreSQL 8.3.3 on x86_64-redhat-linux-gnu, compiled by GCC gcc (GCC) 3.4.6 20060404 (Red Hat 3.4.6-9) '''Please provide any additional information below.''' Another instance of someone running into a similar thing is in this thread. Maybe there's a reason we shouldn't be expecting real selectivity estimate in this case... [http://postgis.refractions.net/pipermail/postgis-users/2009-March/023043.html http://postgis.refractions.net/pipermail/postgis-users/2009-March/023043.html] ",enhancement,closed,medium,PostGIS 1.4.3,postgis,,wontfix,,