[postgis-users] GIST index works, then stops

[Robert W. Burgholzer]

Okay, This seems a little odd top me, but here goes:

I had a fairly time-consuming query, with no index:

explain update sm_wshed100 set p = (type2storm.pcurrent - 
type2storm.plast) where type2storm.the_geom && sm_wshed100.the_geom;
NOTICE:  QUERY PLAN:

Nested Loop  (cost=0.00..1223.94 rows=9 width=268)
   ->  Seq Scan on type2storm  (cost=0.00..2.01 rows=1 width=48)
   ->  Seq Scan on sm_wshed100  (cost=0.00..1220.86 rows=86 width=220)


then, I created an index:
create index stormindex on type2storm using GIST (the_geom 
GIST_GEOMETRY_OPS);

Now the query plan was more reasonable:
explain update sm_wshed100 set p = (type2storm.pcurrent - 
type2storm.plast) where type2storm.the_geom && sm_wshed100.the_geom;
NOTICE:  QUERY PLAN:

Nested Loop  (cost=0.00..79.94 rows=9 width=268)
   ->  Seq Scan on type2storm  (cost=0.00..1.01 rows=1 width=48)
   ->  Seq Scan on sm_wshed100  (cost=0.00..77.86 rows=86 width=220)

Then, after creating 2 other indexes on different tables, I get this:

NOTICE:  QUERY PLAN:

Nested Loop  (cost=0.00..1245.94 rows=9 width=268)
   ->  Seq Scan on type2storm  (cost=0.00..2.01 rows=1 width=48)
   ->  Seq Scan on sm_wshed100  (cost=0.00..1242.86 rows=86 width=220)


And, then, after dropping and re-creating my original index, I get this:

NOTICE:  QUERY PLAN:

Nested Loop  (cost=100000000.00..100001606.30 rows=9 width=268)
   ->  Seq Scan on sm_wshed100  (cost=100000000.00..100001242.86 rows=86 
width=220)
   ->  Index Scan using stormindex on type2storm  (cost=0.00..4.21 
rows=1 width=48)

Any clues? There are some standard indexes on the sm_wshed100 table, if 
that is perhaps the culprit.

Thanks in advance.


-- 
Robert Burgholzer
al·go·rithm  n.
        A step-by-step problem-solving procedure, especially an 
established, recursive computational
        procedure for solving a problem in a finite number of steps. 
Invented by Al Gore.
rburghol at vt.edu
http://www.soulswimmer.net/