[postgis-users] Spatial Index Workings

[Paragon Corporation]

Paul,
 
Can you send over your explain plan?  Also what version of PostgreSQL are
you using?
 
As a general rule, PostgreSQL (particularly older versions) and I think it
holds for other databases as well, have a hard time optimizing subselect
filters used in WHERE so we try to avoid them.  It could also be that the
subselect it is not seeing as a constant and could be throwing off its
ability to break apart the ST_DWithin function into the index and non index
part.
 
The syntax we normally use which works fairly well, is to move the subselect
into the FROM clause.  Its cleaner also for example since you won't be
limited to it having to return one record.
 
So rewrite of your examples
 
Query 2:
 
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;
 
Query3:
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);
 
 
Leo and Regina
http://www.postgis.us

 
  _____  

From: postgis-users-bounces at postgis.refractions.net
[mailto:postgis-users-bounces at postgis.refractions.net] On Behalf Of Paul &
Caroline Lewis
Sent: Friday, June 11, 2010 11:01 AM
To: postgis-users at postgis.refractions.net
Subject: [postgis-users] Spatial Index Workings


Hi,
  First off, thank you for your help with the St_Expands issues, your
solution is what I need, however I'm not sure if I understand it properly or
if I'm implementing it correctly.
  Therefore, my question relates to how postGIS SQL should be set up to
ensure the spatial index is used.
 
To start could someone explain what is wrong/the-difference between how the
following two queries operate.
 
Query1: SELECT * FROM table1 WHERE ST_DWithin(table1.the_geom,
ST_SetSRID(ST_MakePoint(693230.940102042, 739076.954923132, 115.122), 2157),
5)  LIMIT 100;

Query2: SELECT * FROM table1 WHERE ST_DWithin(table1.the_geom, (SELECT
table2.the_geom FROM table2 WHERE id = 356), 5)  LIMIT 100;
 
Both queries produce the same results, however the first uses the spatial
index while the second doesn't, according to the pgAdmin3 explain tool. The
first query takes 1432ms while the second takes 141239ms and there are
24million+ geometries in table1. The point defined in Query1 is taken from
id 359 of table2, as is used in the nested select statement of Query2, so
both queries are using the exact same geometry point as the second argument
of the DWithin function. I obviously am not understanding the SQL properly
to ensure that the correct data type is used to force the spatial index to
be used as the performance difference is very significant.
 
In relation to this and in particular to the previous St_Expands solution my
SQL is as follows:
Query3:
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)
 )
));
 
This takes about 3 minutes to run but I'm sure I've got lots of performance
gain that can be made here as the pgAdmin3 explain tool shows no spatial
index is used at all when this query is run.
So, any help would be much appreciated in any form, particularly in helping
me understand what I'm doing wrong that causes the spatial index to be
ignored.
 
Thanks in advance,
 
Paul


  _____  

Hotmail: Free, trusted and rich email service. Get it now.
<https://signup.live.com/signup.aspx?id=60969>  
-------------- next part --------------
An HTML attachment was scrubbed...
URL: <http://lists.osgeo.org/pipermail/postgis-users/attachments/20100611/dd1a0445/attachment.html>