<div dir="ltr">It's a pretty diesel box - 2 quad core xeons, 16 GB RAM, dedicated disks for data and txnlog.<br><br>It's
my understanding that a clustered index only helps when the data isn't
really changing. I read that it clusters (basically sorts the data per
the index) when you create the index but subsequent inserts/updates
still get written on new pages. Am I off there?<br><br><div class="gmail_quote">On Thu, Jul 31, 2008 at 4:46 PM, Paragon Corporation <span dir="ltr"><<a href="mailto:lr@pcorp.us">lr@pcorp.us</a>></span> wrote:<br>
<blockquote class="gmail_quote" style="border-left: 1px solid rgb(204, 204, 204); margin: 0pt 0pt 0pt 0.8ex; padding-left: 1ex;">
<div>
<div dir="ltr" align="left"><span><font size="2" color="#0000ff" face="Arial">It would probably help if you clustered on the gist
index.</font></span></div>
<div dir="ltr" align="left"><span><font size="2" color="#0000ff" face="Arial"></font></span> </div>
<div dir="ltr" align="left"><span><font size="2" color="#0000ff" face="Arial">Do</font></span></div>
<div dir="ltr" align="left"><span><font size="2" color="#0000ff" face="Arial"></font></span> </div>
<div dir="ltr" align="left"><span><font size="2" color="#0000ff" face="Arial">ALTER TABLE <font size="3" color="#000000" face="Times New Roman">users CLUSTER ON users_locbbox;</font></font></span></div>
<div dir="ltr" align="left"><span></span><span></span>C<span>LUSTER
users;</span></div>
<div><span></span> </div>
<div><span><font size="2" color="#0000ff" face="Arial">Also
what kind of processors and on board ram do you have?</font></span></div>
<div><font size="2" color="#0000ff" face="Arial"></font> </div>
<div><span><font size="2" color="#0000ff" face="Arial">Hope
that helps,</font></span></div>
<div><span><font size="2" color="#0000ff" face="Arial">Regina</font></span></div>
<div dir="ltr" align="left"><br></div>
<div dir="ltr" align="left" lang="en-us">
<hr>
<font size="2" face="Tahoma"><b>From:</b>
<a href="mailto:postgis-users-bounces@postgis.refractions.net" target="_blank">postgis-users-bounces@postgis.refractions.net</a>
[mailto:<a href="mailto:postgis-users-bounces@postgis.refractions.net" target="_blank">postgis-users-bounces@postgis.refractions.net</a>] <b>On Behalf Of
</b>marc2112<br><b>Sent:</b> Thursday, July 31, 2008 3:31 PM<br><b>To:</b>
<a href="mailto:postgis-users@postgis.refractions.net" target="_blank">postgis-users@postgis.refractions.net</a><br><b>Subject:</b> [postgis-users] query
tuning<br></font><br></div><div><div></div><div class="Wj3C7c">
<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></div></div></div>
<br>_______________________________________________<br>
postgis-users mailing list<br>
<a href="mailto:postgis-users@postgis.refractions.net">postgis-users@postgis.refractions.net</a><br>
<a href="http://postgis.refractions.net/mailman/listinfo/postgis-users" target="_blank">http://postgis.refractions.net/mailman/listinfo/postgis-users</a><br>
<br></blockquote></div><br></div>