[postgis-users] S...L...o.....w..@^ performance on Solaris/SPARC
Graeme Leeming
gleeming at refractions.net
Thu Feb 14 14:38:01 PST 2008
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)
>
> @^....@^........@^......(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
>postgis-users at postgis.refractions.net
>http://postgis.refractions.net/mailman/listinfo/postgis-users
>
>
-------------- next part --------------
An HTML attachment was scrubbed...
URL: <http://lists.osgeo.org/pipermail/postgis-users/attachments/20080214/d369d5d3/attachment.html>
More information about the postgis-users
mailing list