[postgis-users] Strange result about spatial indexes?

[Kevin Neufeld]

I find it amazing that this wasn't discovered and exploited long ago.  I 
get the same results testing two of my own linear tables:

postgis=# EXPLAIN ANALYZE select * from foo a, foo2 b where a.the_geom 
&& b.the_geom;
                                                         QUERY 
PLAN                                                        
----------------------------------------------------------------------------------------------------------------------------
 Nested Loop  (cost=0.00..1684.19 rows=27910 width=703) (actual 
time=2.046..795.320 rows=870 loops=1)
   ->  Seq Scan on foo a  (cost=0.00..1211.13 rows=13113 width=634) 
(actual time=0.015..45.444 rows=13113 loops=1)
   ->  Index Scan using foo2_idx on foo2 b  (cost=0.00..0.02 rows=1 
width=69) (actual time=0.047..0.050 rows=0 loops=13113)
         Index Cond: (a.the_geom && b.the_geom)
         Filter: (a.the_geom && b.the_geom)
 Total runtime: 798.109 ms
(6 rows)

postgis=# EXPLAIN ANALYZE select * from foo a, foo2 b where a.the_geom 
&& expand(b.the_geom, 0);
NOTICE:  LWGEOM_gist_joinsel called with arguments that are not column 
references
                                                       QUERY 
PLAN                                                       
-------------------------------------------------------------------------------------------------------------------------
 Nested Loop  (cost=0.00..2894.97 rows=59 width=703) (actual 
time=0.106..65.595 rows=870 loops=1)
   ->  Seq Scan on foo2 b  (cost=0.00..20.93 rows=893 width=69) (actual 
time=0.018..3.120 rows=893 loops=1)
   ->  Index Scan using foo_idx on foo a  (cost=0.00..3.20 rows=1 
width=634) (actual time=0.038..0.056 rows=1 loops=893)
         Index Cond: (a.the_geom && expand(b.the_geom, 0::double precision))
         Filter: (a.the_geom && expand(b.the_geom, 0::double precision))
 Total runtime: 68.424 ms


I've verified that the results are the same in both queries.

Of course, in doing this, you need to be careful you don't perform a 
sequential scan through the larger table as the planner chose to do in 
the first query (this quick testing database is clearly in need of tuning).

postgis=# EXPLAIN ANALYZE select * from foo a, foo2 b where b.the_geom 
&& expand(a.the_geom, 0);
NOTICE:  LWGEOM_gist_joinsel called with arguments that are not column 
references
                                                         QUERY 
PLAN                                                        
----------------------------------------------------------------------------------------------------------------------------
 Nested Loop  (cost=0.00..1756.76 rows=59 width=718) (actual 
time=0.195..467.116 rows=870 loops=1)
   ->  Seq Scan on foo a  (cost=0.00..1218.13 rows=13113 width=645) 
(actual time=0.023..45.412 rows=13113 loops=1)
   ->  Index Scan using foo2_idx on foo2 b  (cost=0.00..0.03 rows=1 
width=73) (actual time=0.023..0.024 rows=0 loops=13113)
         Index Cond: (b.the_geom && expand(a.the_geom, 0::double precision))
         Filter: (b.the_geom && expand(a.the_geom, 0::double precision))
 Total runtime: 469.915 ms
(6 rows)


-- Kevin

Jose Carlos 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
>