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