[postgis-users] Spatial Index Workings

Mon Jun 14 06:10:10 PDT 2010

Sorry for the late reply but away from all computers for the weekend.

Anyway, thanks again for your help.

To answer your questions:

PostgreSQL version is 8.4.4 - PostGIS version 1.5

The original queries and explain statements are below:

Also included results from your suggested solutions which do improve runtimes.

Query1 START********************:

SELECT * FROM table1 WHERE ST_DWithin(table1.the_geom,
ST_SetSRID(ST_MakePoint(693230.940102042, 739076.954923132, 115.122), 2157),
5)  LIMIT 100;

EXPLAIN PLAN:
"Limit  (cost=3406.44..340848.60 rows=1 width=394)"
"  ->  Bitmap Heap Scan on table1  (cost=3406.44..340848.60 rows=1 width=394)"
"        Recheck Cond: (the_geom && '01030000206D08000001000000050000000B0E55E1D32725414CAFEBE8FF8D26410B0E55E1D32725414CAFEBE8138E26410B0E55E1E72725414CAFEBE8138E26410B0E55E1E72725414CAFEBE8FF8D26410B0E55E1D32725414CAFEBE8FF8D2641'::geometry)"
"        Filter: (('01010000A06D0800000B0E55E1DD2725414CAFEBE8098E26412B8716D9CEC75C40'::geometry && st_expand(the_geom, 5::double precision)) AND _st_dwithin(the_geom, '01010000A06D0800000B0E55E1DD2725414CAFEBE8098E26412B8716D9CEC75C40'::geometry, 5::double precision))"
"        ->  Bitmap Index Scan on table1_ind_point  (cost=0.00..3406.44 rows=101315 width=0)"
"              Index Cond: (the_geom && '01030000206D08000001000000050000000B0E55E1D32725414CAFEBE8FF8D26410B0E55E1D32725414CAFEBE8138E26410B0E55E1E72725414CAFEBE8138E26410B0E55E1E72725414CAFEBE8FF8D26410B0E55E1D32725414CAFEBE8FF8D2641'::geometry)"
Query1 END*********************:

Query2 START*******************:

SELECT * FROM table1 WHERE ST_DWithin(table1.the_geom, (SELECT
table2.the_geom FROM table2 WHERE id = 356), 5)  LIMIT 100;

EXPLAIN PLAN:

"Limit  (cost=8.27..102.44 rows=100 width=394)"
"  InitPlan 1 (returns $0)"
"    ->  Index Scan using table2_pkey on table2  (cost=0.00..8.27 rows=1 width=32)"
"          Index Cond: (id = 356)"
"  ->  Seq Scan on table1  (cost=0.00..7632345.80 rows=8104993 width=394)"
"        Filter: st_dwithin(the_geom, $0, 5::double precision)"

Query2 END**********************:

Query2_Improved START************:

This solution produces the same results as Query1 and Query2, it does use the spatial index and is faster. Query2_Improved = 362ms; Query2 = 4624ms; but is slower than Query1 = 253ms which is understandable as Query1 does not have the table2 overhead.

SELECT * FROM table1 INNER JOIN (SELECT table2.the_geom FROM table2 WHERE id
= 356) AS t2
        ON ST_DWithin(table1.the_geom, t2.the_geom, 5)  
LIMIT 100;

EXPLAIN PLAN:

"Limit  (cost=0.00..541.93 rows=1 width=426)"
"  ->  Nested Loop  (cost=0.00..541.93 rows=1 width=426)"
"        Join Filter: ((table2.the_geom && st_expand(table1.the_geom, 5::double precision)) AND _st_dwithin(table1.the_geom, table2.the_geom, 5::double precision))"
"        ->  Index Scan using table2_pkey on table2  (cost=0.00..8.27 rows=1 width=32)"
"              Index Cond: (id = 356)"
"        ->  Index Scan using table1_ind_point on table1  (cost=0.00..500.72 rows=122 width=394)"
"              Index Cond: (table1.the_geom && st_expand(table2.the_geom, 5::double precision))"

Query2_Improved END**************:

Query3 START********************:
SELECT tb1.id, tb1.the_geom 
 FROM table1 AS tb1
 WHERE ST_Within(tb1.the_geom, (
  SELECT ST_SetSRID(CAST(ST_MakeBox3D(ST_Translate((
   SELECT tb2.the_geom FROM table2 AS tb2 WHERE tb2.id = 356
   ), -5, -4, -2)
   , ST_Translate((
   SELECT tb3.the_geom FROM table2 AS tb3 WHERE tb3.id = 356
   ), 5, 4, 2)) AS geometry), 2157)
 )
));

EXPLAIN PLAN:

"Seq Scan on table1 tb1  (cost=16.57..7632362.37 rows=8104993 width=136)"
"  Filter: st_within(the_geom, $2)"
"  InitPlan 3 (returns $2)"
"    ->  Result  (cost=16.55..16.57 rows=1 width=0)"
"          InitPlan 1 (returns $0)"
"            ->  Index Scan using table2_pkey on table2 tb2  (cost=0.00..8.27 rows=1 width=32)"
"                  Index Cond: (id = 356)"
"          InitPlan 2 (returns $1)"
"            ->  Index Scan using table2_pkey on table2 tb3  (cost=0.00..8.27 rows=1 width=32)"
"                  Index Cond: (id = 356)"

Query3 END**********************:

Query3_Improved START************:

This solution produces a much faster result, same output results as Query3 and does us the spatial index. Query3 = 435794ms; Query3_Improved = 9452ms, huge improvement so thanks again.
SELECT tb1.id, tb1.the_geom 
 FROM table1 AS tb1 INNER JOIN
     (SELECT ST_SetSRID(CAST(ST_MakeBox3D(ST_Translate(tb2.the_geom, -5, -4,
-2)), ST_Translate(tb2.the_geom, 5,4,2)) As geometry), 2157) As bgeom FROM
table2 As tb2
                WHERE tb2.id = 356) As t2
            ON ST_Within(tb1.the_geom, t2.bgeom);

EXPLAIN PLAN:

"Nested Loop  (cost=0.01..544.08 rows=41 width=136)"
"  Join Filter: _st_within(tb1.the_geom, st_setsrid((st_makebox3d(affine(tb2.the_geom, 1::double precision, 0::double precision, 0::double precision, 0::double precision, 1::double precision, 0::double precision, 0::double precision, 0::double precision, 1::double precision, (-5)::double precision, (-4)::double precision, (-2)::double precision), affine(tb2.the_geom, 1::double precision, 0::double precision, 0::double precision, 0::double precision, 1::double precision, 0::double precision, 0::double precision, 0::double precision, 1::double precision, 5::double precision, 4::double precision, 2::double precision)))::geometry, 2157))"
"  ->  Index Scan using table2_pkey on table2 tb2  (cost=0.00..8.27 rows=1 width=32)"
"        Index Cond: (id = 356)"
"  ->  Index Scan using table1_ind_point on table1 tb1  (cost=0.01..500.73 rows=122 width=136)"
"        Index Cond: (tb1.the_geom && st_setsrid((st_makebox3d(affine(tb2.the_geom, 1::double precision, 0::double precision, 0::double precision, 0::double precision, 1::double precision, 0::double precision, 0::double precision, 0::double precision, 1::double precision, (-5)::double precision, (-4)::double precision, (-2)::double precision), affine(tb2.the_geom, 1::double precision, 0::double precision, 0::double precision, 0::double precision, 1::double precision, 0::double precision, 0::double precision, 0::double precision, 1::double precision, 5::double precision, 4::double precision, 2::double precision)))::geometry, 2157))"

Query3_Improved END***************:

Finally and just to clarify, you say that the Z check is not performed in the ST_Within and ST_DWithin cases. Thus my result set only contains points within the X and Y plane boundaries of the Box3D where some of them could be outside in the Z plane!!! If this is correct can you suggest a solution in PostGIS SQL terms or will postprocessing the results in a seperate manner be required? Will 3D awaremess of situations like this be included in future PostGIS versions.

Anyway thanks again,

Paul

_________________________________________________________________
Hotmail: Trusted email with Microsoft’s powerful SPAM protection.
https://signup.live.com/signup.aspx?id=60969
-------------- next part --------------
An HTML attachment was scrubbed...
URL: <http://lists.osgeo.org/pipermail/postgis-users/attachments/20100614/81d0c91e/attachment.html>