<!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 4.0 Transitional//EN">
<HTML><HEAD>
<META content="text/html; charset=us-ascii" http-equiv=Content-Type>
<META name=GENERATOR content="MSHTML 8.00.7600.16588"></HEAD>
<BODY>
<DIV dir=ltr align=left><SPAN class=446072405-17072010><FONT color=#0000ff
size=2 face=Arial>Chris,</FONT></SPAN></DIV>
<DIV dir=ltr align=left><SPAN class=446072405-17072010><FONT color=#0000ff
size=2 face=Arial></FONT></SPAN> </DIV>
<DIV dir=ltr align=left><SPAN class=446072405-17072010><FONT color=#0000ff
size=2 face=Arial>Did you confirm that your btree index works fine without
the spatial query part?</FONT></SPAN></DIV>
<DIV dir=ltr align=left><SPAN class=446072405-17072010><FONT color=#0000ff
size=2 face=Arial></FONT></SPAN> </DIV>
<DIV dir=ltr align=left><SPAN class=446072405-17072010><FONT color=#0000ff
size=2 face=Arial>Run the query without the spatial part at all. If you
don't have enough data distinct categories, its often faster for PostgreSQL not
to use the btree index or at least it thinks so. Its also sometimes some
problem with differences in data types used that it can't use an index. I
don't think that is your issue here though.</FONT></SPAN></DIV>
<DIV dir=ltr align=left><SPAN class=446072405-17072010><FONT color=#0000ff
size=2 face=Arial></FONT></SPAN> </DIV>
<DIV dir=ltr align=left><SPAN class=446072405-17072010><FONT color=#0000ff
size=2 face=Arial>One way to try to force it to use all indexes is to do a
</FONT></SPAN></DIV>
<DIV dir=ltr align=left><SPAN class=446072405-17072010><FONT color=#0000ff
size=2 face=Arial></FONT></SPAN> </DIV>
<DIV dir=ltr align=left><SPAN class=446072405-17072010><FONT color=#0000ff
size=2 face=Arial>set enable_seqscan = off;</FONT></SPAN></DIV>
<DIV dir=ltr align=left><SPAN class=446072405-17072010><FONT color=#0000ff
size=2 face=Arial></FONT></SPAN> </DIV>
<DIV><SPAN class=446072405-17072010></SPAN><FONT face=Arial><FONT
color=#0000ff><FONT size=2>Before running the query<SPAN
class=446072405-17072010> and if that works, increasing the
seq_page_cost</SPAN></FONT></FONT></FONT></DIV>
<DIV><FONT face=Arial><FONT color=#0000ff><FONT size=2><SPAN
class=446072405-17072010></SPAN></FONT></FONT></FONT> </DIV>
<DIV><FONT face=Arial><FONT color=#0000ff><FONT size=2><SPAN
class=446072405-17072010>Hope that helps</SPAN></FONT></FONT></FONT></DIV>
<DIV><SPAN class=446072405-17072010></SPAN><FONT face=Arial><FONT
color=#0000ff><FONT size=2>L<SPAN class=446072405-17072010>eo and
Regina,</SPAN></FONT></FONT></FONT></DIV>
<DIV><SPAN class=446072405-17072010></SPAN><SPAN
class=446072405-17072010></SPAN><FONT face=Arial><FONT color=#0000ff><FONT
size=2><A href="http://www.postgis.us">h<SPAN
class=446072405-17072010>ttp://www.postgis.us</A></SPAN></FONT></FONT></FONT><BR></DIV>
<DIV dir=ltr lang=en-us class=OutlookMessageHeader align=left>
<HR tabIndex=-1>
<FONT size=2 face=Tahoma><B>From:</B>
postgis-users-bounces@postgis.refractions.net
[mailto:postgis-users-bounces@postgis.refractions.net] <B>On Behalf Of
</B>Christian Beaumont<BR><B>Sent:</B> Friday, July 16, 2010 12:31
PM<BR><B>To:</B> postgis-users@postgis.refractions.net<BR><B>Subject:</B>
[postgis-users] Execution plan with spatial index<BR></FONT><BR></DIV>
<DIV></DIV>Greetings,
<DIV><BR></DIV>
<DIV>I have a table with around a million rows with each row containing a
<B>geography</B> POINT (I'm covering the entire world). With these rows I
need to perform many nearest neighbor searches to locate other entities within a
range of 0 to 100km. The location field has a GiST index</DIV>
<DIV><BR></DIV>
<DIV>With 5,000 records in the table my average query took around 0.5ms;
however, now I have a million records the query time has gone up to around 4ms.
I did a reindex/vacuum etc. My goal is to try and make this query as
fast as possible since it underpins everything I'm doing and the target hardware
won't be as powerful as my development box.</DIV>
<DIV><BR></DIV>
<DIV>As well as the location column, I also have a "entity_type" column that is
a simple integer - 1, 2, ... (n)... </DIV>
<DIV><BR></DIV>
<DIV>I figured I could improve performance by adding an index on
the entity_type column, then filter the rowset prior to nearest
neighbor search. My logic was that it must be quicker to isolate a 10%
subset of the records using a simple integer index before feeding in to the
expensive GiST index.</DIV>
<DIV><BR></DIV>
<DIV>Unfortunately when I did this, PostgresSQL didn't use
my entity_type_idx at all. Instead, it did the nearest neighbor
search using the GiST index, then did a simple filter on the collected records
based on the entity_type. I tried a few tricks to make it use the
index but no-luck.</DIV>
<DIV><BR></DIV>
<DIV>Any ideas for speeding this up would be very much appreciated! Right
now my best idea would be to have separate tables for each entity type, but that
wouldn't be fun as I don't know the entity types in advance.</DIV>
<DIV><BR></DIV>
<DIV>cheers,</DIV>
<DIV>-chris</DIV>
<DIV><BR></DIV>
<DIV>Here is pseudo-code of the query and execution plan/analysis.
CB_GetPlace() is one of my helper functions that returns a geography from
an entity id (marked <B>stable</B>).</DIV>
<DIV><BR></DIV>
<DIV>
<DIV><B>SELECT</B></DIV>
<DIV><SPAN style="WHITE-SPACE: pre" class=Apple-tab-span></SPAN>entity_id,
category_id,</DIV>
<DIV><SPAN style="WHITE-SPACE: pre"
class=Apple-tab-span></SPAN>ST_Distance(location, CB_GetPlace(someEntityID)) as
arcLength</DIV>
<DIV><SPAN style="WHITE-SPACE: pre" class=Apple-tab-span></SPAN><B>FROM</B>
entities <B>WHERE</B></DIV>
<DIV><SPAN style="WHITE-SPACE: pre" class=Apple-tab-span></SPAN>category_id = 1
<B>AND</B></DIV>
<DIV><SPAN style="WHITE-SPACE: pre"
class=Apple-tab-span></SPAN>ST_DWithin(location, CB_GetPlace(someEntityID),
someRadius) <B>ORDER BY</B> arcLength;</DIV></DIV>
<DIV><BR></DIV>
<DIV>
<DIV>"Sort (cost=26.64..26.64 rows=1 width=140) (actual time=4.207..4.209
rows=16 loops=1)"</DIV>
<DIV>" Sort Key: (_st_distance(location,
cb_getplace(someEntityID::bigint), 0::double precision, true))"</DIV>
<DIV>" Sort Method: quicksort Memory: 18kB"</DIV>
<DIV>" -> Index Scan using place_idx on "entities"
(cost=0.03..26.63 rows=1 width=140) (actual time=1.691..4.187 rows=16
loops=1)"</DIV>
<DIV>" Index Cond: (location &&
_st_expand(cb_getplace(someEntityID::bigint), someRadius::double
precision))"</DIV>
<DIV>" Filter: ((urt_id = 1) AND
(cb_getplace(someEntityID::bigint) &&
_st_expand(location, someRadius::double precision)) AND
_st_dwithin(location, cb_getplace(someEntityID::bigint), someRadius::double
precision, true))"</DIV>
<DIV>"Total runtime: 4.242 ms"</DIV></DIV>
<DIV><BR></DIV>
<DIV>If it matters, my test platform is PostGIS 1.5.1 with PostgreSQL 8.4.4-1
(Windows 32bit build) though my target platform is Ubuntu x64.</DIV>
<DIV><BR></DIV></BODY></HTML>