[postgis-users] Strange result about spatial indexes?

José Carlos Martínez jomarlla at cgf.upv.es
Thu Aug 13 23:46:50 PDT 2009


Sorry about that..I am using sky drive for storing the file. The 
'download' link from the htmlpage works though.
You can check it out with your own tables too I think you are going to 
find the same problem.


Paul Ramsey escribió:
> That link isn't downloading for me, it's an HTML page instead of a tar file...
>
> On Thu, Aug 13, 2009 at 9:03 PM, Jose Carlos<jomarlla at cgf.upv.es> wrote:
>   
>> Hi,
>> I made the table smaller for sharing. Now the table has just 1600 rows.
>> The effect still remains.  I tried with others tables and other PostGIS
>> versions, I tried even just with tables with POINT features getting similar
>> results.
>>
>> Thanx,
>> Best
>>
>> You can download the table from here:
>> http://cid-40b8dee7bf5b661f.skydrive.live.com/self.aspx/.Public/data.tgz
>>
>> test3=# explain analyze select count(*) from suelos1 s1, suelos1 s2
>> where (s1.geom && s2.geom);
>>                                                                QUERY
>> PLAN
>> -------------------------------------------------------------------------------------------------------------------------------------------
>>  Aggregate  (cost=1095.91..1095.92 rows=1 width=0) (actual
>> time=2840.187..2840.189 rows=1 loops=1)
>>   ->  Nested Loop  (cost=0.00..1079.87 rows=6416 width=0) (actual
>> time=0.111..2820.408 rows=13808 loops=1)
>>         ->  Seq Scan on suelos1 s1  (cost=0.00..130.04 rows=1604
>> width=4597) (actual time=0.009..2.580 rows=1604 loops=1)
>>         ->  Index Scan using suelos1_geom on suelos1 s2
>> (cost=0.00..0.58 rows=1 width=4597) (actual time=0.778..1.729 rows=9
>> loops=1604)
>>               Index Cond: (s1.geom && s2.geom)
>>  Total runtime: 2840.270 ms
>> (6 rows)
>>
>> test3=# explain analyze select count(*) from suelos1 s1, suelos1 s2
>> where (st_expand(s1.geom,00) && s2.geom);
>>                                                                QUERY
>> PLAN
>> -------------------------------------------------------------------------------------------------------------------------------------------
>>  Aggregate  (cost=1087.92..1087.93 rows=1 width=0) (actual
>> time=212.789..212.790 rows=1 loops=1)
>>   ->  Nested Loop  (cost=0.00..1087.89 rows=13 width=0) (actual
>> time=0.090..189.842 rows=13808 loops=1)
>>         ->  Seq Scan on suelos1 s1  (cost=0.00..130.04 rows=1604
>> width=4597) (actual time=0.012..2.632 rows=1604 loops=1)
>>         ->  Index Scan using suelos1_geom on suelos1 s2
>> (cost=0.00..0.58 rows=1 width=4597) (actual time=0.043..0.084 rows=9
>> loops=1604)
>>               Index Cond: (st_expand(s1.geom, 0::double precision) &&
>> s2.geom)
>>  Total runtime: 212.864 ms
>>
>>
>> (6 rows)
>>
>> _______________________________________________
>> postgis-users mailing list
>> postgis-users at postgis.refractions.net
>> http://postgis.refractions.net/mailman/listinfo/postgis-users
>>
>>     
> _______________________________________________
> postgis-users mailing list
> postgis-users at postgis.refractions.net
> http://postgis.refractions.net/mailman/listinfo/postgis-users
>   




More information about the postgis-users mailing list