[postgis-users] Old (non-ST_) names don't use indexes?

[Paragon Corporation]

Note the wildly different plans; the first uses no indexes. I was under the
impression that while the old names were deprecated, the functionality was
identical to the new names. No?

No.  As your plan concludes -- old ones don't have built in indexes and new
ones do usually where possible.

The old ones are more comparable to the _ST_Intersects etc. 

-----Original Message-----
From: postgis-users-bounces at postgis.refractions.net
[mailto:postgis-users-bounces at postgis.refractions.net] On Behalf Of Reid
Priedhorsky
Sent: Friday, December 26, 2008 3:04 PM
To: PostGIS Users Discussion
Subject: [postgis-users] Old (non-ST_) names don't use indexes?

All,

Here are the query plans for two queries, identical except for the name of
the intersection function:

First, with Intersects():

> explain
>   select Buffer(nodepoint, :wh_radius, 4) as wh_geometry
>   from byway_extension be, byway_current b
>   where
>     be.byway_id != b.id
>     and Intersects(be.extension, b.geometry);

>                                      QUERY PLAN

> ----------------------------------------------------------------------
> ---------------  Nested Loop  (cost=684.78..97841763.45 
> rows=1262371587 width=32)
>    Join Filter: ((be.byway_id <> bs.id) AND intersects(be.extension,
bs.geometry))
>    ->  Seq Scan on byway_segment bs  (cost=0.00..6578.20 rows=151607
width=100)
>          Filter: ((NOT deleted) AND (valid_before_rid = 2000000000))
>    ->  Materialize  (cost=684.78..934.58 rows=24980 width=68)
>          ->  Seq Scan on byway_extension be  (cost=0.00..659.80 
> rows=24980 width=68)

Second, with ST_Intersects():

> explain
>   select Buffer(nodepoint, :wh_radius, 4) as wh_geometry
>   from byway_extension be, byway_current b
>   where
>     be.byway_id != b.id
>     and ST_Intersects(be.extension, b.geometry);

>                                                  QUERY PLAN

> ----------------------------------------------------------------------
> ---------------------------------------
>  Nested Loop  (cost=0.00..31033.43 rows=6312 width=32)
>    Join Filter: ((be.byway_id <> bs.id) AND _st_intersects(be.extension,
bs.geometry))
>    ->  Seq Scan on byway_extension be  (cost=0.00..659.80 rows=24980
width=68)
>    ->  Index Scan using byway_segment_gist on byway_segment bs
(cost=0.00..1.20 rows=1 width=100)
>          Index Cond: (be.extension && bs.geometry)
>          Filter: ((NOT bs.deleted) AND (bs.valid_before_rid = 
> 2000000000) AND (be.extension && bs.geometry))

Note the wildly different plans; the first uses no indexes. I was under the
impression that while the old names were deprecated, the functionality was
identical to the new names. No?

Thanks,

Reid

_______________________________________________
postgis-users mailing list
postgis-users at postgis.refractions.net
http://postgis.refractions.net/mailman/listinfo/postgis-users