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: [email protected] [mailto:[email protected]] 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 [email protected] http://postgis.refractions.net/mailman/listinfo/postgis-users _______________________________________________ postgis-users mailing list [email protected] http://postgis.refractions.net/mailman/listinfo/postgis-users
