Nicholas Bower wrote:
Perhaps this makes it more obvious - 9s to query a table of just 1.3M
rows with ST_Intersects and 20ms using &&.
explain analyze select count(*) from wastac.t_tile_geometry where
ST_Intersects(border,
ST_GeographyFromText('SRID=4326;POLYGON((116.751709
-31.381779,116.883545 -32.676373,114.741211 -32.796510,114.796143
-31.316101,116.751709 -31.381779))'));
Aggregate (cost=364056.80..364056.81 rows=1 width=0) (actual
time=9175.760..9175.761 rows=1 loops=1)
-> Seq Scan on t_tile_geometry (cost=0.00..362993.14 rows=425463
width=0) (actual time=2164.049..9175.706 rows=95 loops=1)
Filter: st_intersects(border,
'0103000020E61000000100000005000000F8C610001C305D40B24CBF44BC613FC0D6E253008C385D40DE72F563935640C0DF1B430070AF5C40EE77280A
F46540C01EFCC401F4B25C409F3BC1FEEB503FC0F8C610001C305D40B24CBF44BC613FC0'::geography)
Total runtime: 9175.859 ms
(4 rows)
wastac=> explain analyze select count(*) from wastac.t_tile_geometry
where ST_Intersects(border,
ST_GeographyFromText('SRID=4326;POLYGON((116.751709
-31.381779,116.883545 -32.676373,114.741211 -32.796510,114.796143
-31.316101,116.751709 -31.381779))'));
Aggregate (cost=364056.80..364056.81 rows=1 width=0) (actual
time=8854.932..8854.933 rows=1 loops=1)
-> Seq Scan on t_tile_geometry (cost=0.00..362993.14 rows=425463
width=0) (actual time=2017.629..8854.879 rows=95 loops=1)
Filter: st_intersects(border,
'0103000020E61000000100000005000000F8C610001C305D40B24CBF44BC613FC0D6E253008C385D40DE72F563935640C0DF1B430070AF5C40EE77280A
F46540C01EFCC401F4B25C409F3BC1FEEB503FC0F8C610001C305D40B24CBF44BC613FC0'::geography)
Total runtime: 8854.976 ms
(4 rows)
wastac=> explain analyze select count(*) from wastac.t_tile_geometry
where border && ST_GeographyFromText('SRID=4326;POLYGON((116.751709
-31.381779,116.883545 -32.676373,114.741211 -32.796510,114.796143
-31.316101,116.751709 -31.381779))');
Aggregate (cost=28.99..29.00 rows=1 width=0) (actual
time=24.443..24.443 rows=1 loops=1)
-> Index Scan using t_tile_geometry_border_key on t_tile_geometry
(cost=0.00..28.97 rows=6 width=0) (actual time=9.952..24.407 rows=112
loops=1)
Index Cond: (border &&
'0103000020E61000000100000005000000F8C610001C305D40B24CBF44BC613FC0D6E253008C385D40DE72F563935640C0DF1B430070AF5C40EE77280AF46540C
01EFCC401F4B25C409F3BC1FEEB503FC0F8C610001C305D40B24CBF44BC613FC0'::geography)
Total runtime: 24.681 ms
Right, because && is an index-only bounding box search (and so doesn't
require access to the heap unless you are using an older version of
PostGIS) whereas ST_Intersects() calculates the intersection predicate
based upon all points within the geometry (time consuming if they have
lots of points).
Incidentally, you've missed two of the most important bits of
information - the version of PostgreSQL and the version of PostGIS you
are using.
What does the output of:
SELECT version(), postgis_full_version();
return?
ATB,
Mark.
--
Mark Cave-Ayland - Senior Technical Architect
PostgreSQL - PostGIS
Sirius Corporation plc - control through freedom
http://www.siriusit.co.uk
t: +44 870 608 0063
Sirius Labs: http://www.siriusit.co.uk/labs
_______________________________________________
postgis-users mailing list
postgis-users@postgis.refractions.net
http://postgis.refractions.net/mailman/listinfo/postgis-users
