<!DOCTYPE html PUBLIC "-//W3C//DTD HTML 4.01 Transitional//EN">
<html>
<head>
<meta http-equiv="Content-Type" content="text/html;charset=ISO-8859-1">
<title></title>
</head>
<body text="#000000" bgcolor="#ffffff">
Shaun,<br>
<br>
Don't despair, there is a simple explanation. Your query plans
actually provide the hint.<br>
<br>
On your Solaris server: <br>
<p align="left"><span lang="en-au"><font size="2" face="Arial">
-> Seq Scan on herb_ortho_index_poly a (cost=0.00..565.71
rows=9771 width=134) (actual time=0.020..22.089 rows=9771 loops=1)</font></span></p>
<br>
On your backyard server:<br>
<br>
<span lang="en-au"><font size="2" face="Arial"> ->
Index Scan using herb_ortho_index_poly_the_geom_gist on
herb_ortho_index_poly a (cost=0.00..8.27 rows=1 width=488) (actual
time=14.202..118.670 rows=25 loops=3)</font></span>
<p align="left"><span lang="en-au"><font size="2" face="Arial">
Index Cond: (a.the_geom && b.the_geom)<br>
<br>
</font></span></p>
<p align="left">So try creating a GIST index against
herb_ortho_index_poly.the_geom on your Solaris server and then rerun
the query. The ST_Within() geometry operation will execute much more
efficiently using that index, as it is doing on the backyard machine.
Also a vacuum full analyze on your database may improve performance.<br>
</p>
<p align="left">Let us know what kind of improvement that makes.<br>
</p>
-Graeme<br>
<br>
Shaun Kolomeitz wrote:<br>
<blockquote type="cite"
cite="mid4F51CF02179D2247B9ED6AFA54E2DF6E053CF341@BNEXVS02.prod.ad.internal">
<meta http-equiv="Content-Type" content="text/html; ">
<meta name="Generator" content="MS Exchange Server version 6.5.7638.1">
<title>S...L...o.....w..@^ performance on Solaris/SPARC</title>
<!-- Converted from text/rtf format -->
<p align="left"><span lang="en-au"><font size="2" face="Arial">Dear
PostGIS</font></span><span lang="en-au"><font size="2" face="Arial">’</font></span><span
lang="en-au"><font size="2" face="Arial">ers</font></span></p>
<p align="left"><span lang="en-au"><font size="2" face="Arial">I</font></span><span
lang="en-au"><font size="2" face="Arial">’</font></span><span
lang="en-au"><font size="2" face="Arial">ve started to look at
performance of PostGreSQL/PostGIS on our Solaris/SPARC (T2000 server
with 8GB RAM and SAS Disks</font></span><span lang="en-au"><font
size="2" face="Arial"> using Solaris 10</font></span><span lang="en-au"><font
size="2" face="Arial">)</font></span><span lang="en-au"><font size="2"
face="Arial">.</font></span></p>
<p align="left"><span lang="en-au"><font size="2" face="Arial">I know
these boxes are better at web serving and not so great at database
serving, but this seems a bit</font></span><span lang="en-au"> <font
size="2" face="Arial">ridiculous</font></span><span lang="en-au"><font
size="2" face="Arial">.</font></span></p>
<p align="left"><span lang="en-au"><font size="2" face="Arial">It was
pretty bad</font></span><span lang="en-au"> <font size="2" face="Arial">(~36
secs for common</font></span><span lang="en-au"><font size="2"
face="Arial"> queries)</font></span><span lang="en-au"> <font size="2"
face="Arial">so I decided to bite the bullet</font></span><span
lang="en-au"> <font size="2" face="Arial">(last night)</font></span><span
lang="en-au"> <font size="2" face="Arial">and</font></span><span
lang="en-au"> <font size="2" face="Arial">upgrade to PostGreSQL
8.3.0, Proj 4.6.0, GEOS 3.0.0 and PostGIS 1.3.2.</font></span></p>
<p align="left"><span lang="en-au"><font size="2" face="Arial">All
went smashingly (well</font></span><span lang="en-au"><font size="2"
face="Arial">, at least I thought</font></span><span lang="en-au"><font
size="2" face="Arial">) !</font></span><span lang="en-au"><font
size="2" face="Arial"> Until I</font></span><span lang="en-au"> <font
size="2" face="Arial">“</font></span><span lang="en-au"><font size="2"
face="Arial">tested</font></span><span lang="en-au"><font size="2"
face="Arial">”</font></span><span lang="en-au"><font size="2"
face="Arial"> performance again (hoping it was going to improve).</font></span><span
lang="en-au"><font size="2" face="Arial"> Kudos to Paul (I think) who
put together the simple</font></span><span lang="en-au"> <font size="2"
face="Arial">“</font></span><span lang="en-au"><font size="2"
face="Arial">magic upgrade</font></span><span lang="en-au"><font
size="2" face="Arial">”</font></span><span lang="en-au"><font size="2"
face="Arial"> path instructions.</font></span></p>
<p align="left"><span lang="en-au"><font size="2" face="Arial">Here</font></span><span
lang="en-au"> <font size="2" face="Arial">are</font></span><span
lang="en-au"><font size="2" face="Arial"> the results</font></span><span
lang="en-au"> <font size="2" face="Arial">–</font></span></p>
<p align="left"><span lang="en-au"><font size="2" face="Arial">(Before
upgrade)</font></span></p>
<p align="left"><span lang="en-au"><font size="2" face="Arial">test=#
explain analyze select a.filename from herb_ortho_index_poly a,
estate127_bdy b where b.name_caps like 'KOOM%' and
ST_Within(a.the_geom, b.the_geom);</font></span></p>
<p align="left"><span lang="en-au"><font size="2" face="Arial">
QUERY PLAN </font></span></p>
<p align="left"><span lang="en-au"><font size="2" face="Arial">---------------------------------------------------------------------------------------------------------------------------------</font></span></p>
<p align="left"><span lang="en-au"><font size="2" face="Arial"> Nested
Loop (cost=318.76..1997.86 rows=15440 width=42) (actual
time=24386.877..35513.845 rows=8 loops=1)</font></span></p>
<p align="left"><span lang="en-au"><font size="2" face="Arial">
Join Filter: st_within(a.the_geom, b.the_geom)</font></span></p>
<p align="left"><span lang="en-au"><font size="2" face="Arial">
-> Seq Scan on herb_ortho_index_poly a (cost=0.00..636.90
rows=5790 width=74) (actual time=0.022..21.949 rows=9771 loops=1)</font></span></p>
<p align="left"><span lang="en-au"><font size="2" face="Arial">
-> Materialize (cost=318.76..318.84 rows=8 width=32) (actual
time=0.001..0.009 rows=3 loops=9771)</font></span></p>
<p align="left"><span lang="en-au"><font size="2" face="Arial">
-> Seq Scan on estate127_bdy b (cost=0.00..318.75 rows=8 width=32)
(actual time=5.475..44.241 rows=3 loops=1)</font></span></p>
<p align="left"><span lang="en-au"><font size="2" face="Arial">
Filter: ((name_caps)::text ~~ 'KOOM%'::text)</font></span></p>
<p align="left"><span lang="en-au"><font size="2" face="Arial"> Total
runtime: 35514.085 ms</font></span></p>
<p align="left"><span lang="en-au"><font size="2" face="Arial">(7
rows)</font></span></p>
<p align="left"><span lang="en-au"><font size="2" face="Arial">@^</font></span><span
lang="en-au"><font size="2" face="Arial">…</font></span><span
lang="en-au"><font size="2" face="Arial">.</font></span><span
lang="en-au"><font size="2" face="Arial">@^</font></span><span
lang="en-au"><font size="2" face="Arial">……</font></span><span
lang="en-au"><font size="2" face="Arial">..@^</font></span><span
lang="en-au"><font size="2" face="Arial">……</font></span><span
lang="en-au"><font size="2" face="Arial">(snails pace</font></span><span
lang="en-au"><font size="2" face="Arial">…</font></span><span
lang="en-au"><font size="2" face="Arial">)</font></span></p>
<p align="left"><span lang="en-au"><font size="2" face="Arial">(After
Upgrade)</font></span><span lang="en-au"> </span></p>
<p align="left"><span lang="en-au"><font size="2" face="Arial">test=#
select postgis_full_version();</font></span></p>
<p align="left"><span lang="en-au"><font size="2" face="Arial">
postgis_full_version</font></span></p>
<p align="left"><span lang="en-au"><font size="2" face="Arial">------------------------------------------------------------------------------------------------------------------</font></span></p>
<p align="left"><span lang="en-au"><font size="2" face="Arial"> POSTGIS="1.3.2"
GEOS="3.0.0-CAPI-1.4.1" PROJ="Rel. 4.6.0, 21 Dec 2007" USE_STATS (procs
from 1.2.1 need upgrade)</font></span></p>
<p align="left"><span lang="en-au"><font size="2" face="Arial">(1 row)</font></span></p>
<p align="left"><span lang="en-au"><font size="2" face="Arial">test=#
explain analyze select a.filename from herb_ortho_index_poly a,
estate127_bdy b where b.name_caps like 'KOOM%' and
ST_Within(a.the_geom, b.the_geom);</font></span></p>
<p align="left"><span lang="en-au"><font size="2" face="Arial">
QUERY PLAN</font></span></p>
<p align="left"><span lang="en-au"><font size="2" face="Arial">----------------------------------------------------------------------------------------------------------------------------------</font></span></p>
<p align="left"><span lang="en-au"><font size="2" face="Arial"> Nested
Loop (cost=289.31..1294.72 rows=6514 width=16) (actual
time=24376.602..35325.759 rows=8 loops=1)</font></span></p>
<p align="left"><span lang="en-au"><font size="2" face="Arial">
Join Filter: st_within(a.the_geom, b.the_geom)</font></span></p>
<p align="left"><span lang="en-au"><font size="2" face="Arial">
-> Seq Scan on herb_ortho_index_poly a (cost=0.00..565.71
rows=9771 width=134) (actual time=0.020..22.089 rows=9771 loops=1)</font></span></p>
<p align="left"><span lang="en-au"><font size="2" face="Arial">
-> Materialize (cost=289.31..289.33 rows=2 width=2825) (actual
time=0.001..0.007 rows=3 loops=9771)</font></span></p>
<p align="left"><span lang="en-au"><font size="2" face="Arial">
-> Seq Scan on estate127_bdy b (cost=0.00..289.31 rows=2
width=2825) (actual time=4.367..31.726 rows=3 loops=1)</font></span></p>
<p align="left"><span lang="en-au"><font size="2" face="Arial">
Filter: ((name_caps)::text ~~ 'KOOM%'::text)</font></span></p>
<p align="left"><span lang="en-au"><font size="2" face="Arial"> Total
runtime: 35326.335 ms</font></span></p>
<p align="left"><span lang="en-au"><font size="2" face="Arial">(7
rows)</font></span></p>
<br>
<p align="left"><span lang="en-au"><font size="2" face="Arial">I have
followed the optimisation parameters available from Sun and other
places on the net.</font></span><span lang="en-au"><font size="2"
face="Arial"> I</font></span><span lang="en-au"><font size="2"
face="Arial">’</font></span><span lang="en-au"><font size="2"
face="Arial">ve also indexed the spatial and non-spatial columns
appropriately.</font></span></p>
<p align="left"><span lang="en-au"><font size="2" face="Arial">I</font></span><span
lang="en-au"><font size="2" face="Arial">’</font></span><span
lang="en-au"><font size="2" face="Arial">ve even pre-loaded liblwgeom
into PostGreSQL on startup.</font></span></p>
<p align="left"><span lang="en-au"><font size="2" face="Arial">Even
our</font></span><span lang="en-au"> <font size="2" face="Arial">“</font></span><span
lang="en-au"><font size="2" face="Arial">put together with</font></span><span
lang="en-au"> <font size="2" face="Arial">pieces</font></span><span
lang="en-au"><font size="2" face="Arial"> laying around in the backyard</font></span><span
lang="en-au"><font size="2" face="Arial">”</font></span><span
lang="en-au"><font size="2" face="Arial"> Ubuntu server poo</font></span><span
lang="en-au"><font size="2" face="Arial">’</font></span><span
lang="en-au"><font size="2" face="Arial">s on our</font></span><span
lang="en-au"> <font size="2" face="Arial">megabuck</font></span><span
lang="en-au"><font size="2" face="Arial"> production server !</font></span></p>
<p align="left"><span lang="en-au"><font size="2" face="Arial">(Test
box)</font></span></p>
<p align="left"><span lang="en-au"><font size="2" face="Arial">test=#
explain</font></span><span lang="en-au"> <font size="2" face="Arial">test=#
select postgis_full_version();</font></span></p>
<p align="left"><span lang="en-au"><font size="2" face="Arial">
postgis_full_version</font></span></p>
<p align="left"><span lang="en-au"><font size="2" face="Arial">---------------------------------------------------------------------------------------------------------------------</font></span></p>
<p align="left"><span lang="en-au"><font size="2" face="Arial"> POSTGIS="1.3.2"
GEOS="2.2.3-CAPI-1.1.1" PROJ="Rel. 4.5.0, 22 Oct 2006" USE_STATS (procs
from 1.3.0RC5 need upgrade)</font></span></p>
<p align="left"><span lang="en-au"><font size="2" face="Arial">(1 row)</font></span></p>
<p align="left"><span lang="en-au"><font size="2" face="Arial">analyze
select a.filename from herb_ortho_index_poly a, estate127_bdy b where
b.name_caps like 'KOOM%' and ST_Within(a.the_geom, b.the_geom);</font></span></p>
<p align="left"><span lang="en-au"><font size="2" face="Arial">
QUERY PLAN </font></span></p>
<p align="left"><span lang="en-au"><font size="2" face="Arial">-------------------------------------------------------------------------------------------------------------------------------------------------------------------------</font></span></p>
<p align="left"><span lang="en-au"><font size="2" face="Arial"> Nested
Loop (cost=0.00..296.60 rows=4 width=16) (actual time=176.402..732.686
rows=8 loops=1)</font></span></p>
<p align="left"><span lang="en-au"><font size="2" face="Arial">
Join Filter: _st_within(a.the_geom, b.the_geom)</font></span></p>
<p align="left"><span lang="en-au"><font size="2" face="Arial">
-> Seq Scan on estate127_bdy b (cost=0.00..288.31 rows=1
width=11300) (actual time=1.047..8.541 rows=3 loops=1)</font></span></p>
<p align="left"><span lang="en-au"><font size="2" face="Arial">
Filter: ((name_caps)::text ~~ 'KOOM%'::text)</font></span></p>
<p align="left"><span lang="en-au"><font size="2" face="Arial">
-> Index Scan using herb_ortho_index_poly_the_geom_gist on
herb_ortho_index_poly a (cost=0.00..8.27 rows=1 width=488) (actual
time=14.202..118.670 rows=25 loops=3)</font></span></p>
<p align="left"><span lang="en-au"><font size="2" face="Arial">
Index Cond: (a.the_geom && b.the_geom)</font></span></p>
<p align="left"><span lang="en-au"><font size="2" face="Arial">
Filter: (a.the_geom && b.the_geom)</font></span></p>
<p align="left"><span lang="en-au"><font size="2" face="Arial"> Total
runtime: 732.775 ms</font></span></p>
<p align="left"><span lang="en-au"><font size="2" face="Arial">(8
rows)</font></span></p>
<p align="left"><span lang="en-au"><font size="2" face="Arial">Now</font></span><span
lang="en-au"> <font size="2" face="Arial">that’s</font></span><span
lang="en-au"><font size="2" face="Arial"> a 50x performance difference.</font></span></p>
<p align="left"><span lang="en-au"><font size="2" face="Arial">Any
hints at what I could</font></span><span lang="en-au"> <font size="2"
face="Arial">do</font></span><span lang="en-au"> <font size="2"
face="Arial">to a) look at wh</font></span><span lang="en-au"><font
size="2" face="Arial">y it might be going slow and b) speed it up ?</font></span></p>
<p align="left"><span lang="en-au"><font size="2" face="Arial">What
does the</font></span><span lang="en-au"> <font size="2" face="Arial">“</font></span><span
lang="en-au"><font size="2" face="Arial">Procs from xxx ne</font></span><span
lang="en-au"><font size="2" face="Arial">e</font></span><span
lang="en-au"><font size="2" face="Arial">d upgrade</font></span><span
lang="en-au"><font size="2" face="Arial">”</font></span><span
lang="en-au"><font size="2" face="Arial"> indicate ?</font></span></p>
<p align="left"><span lang="en-au"><font size="2" face="Arial">Option
B</font></span><span lang="en-au"> <font size="2" face="Arial">–</font></span><span
lang="en-au"><font size="2" face="Arial"> Move ALL our PostGIS
operations to our</font></span><span lang="en-au"> <font size="2"
face="Arial">“</font></span><span lang="en-au"><font size="2"
face="Arial">back-yard</font></span><span lang="en-au"><font size="2"
face="Arial">”</font></span><span lang="en-au"><font size="2"
face="Arial"> server.</font></span></p>
<p align="left"><span lang="en-au"><font size="2" face="Arial">Any
hints appreciated.</font></span></p>
<p align="left"><span lang="en-au"><font size="2" face="Arial">Cheers,</font></span></p>
<p align="left"><a name=""><b><span lang="en-au"><font size="2"
face="Hobo Std">Shaun Kolomeitz,</font></span></b></a></p>
<p align="left"><span lang="en-au"><font size="2" face="Arial">Senior
Technical Officer</font></span></p>
<p align="left"><span lang="en-au"><font size="2" face="Arial">Systems
& Support Branch</font></span></p>
<p align="left"><span lang="en-au"><font size="2" face="Arial">Parks
Division</font></span></p>
<p align="left"><b><span lang="en-au"><font size="2" face="Arial">Environmental
Protection</font></span></b><span lang="en-au"> <font size="2"
face="Arial">Agency</font></span></p>
<p align="left"><span lang="en-au"><font size="2" face="Arial">Queensland</font></span><b><span
lang="en-au"> <font size="2" face="Arial">Parks and Wildlife</font></span></b><b><span
lang="en-au"> <font size="2" face="Arial">Division</font></span></b></p>
<p style="margin-left: 18pt;"><span
style="font-family: 'Arial'; font-size: 8pt;">+----------------------------------------------------------------+</span></p>
<p style="margin-left: 18pt;"><span
style="font-family: 'Arial'; font-size: 8pt;">Think B4U Print</span></p>
<p style="margin-left: 18pt;"><span
style="font-family: 'Arial'; font-size: 8pt;">1 ream of paper = 6% of
a tree and 5.4kg CO2 in the atmosphere</span></p>
<p style="margin-left: 18pt;"><span
style="font-family: 'Arial'; font-size: 8pt;">3 sheets of A4 paper = 1
litre of water</span></p>
<p style="margin-left: 18pt;"><span
style="font-family: 'Arial'; font-size: 8pt;">+----------------------------------------------------------------+</span></p>
<p><span style="font-family: 'Arial'; font-size: 8pt;"> </span></p>
<p><span style="font-family: 'Arial'; font-size: 8pt;">___________________________</span></p>
<p><span style="font-family: 'Arial'; font-size: 8pt;">Disclaimer</span></p>
<p><span style="font-family: 'Arial'; font-size: 8pt;"> </span></p>
<p><span style="font-family: 'Arial'; font-size: 8pt;">WARNING: This
e-mail (including any attachments) has originated from a Queensland
Government department and may contain information that is confidential,
private, or covered by legal professional privilege, and may be
protected by copyright. </span></p>
<p><span style="font-family: 'Arial'; font-size: 8pt;"> </span></p>
<p><span style="font-family: 'Arial'; font-size: 8pt;">You may use
this e-mail only if you are the person(s) it was intended to be sent to
and if you use it in an authorised way. No one is allowed to use,
review, alter, transmit, disclose, distribute, print or copy this
e-mail without appropriate authority. If you have received this e-mail
in error, please inform the sender immediately by phone or e-mail and
delete this e-mail, including any copies, from your computer system
network and destroy any hardcopies.</span></p>
<p><span style="font-family: 'Arial'; font-size: 8pt;"> </span></p>
<p><span style="font-family: 'Arial'; font-size: 8pt;">Unless
otherwise stated, this e-mail represents the views of the sender and
not the views of the Environmental Protection Agency.</span></p>
<p><span style="font-family: 'Arial'; font-size: 8pt;"> </span></p>
<p><span style="font-family: 'Arial'; font-size: 8pt;">Although this
e-mail has been checked for the presence of computer viruses, the
Environmental Protection Agency provides no warranty that all viruses
have been detected and cleaned. Any use of this e-mail could harm your
computer system. It is your responsibility to ensure that this e-mail
does not contain and is not affected by computer viruses, defects or
interference by third parties or replication problems (including
incompatibility with your computer system).</span></p>
<p><span style="font-family: 'Arial'; font-size: 8pt;"> </span></p>
<p><span style="font-family: 'Arial'; font-size: 8pt;">E-mails sent
to and from the Environmental Protection Agency will be electronically
stored, managed and may be audited, in accordance with the law and
Queensland Government Information Standards (IS31, IS38, IS40, IS41 and
IS42) to the extent they are consistent with the law.</span></p>
<p><span style="font-family: 'Arial'; font-size: 8pt;"> </span></p>
<p><span style="font-family: 'Arial'; font-size: 8pt;">___________________________</span></p>
<p><span style="font-family: 'Arial'; font-size: 8pt;"> </span></p>
<pre wrap="">
<hr width="90%" size="4">
_______________________________________________
postgis-users mailing list
<a class="moz-txt-link-abbreviated" href="mailto:postgis-users@postgis.refractions.net">postgis-users@postgis.refractions.net</a>
<a class="moz-txt-link-freetext" href="http://postgis.refractions.net/mailman/listinfo/postgis-users">http://postgis.refractions.net/mailman/listinfo/postgis-users</a>
</pre>
</blockquote>
</body>
</html>