Shaun,
Don't despair, there is a simple explanation. Your query plans actually
provide the hint.
On your Solaris server:
-> 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)
On your backyard server:
-> 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)
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.
Let us know what kind of improvement that makes.
-Graeme
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 Queensland Government Information Standards (IS31, IS38,
IS40, IS41 and IS42) to the extent they are consistent with the law.
___________________________
------------------------------------------------------------------------
_______________________________________________
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
