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

[Stephen Woodbridge]

Regina,

I came across an optimizer issue with IN of the form:

A IN (B, C) that would not use the index but
A=B or A=C that would use the index.

So you might be on track with your suggestion below. I thought it was 
strange at the time and that I might have been doing something wrong so 
I'm glad you mentioned it also.

-Steve W.


Paragon Corporation wrote:
> 
> 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[]))
> 
> 
> 
> ------------------------------------------------------------------------
> 
> _______________________________________________
> postgis-users mailing list
> postgis-users at postgis.refractions.net
> http://postgis.refractions.net/mailman/listinfo/postgis-users