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: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED] 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[]))
_______________________________________________ postgis-users mailing list [email protected] http://postgis.refractions.net/mailman/listinfo/postgis-users
