[postgis-devel] ST_OrderingEquals vs ~=
Paragon Corporation
lr at pcorp.us
Sun Jun 21 00:05:19 PDT 2009
> Anywho, back to your original post. It does appear that ST_OrderingEquals
is invoking the index twice.
> http://trac.osgeo.org/postgis/browser/trunk/postgis/sqlmm.sql.in.c#L152
> But then, I can't seem to make it work at all.
> kneufeld=# explain analyze select * from pts where the_geom ~=
'010100000080B75585372032413885390ABF8A2441';
QUERY
> PLAN
----------------------------------------------------------------------------
---------------------------------------
Index Scan using pts_geom_idx on pts (cost=0.00..8.49 rows=1 width=88)
(actual time=9.469..9.657 rows=1 loops=1)
Index Cond: (the_geom ~=
'010100000080B75585372032413885390ABF8A2441'::geometry)
Total runtime: 9.968 ms
(3 rows)
kneufeld=# explain analyze select * from pts where
st_orderingequals(the_geom,
'010100000080B75585372032413885390ABF8A2441');
QUERY
> PLAN
>
----------------------------------------------------------------------------
-------------------------------
Seq Scan on pts (cost=0.00..266343.74 rows=333300 width=88) (actual
> time=0.076..9472.457 rows=1 loops=1)
> Filter: st_orderingequals(the_geom,
>'010100000080B75585372032413885390ABF8A2441'::geometry)
> Total runtime: 9472.499 ms
> (3 rows)
> -- Kevin
Kevin,
Oh My, ST_orderingEquals for some reason is forcing a table scan. It looks
like your st_orderingequals is not using an index -- doing a table scan, but
your
~= is using the index. On mine if I run ~= its super fast -- returns one
record out of about 1 million records and in 61ms definitely using index. I
tried on bigger geoms as well.
If I do ST_OrderingEquals -- it appears to be doing a table scan like yours
and takes whopping 21 seconds to return the same answer.
More information about the postgis-devel
mailing list