[postgis-users] Intersection of 2 large maps overlap at a corner

[Paragon Corporation]

Looks like its not using the ptype index and opting to do a sequential scan.
That could be right since you are using a NOT IN and if the stats say most
of the table fits the condition it ouwld rightfully not use the index.
 
Did you run a 
 
vacuum analyze st_quat_geopy;
vacuum analyze sj_100k_geopy;
 
I recall seeing some strange behavior with IN clause, but I dismissed it as
an issue with constraint exclusion not being able to optimize the IN since I
was using it in context of constraint exclusion and it wasn't behaving as I
was expecting.
 
If vacuum analyzing doesn't help 
 
try changing your query to use 
 
NOT (a = b OR a = c) 
 
instead of 
 
a NOT IN(b,c)
 
Hope that helps,
Regina

  _____  

From: postgis-users-bounces at postgis.refractions.net
[mailto:postgis-users-bounces at postgis.refractions.net] On Behalf Of Bob and
Deb
Sent: Friday, October 24, 2008 6:04 PM
To: PostGIS Users Discussion
Subject: Re: [postgis-users] Intersection of 2 large maps overlap at a
corner


I tried reversing the test and I'm still having the same problem.  Here is
some information I got from pgadmin3 concerning my tables:

CREATE TABLE sf_quat_geopy
(
  gid serial NOT NULL,
  sf_quat_ numeric,
  sf_quat_id numeric,
  ptype character varying(35),
  name character varying(200),
  liq character varying(8),
  liq_source bigint,
  area numeric,
  perimeter numeric,
  e00_centro numeric,
  e00_centr1 numeric,
  the_geom geometry,
  CONSTRAINT sf_quat_geopy_pkey PRIMARY KEY (gid),
  CONSTRAINT enforce_dims_the_geom CHECK (ndims(the_geom) = 2),
  CONSTRAINT enforce_geotype_the_geom CHECK (geometrytype(the_geom) =
'MULTIPOLYGON'::text OR the_geom IS NULL),
  CONSTRAINT enforce_srid_the_geom CHECK (srid(the_geom) = 32610)
)

CREATE INDEX sf_quat_geopy_key
  ON sf_quat_geopy
  USING btree
  (ptype);

CREATE INDEX sf_quat_geopy_the_geom_gist
  ON sf_quat_geopy
  USING gist
  (the_geom);

CREATE TABLE sj_100k_geopy
(
  gid serial NOT NULL,
  sj_geol_ numeric,
  sj_geol_id numeric,
  ptype character varying(35),
  name character varying(200),
  area numeric,
  perimeter numeric,
  e00_centro numeric,
  e00_centr1 numeric,
  the_geom geometry,
  CONSTRAINT sj_100k_geopy_pkey PRIMARY KEY (gid),
  CONSTRAINT enforce_dims_the_geom CHECK (ndims(the_geom) = 2),
  CONSTRAINT enforce_geotype_the_geom CHECK (geometrytype(the_geom) =
'MULTIPOLYGON'::text OR the_geom IS NULL),
  CONSTRAINT enforce_srid_the_geom CHECK (srid(the_geom) = 32610)
)

CREATE INDEX sj_100k_geopy_ptype
  ON sj_100k_geopy
  USING btree
  (ptype);

CREATE INDEX sj_100k_geopy_the_geom_gist
  ON sj_100k_geopy
  USING gist
  (the_geom);


 Nested Loop  (cost=0.00..8226.67 rows=4509 width=15085)
   Join Filter: _st_intersects(geob.the_geom, geoq.the_geom)
   ->  Seq Scan on sj_100k_geopy geob  (cost=0.00..993.00 rows=7407
width=6918)
         Filter: ((ptype)::text <> ALL ('{Qls,Qls?}'::text[]))
   ->  Index Scan using sf_quat_geopy_the_geom_gist on sf_quat_geopy geoq
(cost=0.00..0.96 rows=1 width=8167)
         Index Cond: (geob.the_geom && geoq.the_geom)
         Filter: ((geoq.ptype)::text <> ALL ('{br,br?}'::text[]))



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