Default selectivity returned when geometry used for restricted join comes from another table, but accurate when from WKT

[mose.andre]

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

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