[postgis-devel] Issue 139 in postgis: Default selectivity returned when geometry used for restricted join comes from another table, but accurate when from WKT

[codesite-noreply at google.com]

Status: New
Owner: ----

New issue 139 by mose.andre: Default selectivity returned when geometry  
used for restricted join comes from another table, but accurate when from  
WKT
http://code.google.com/p/postgis/issues/detail?id=139

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


--
You received this message because you are listed in the owner
or CC fields of this issue, or because you starred this issue.
You may adjust your issue notification preferences at:
http://code.google.com/hosting/settings