<!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 4.0 Transitional//EN">
<HTML><HEAD>
<META http-equiv=Content-Type content="text/html; charset=us-ascii">
<META content="MSHTML 6.00.6001.18148" name=GENERATOR></HEAD>
<BODY>
<DIV dir=ltr align=left><SPAN class=029450017-25102008><FONT face=Arial
color=#0000ff size=2>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.</FONT></SPAN></DIV>
<DIV dir=ltr align=left><SPAN class=029450017-25102008><FONT face=Arial
color=#0000ff size=2></FONT></SPAN> </DIV>
<DIV dir=ltr align=left><SPAN class=029450017-25102008><FONT face=Arial
color=#0000ff size=2>Did you run a </FONT></SPAN></DIV>
<DIV dir=ltr align=left><SPAN class=029450017-25102008><FONT face=Arial
color=#0000ff size=2></FONT></SPAN> </DIV>
<DIV dir=ltr align=left><SPAN class=029450017-25102008><FONT face=Arial
color=#0000ff size=2>vacuum analyze st_quat_geopy;</FONT></SPAN></DIV>
<DIV dir=ltr align=left><SPAN class=029450017-25102008><SPAN
class=029450017-25102008><FONT face=Arial color=#0000ff size=2>vacuum analyze
<FONT face="Times New Roman"
color=#000000>sj_100k_geopy</FONT>;</FONT></SPAN></SPAN></DIV>
<DIV dir=ltr align=left><SPAN class=029450017-25102008><FONT face=Arial
color=#0000ff size=2></FONT></SPAN> </DIV>
<DIV dir=ltr align=left><SPAN class=029450017-25102008><FONT face=Arial
color=#0000ff size=2>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.</FONT></SPAN></DIV>
<DIV dir=ltr align=left><SPAN class=029450017-25102008><FONT face=Arial
color=#0000ff size=2></FONT></SPAN> </DIV>
<DIV dir=ltr align=left><SPAN class=029450017-25102008><FONT face=Arial
color=#0000ff size=2>If vacuum analyzing doesn't help </FONT></SPAN></DIV>
<DIV dir=ltr align=left><SPAN class=029450017-25102008><FONT face=Arial
color=#0000ff size=2></FONT></SPAN> </DIV>
<DIV dir=ltr align=left><SPAN class=029450017-25102008><FONT face=Arial
color=#0000ff size=2>try changing your query to use </FONT></SPAN></DIV>
<DIV dir=ltr align=left><SPAN class=029450017-25102008><FONT face=Arial
color=#0000ff size=2></FONT></SPAN> </DIV>
<DIV dir=ltr align=left><SPAN class=029450017-25102008><FONT face=Arial
color=#0000ff size=2>NOT (a = b OR a = c) </FONT></SPAN></DIV>
<DIV dir=ltr align=left><SPAN class=029450017-25102008><FONT face=Arial
color=#0000ff size=2></FONT></SPAN> </DIV>
<DIV dir=ltr align=left><SPAN class=029450017-25102008><FONT face=Arial
color=#0000ff size=2>instead of </FONT></SPAN></DIV>
<DIV dir=ltr align=left><SPAN class=029450017-25102008><FONT face=Arial
color=#0000ff size=2></FONT></SPAN> </DIV>
<DIV dir=ltr align=left><SPAN class=029450017-25102008><FONT face=Arial
color=#0000ff size=2>a NOT IN(b,c)</FONT></SPAN></DIV>
<DIV dir=ltr align=left><SPAN class=029450017-25102008><FONT face=Arial
color=#0000ff size=2></FONT></SPAN> </DIV>
<DIV dir=ltr align=left><SPAN class=029450017-25102008><FONT face=Arial
color=#0000ff size=2>Hope that helps,</FONT></SPAN></DIV>
<DIV dir=ltr align=left><SPAN class=029450017-25102008><FONT face=Arial
color=#0000ff size=2>Regina</FONT></SPAN></DIV><BR>
<DIV class=OutlookMessageHeader lang=en-us dir=ltr align=left>
<HR tabIndex=-1>
<FONT face=Tahoma size=2><B>From:</B>
postgis-users-bounces@postgis.refractions.net
[mailto:postgis-users-bounces@postgis.refractions.net] <B>On Behalf Of </B>Bob
and Deb<BR><B>Sent:</B> Friday, October 24, 2008 6:04 PM<BR><B>To:</B> PostGIS
Users Discussion<BR><B>Subject:</B> Re: [postgis-users] Intersection of 2 large
maps overlap at a corner<BR></FONT><BR></DIV>
<DIV></DIV>I tried reversing the test and I'm still having the same
problem. Here is some information I got from pgadmin3 concerning my
tables:<BR><BR>CREATE TABLE sf_quat_geopy<BR>(<BR> gid serial NOT
NULL,<BR> sf_quat_ numeric,<BR> sf_quat_id numeric,<BR> ptype
character varying(35),<BR> name character varying(200),<BR> liq
character varying(8),<BR> liq_source bigint,<BR> area
numeric,<BR> perimeter numeric,<BR> e00_centro numeric,<BR>
e00_centr1 numeric,<BR> the_geom geometry,<BR> CONSTRAINT
sf_quat_geopy_pkey PRIMARY KEY (gid),<BR> CONSTRAINT enforce_dims_the_geom
CHECK (ndims(the_geom) = 2),<BR> CONSTRAINT enforce_geotype_the_geom CHECK
(geometrytype(the_geom) = 'MULTIPOLYGON'::text OR the_geom IS NULL),<BR>
CONSTRAINT enforce_srid_the_geom CHECK (srid(the_geom) =
32610)<BR>)<BR><BR>CREATE INDEX sf_quat_geopy_key<BR> ON
sf_quat_geopy<BR> USING btree<BR> (ptype);<BR><BR>CREATE INDEX
sf_quat_geopy_the_geom_gist<BR> ON sf_quat_geopy<BR> USING
gist<BR> (the_geom);<BR><BR>CREATE TABLE sj_100k_geopy<BR>(<BR> gid
serial NOT NULL,<BR> sj_geol_ numeric,<BR> sj_geol_id
numeric,<BR> ptype character varying(35),<BR> name character
varying(200),<BR> area numeric,<BR> perimeter numeric,<BR>
e00_centro numeric,<BR> e00_centr1 numeric,<BR> the_geom
geometry,<BR> CONSTRAINT sj_100k_geopy_pkey PRIMARY KEY (gid),<BR>
CONSTRAINT enforce_dims_the_geom CHECK (ndims(the_geom) = 2),<BR>
CONSTRAINT enforce_geotype_the_geom CHECK (geometrytype(the_geom) =
'MULTIPOLYGON'::text OR the_geom IS NULL),<BR> CONSTRAINT
enforce_srid_the_geom CHECK (srid(the_geom) = 32610)<BR>)<BR><BR>CREATE INDEX
sj_100k_geopy_ptype<BR> ON sj_100k_geopy<BR> USING btree<BR>
(ptype);<BR><BR>CREATE INDEX sj_100k_geopy_the_geom_gist<BR> ON
sj_100k_geopy<BR> USING gist<BR> (the_geom);<BR><BR><BR> Nested
Loop (cost=0.00..8226.67 rows=4509 width=15085)<BR> Join
Filter: _st_intersects(geob.the_geom, geoq.the_geom)<BR> ->
Seq Scan on sj_100k_geopy geob (cost=0.00..993.00 rows=7407
width=6918)<BR> Filter:
((ptype)::text <> ALL ('{Qls,Qls?}'::text[]))<BR> ->
Index Scan using sf_quat_geopy_the_geom_gist on sf_quat_geopy geoq
(cost=0.00..0.96 rows=1
width=8167)<BR> Index Cond:
(geob.the_geom &&
geoq.the_geom)<BR> Filter:
((geoq.ptype)::text <> ALL ('{br,br?}'::text[]))<BR><BR><BR></BODY></HTML>