<!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 4.0 Transitional//EN">
<HTML><HEAD>
<META content="text/html; charset=us-ascii" http-equiv=Content-Type>
<STYLE>.hmmessage P {
PADDING-BOTTOM: 0px; MARGIN: 0px; PADDING-LEFT: 0px; PADDING-RIGHT: 0px; PADDING-TOP: 0px
}
BODY.hmmessage {
FONT-FAMILY: Verdana; FONT-SIZE: 10pt
}
</STYLE>
<META name=GENERATOR content="MSHTML 8.00.7600.16588"></HEAD>
<BODY class=hmmessage>
<DIV dir=ltr align=left><SPAN class=365481719-11062010><FONT color=#0000ff
face=Arial>Paul,</FONT></SPAN></DIV>
<DIV dir=ltr align=left><SPAN class=365481719-11062010><FONT color=#0000ff
face=Arial></FONT></SPAN> </DIV>
<DIV dir=ltr align=left><SPAN class=365481719-11062010><FONT color=#0000ff
face=Arial>Can you send over your explain plan? Also what version of
PostgreSQL are you using?</FONT></SPAN></DIV>
<DIV dir=ltr align=left><SPAN class=365481719-11062010><FONT color=#0000ff
face=Arial></FONT></SPAN> </DIV>
<DIV dir=ltr align=left><SPAN class=365481719-11062010><FONT color=#0000ff
face=Arial>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.</FONT></SPAN></DIV>
<DIV dir=ltr align=left><SPAN class=365481719-11062010><FONT color=#0000ff
face=Arial></FONT></SPAN> </DIV>
<DIV dir=ltr align=left><SPAN class=365481719-11062010><FONT color=#0000ff
face=Arial>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.</FONT></SPAN></DIV>
<DIV dir=ltr align=left><SPAN class=365481719-11062010><FONT color=#0000ff
face=Arial></FONT></SPAN> </DIV>
<DIV dir=ltr align=left><SPAN class=365481719-11062010><FONT color=#0000ff
face=Arial>So rewrite of your examples</FONT></SPAN></DIV>
<DIV dir=ltr align=left><SPAN class=365481719-11062010><FONT color=#0000ff
face=Arial></FONT></SPAN> </DIV>
<DIV dir=ltr align=left><SPAN class=365481719-11062010><FONT color=#0000ff
face=Arial>Query 2:</FONT></SPAN></DIV>
<DIV dir=ltr align=left><SPAN class=365481719-11062010><FONT color=#0000ff
face=Arial></FONT></SPAN> </DIV>
<DIV dir=ltr align=left><SPAN class=365481719-11062010><FONT face=Arial>SELECT *
FROM table1 INNER JOIN (SELECT table2.the_geom FROM table2 WHERE id = 356)
AS t2</FONT></SPAN></DIV>
<DIV dir=ltr align=left><SPAN
class=365481719-11062010> <FONT
face=Arial>ON </FONT></SPAN><SPAN class=365481719-11062010><FONT
face=Arial>ST_DWithin(table1.the_geom, t2.the_geom, 5)
</FONT></SPAN></DIV>
<DIV dir=ltr align=left><SPAN class=365481719-11062010><FONT face=Arial>LIMIT
100;<BR> </FONT></SPAN></DIV>
<DIV><FONT color=#0000ff>Query3:</FONT><BR>SELECT tb1.id, tb1.the_geom
<BR> FROM table1 AS tb1 INNER JOIN</DIV>
<DIV><SPAN class=365481719-11062010> </SPAN> (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 b<SPAN
class=365481719-11062010>geom</SPAN> FROM table2 As tb2</DIV>
<DIV><SPAN class=365481719-11062010></SPAN><SPAN
class=365481719-11062010> WHERE
tb2.id = 356) As t2</SPAN></DIV>
<DIV><SPAN class=365481719-11062010></SPAN><SPAN
class=365481719-11062010> ON
ST_Within(tb1.the_geom, t2.bgeom);</SPAN></DIV>
<DIV><SPAN class=365481719-11062010></SPAN> </DIV>
<DIV><SPAN class=365481719-11062010><FONT color=#0000ff
face=Arial></FONT></SPAN> </DIV>
<DIV><SPAN class=365481719-11062010><FONT color=#0000ff face=Arial>Leo and
Regina</FONT></SPAN></DIV>
<DIV><SPAN class=365481719-11062010><FONT color=#0000ff face=Arial><A
href="http://www.postgis.us">http://www.postgis.us</A></FONT></SPAN></DIV>
<DIV><BR> </DIV>
<DIV dir=ltr lang=en-us class=OutlookMessageHeader align=left>
<HR tabIndex=-1>
<FONT face=Tahoma><B>From:</B> postgis-users-bounces@postgis.refractions.net
[mailto:postgis-users-bounces@postgis.refractions.net] <B>On Behalf Of </B>Paul
& Caroline Lewis<BR><B>Sent:</B> Friday, June 11, 2010 11:01
AM<BR><B>To:</B> postgis-users@postgis.refractions.net<BR><B>Subject:</B>
[postgis-users] Spatial Index Workings<BR></FONT><BR></DIV>
<DIV></DIV>Hi,<BR> 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.<BR> Therefore,
my question relates to how postGIS SQL should be set up to ensure the
spatial index is used.<BR> <BR>To start could someone explain what is
wrong/the-difference between how the following two queries
operate.<BR> <BR>Query1: SELECT * FROM table1 WHERE
ST_DWithin(table1.the_geom, ST_SetSRID(ST_MakePoint(693230.940102042,
739076.954923132, 115.122), 2157), 5) LIMIT 100;<BR><BR>Query2: SELECT *
FROM table1 WHERE ST_DWithin(table1.the_geom, (SELECT table2.the_geom
FROM table2 WHERE id = 356), 5) LIMIT
100;<BR> <BR>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.<BR> <BR>In relation to
this and in particular to the previous St_Expands solution my SQL is
as follows:<BR>Query3:<BR>SELECT tb1.id, tb1.the_geom <BR> FROM table1
AS tb1<BR> WHERE ST_Within(tb1.the_geom, (<BR> SELECT
ST_SetSRID(CAST(ST_MakeBox3D(ST_Translate((<BR> SELECT
tb2.the_geom FROM table2 AS tb2 WHERE tb2.id =
356<BR> ), -5, -4, -2)<BR> ,
ST_Translate((<BR> SELECT tb3.the_geom FROM table2
AS tb3 WHERE tb3.id = 356<BR> ), 5, 4, 2)) AS geometry),
2157)<BR> )<BR>));<BR> <BR>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.<BR>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.<BR> <BR>Thanks in advance,<BR> <BR>Paul<BR><BR>
<HR>
Hotmail: Free, trusted and rich email service. <A
href="https://signup.live.com/signup.aspx?id=60969" target=_new>Get it now.</A>
</BODY></HTML>