<!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 4.0 Transitional//EN">
<HTML><HEAD>
<META http-equiv=Content-Type content="text/html; charset=us-ascii">
<META content="MSHTML 6.00.6001.18063" name=GENERATOR></HEAD>
<BODY>
<DIV dir=ltr align=left><SPAN class=626144320-31072008><FONT face=Arial
color=#0000ff size=2>It would probably help if you clustered on the gist
index.</FONT></SPAN></DIV>
<DIV dir=ltr align=left><SPAN class=626144320-31072008><FONT face=Arial
color=#0000ff size=2></FONT></SPAN> </DIV>
<DIV dir=ltr align=left><SPAN class=626144320-31072008><FONT face=Arial
color=#0000ff size=2>Do</FONT></SPAN></DIV>
<DIV dir=ltr align=left><SPAN class=626144320-31072008><FONT face=Arial
color=#0000ff size=2></FONT></SPAN> </DIV>
<DIV dir=ltr align=left><SPAN class=626144320-31072008><FONT face=Arial
color=#0000ff size=2>ALTER TABLE <FONT face="Times New Roman" color=#000000
size=3>users CLUSTER ON users_locbbox;</FONT></FONT></SPAN></DIV>
<DIV dir=ltr align=left><SPAN class=626144320-31072008></SPAN><SPAN
class=626144320-31072008></SPAN>C<SPAN class=626144320-31072008>LUSTER
users;</SPAN></DIV>
<DIV><SPAN class=626144320-31072008></SPAN> </DIV>
<DIV><SPAN class=626144320-31072008><FONT face=Arial color=#0000ff size=2>Also
what kind of processors and on board ram do you have?</FONT></SPAN></DIV>
<DIV><FONT face=Arial color=#0000ff size=2></FONT> </DIV>
<DIV><SPAN class=626144320-31072008><FONT face=Arial color=#0000ff size=2>Hope
that helps,</FONT></SPAN></DIV>
<DIV><SPAN class=626144320-31072008><FONT face=Arial color=#0000ff
size=2>Regina</FONT></SPAN></DIV>
<DIV dir=ltr align=left><BR></DIV>
<DIV class=OutlookMessageHeader lang=en-us dir=ltr align=left>
<HR tabIndex=-1>
<FONT face=Tahoma size=2><B>From:</B>
postgis-users-bounces@postgis.refractions.net
[mailto:postgis-users-bounces@postgis.refractions.net] <B>On Behalf Of
</B>marc2112<BR><B>Sent:</B> Thursday, July 31, 2008 3:31 PM<BR><B>To:</B>
postgis-users@postgis.refractions.net<BR><B>Subject:</B> [postgis-users] query
tuning<BR></FONT><BR></DIV>
<DIV></DIV>
<DIV dir=ltr>Hi Folks,<BR><BR>I've got a slow running query. When I break
it down to just the spatial constraint on one table, I find that the query takes
8s. I need my broader query to take < 1s so have a long way to go on
just the spatial part. Hopefully you guys will have some ideas for
me...<BR><BR>Here's what I've got:<BR><BR>Query:<BR>SELECT u.username,u.locbbox,
y(u.locpoint) AS latitude, x(u.locpoint) AS longitude<BR>FROM users u<BR>WHERE
u.locbbox && SetSrid( 'BOX(-73.795166015625
9.42738628387451,-69.49951171875 11.813588142395)'::box2d, 4326 )
<BR><BR>Explain Analyze:<BR>"Bitmap Heap Scan on users u
(cost=741.34..43666.77 rows=15789 width=486) (actual time=29.730..7321.358
rows=10687 loops=1)"<BR>" Filter: (locbbox &&
'0103000020E6100000010000000500000000000000E47252C000000060D2DA224000000000E47252C0000000A08EA0274000000000F85F51C0000000A08EA0274000000000F85F51C000000060D2DA224000000000E47252C000000060D2DA2240'::geometry)"<BR>"
-> Bitmap Index Scan on users_locbbox (cost=0.00..737.39
rows=15789 width=0) (actual time=26.981..26.981 rows=10703
loops=1)"<BR>" Index Cond: (locbbox
&&
'0103000020E6100000010000000500000000000000E47252C000000060D2DA224000000000E47252C0000000A08EA0274000000000F85F51C0000000A08EA0274000000000F85F51C000000060D2DA224000000000E47252C000000060D2DA2240'::geometry)"<BR>"Total
runtime: 7328.359 ms"<BR><BR><BR>Index:<BR>CREATE INDEX users_locbbox<BR>
ON users<BR> USING gist<BR> (locbbox);<BR><BR>Some Stats (let me
know what else would be helpful):<BR>Rows in users table: 1.85mm<BR>Table Size:
~4GB<BR>users_locbbox index size: 190MB<BR><BR>Tuning done:<BR>shared_buffers =
100MB<BR>work_mem = 100MB<BR>max_fsm_pages = 153600<BR>random_page_cost =
3.0<BR>cpu_tuple_cost = 0.1<BR>effective_cache_size =
8GB<BR>default_statistics_target = 100<BR><BR>Tables in the query are
vacuum/analyzed daily.<BR><BR><BR></DIV></BODY></HTML>