Shaun,
Your backyard server is building a query plan that uses an index, and
your megabuck server is not.
Now, *why* that would be, is another story. Perhaps your Sun-special
configuration is being over-optimistic about how fast a sequence scan
is? That is probably one part of your performance tale.
The other part is that your backyard server probably has less memory
and slower disks, but a faster CPU clock? Since much of the
ST_Within() test is computational (particularly if you're not using an
index!) a slower CPU can make a big difference (but only linearly with
clock speed, really).
Once you get out of your bad query plan, the next revision of PostGIS/
GEOS with prepared geometry should speed up this kind of query even
more. I got a 6x improvement on my case, some cases report 20x
returns. However, this is not the solution to your root problem.
Paul
On Feb 14, 2008, at 2:21 PM, Shaun Kolomeitz wrote:
Dear PostGIS’ers
I’ve started to look at performance of PostGreSQL/PostGIS on our
Solaris/SPARC (T2000 server with 8GB RAM and SAS Disks using Solaris
10).
I know these boxes are better at web serving and not so great at
database serving, but this seems a bit ridiculous.
It was pretty bad (~36 secs for common queries) so I decided to bite
the bullet (last night) and upgrade to PostGreSQL 8.3.0, Proj 4.6.0,
GEOS 3.0.0 and PostGIS 1.3.2.
All went smashingly (well, at least I thought) ! Until I “tested”
performance again (hoping it was going to improve). Kudos to Paul (I
think) who put together the simple “magic upgrade” path instructions.
Here are the results –
(Before upgrade)
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);
QUERY PLAN
---------------------------------------------------------------------------------------------------------------------------------
Nested Loop (cost=318.76..1997.86 rows=15440 width=42) (actual
time=24386.877..35513.845 rows=8 loops=1)
Join Filter: st_within(a.the_geom, b.the_geom)
-> 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)
-> Materialize (cost=318.76..318.84 rows=8 width=32) (actual
time=0.001..0.009 rows=3 loops=9771)
-> 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)
Filter: ((name_caps)::text ~~ 'KOOM%'::text)
Total runtime: 35514.085 ms
(7 rows)
@[EMAIL PROTECTED]@^……(snails pace…)
(After Upgrade)
test=# select postgis_full_version();
postgis_full_version
------------------------------------------------------------------------------------------------------------------
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)
(1 row)
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);
QUERY PLAN
----------------------------------------------------------------------------------------------------------------------------------
Nested Loop (cost=289.31..1294.72 rows=6514 width=16) (actual
time=24376.602..35325.759 rows=8 loops=1)
Join Filter: st_within(a.the_geom, b.the_geom)
-> 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)
-> Materialize (cost=289.31..289.33 rows=2 width=2825) (actual
time=0.001..0.007 rows=3 loops=9771)
-> 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)
Filter: ((name_caps)::text ~~ 'KOOM%'::text)
Total runtime: 35326.335 ms
(7 rows)
I have followed the optimisation parameters available from Sun and
other places on the net. I’ve also indexed the spatial and non-
spatial columns appropriately.
I’ve even pre-loaded liblwgeom into PostGreSQL on startup.
Even our “put together with pieces laying around in the backyard”
Ubuntu server poo’s on our megabuck production server !
(Test box)
test=# explain test=# select postgis_full_version();
postgis_full_version
---------------------------------------------------------------------------------------------------------------------
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)
(1 row)
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);
QUERY
PLAN
-------------------------------------------------------------------------------------------------------------------------------------------------------------------------
Nested Loop (cost=0.00..296.60 rows=4 width=16) (actual
time=176.402..732.686 rows=8 loops=1)
Join Filter: _st_within(a.the_geom, b.the_geom)
-> 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)
Filter: ((name_caps)::text ~~ 'KOOM%'::text)
-> 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)
Index Cond: (a.the_geom && b.the_geom)
Filter: (a.the_geom && b.the_geom)
Total runtime: 732.775 ms
(8 rows)
Now that’s a 50x performance difference.
Any hints at what I could do to a) look at why it might be going
slow and b) speed it up ?
What does the “Procs from xxx need upgrade” indicate ?
Option B – Move ALL our PostGIS operations to our “back-yard” server.
Any hints appreciated.
Cheers,
Shaun Kolomeitz,
Senior Technical Officer
Systems & Support Branch
Parks Division
Environmental Protection Agency
Queensland Parks and Wildlife Division
+----------------------------------------------------------------+
Think B4U Print
1 ream of paper = 6% of a tree and 5.4kg CO2 in the atmosphere
3 sheets of A4 paper = 1 litre of water
+----------------------------------------------------------------+
___________________________
Disclaimer
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.
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.
Unless otherwise stated, this e-mail represents the views of the
sender and not the views of the Environmental Protection Agency.
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).
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 Queens_______________________________________________
postgis-users mailing list
[email protected]
http://postgis.refractions.net/mailman/listinfo/postgis-users
_______________________________________________
postgis-users mailing list
[email protected]
http://postgis.refractions.net/mailman/listinfo/postgis-users
