Dear Andrea,
thank you for this deep insight into why Postgres/PostGIS with GeoServer
rocks! You were totally right!
I just made an exact clone of the lightning table in PostGIS and there
is no observable difference in performance whether I'm looking at whole
Europe (just a couple hundred ms) or just a tiny village unfortunate
enough to have a major thunderstorm. It's amazing.
BTW, if there's anybody wondering how to migrate table from Oracle
Locator/Spatial to PostGIS, here's a link describing usage of another
excellent open source software ogr2ogr:
http://words.mixedbredie.net/archives/1751
Thank you again!
On 22. 6. 2016 14:31, Andrea Aime wrote:
On Wed, Jun 22, 2016 at 2:15 PM, Jonathan Moules
<jonathan-li...@lightpear.com <mailto:jonathan-li...@lightpear.com>>
wrote:
While Andrea has an (understandable) leaning towards PostGres, I'd
be surprised if you can't optimise Oracle to get the desired
result too if you're fixed with that (but if you're not - PostGres
would probably be a worthwhile change).
The leaning has some explanation, that goes beyond the natural
sympathy for another open source project.
GeoServer is translating every OGC request into the best query it can
setup, but the translation
is still, after all, automatic, with little or no control on the admin
side (sql views might help to some extent).
In Oracle developers decided to add query hints, every time a query
misbehaves you can add one of those to force the better
execution path... that's nice, as long as you can control how the
queries are written.... but that's not the case in GeoServer.
Of course that reduced pressure to improve the query planner (and made
the Oracle consultant market a bigger and more
profitable one).
In PostgreSql developers instead refused to add query hints support,
and treated every case in which the optimizer took
the wrong path as a bug. At the beginning that was pretty painful, but
over time the planner evolved to the point that it's actually
very good... this couples well with an automatic query generator, as
long as the query is valid the db should not need
any help using the best access path.
If you think about it setting up a WFS (or a WMS with CQL_FILTER) is
really like saying "hey, here is my database, hit it with whatever you
want", since
people can literally write the filter they feel like, with no
limitation on complexity.
The is no query hint salvation there, each query is dynamic and
defined by the user at the time the request is made...
either the database is smart and fast on its own, without query
specific help from a human, or you're in for a pile of troubles.
Cheers
Andrea
--
==
GeoServer Professional Services from the experts! Visit
http://goo.gl/it488V for more information.
==
Ing. Andrea Aime
@geowolf
Technical Lead
GeoSolutions S.A.S.
Via di Montramito 3/A
55054 Massarosa (LU)
phone: +39 0584 962313
fax: +39 0584 1660272
mob: +39 339 8844549
http://www.geo-solutions.it
http://twitter.com/geosolutions_it
*AVVERTENZE AI SENSI DEL D.Lgs. 196/2003*
Le informazioni contenute in questo messaggio di posta elettronica e/o
nel/i file/s allegato/i sono da considerarsi strettamente riservate.
Il loro utilizzo è consentito esclusivamente al destinatario del
messaggio, per le finalità indicate nel messaggio stesso. Qualora
riceviate questo messaggio senza esserne il destinatario, Vi preghiamo
cortesemente di darcene notizia via e-mail e di procedere alla
distruzione del messaggio stesso, cancellandolo dal Vostro sistema.
Conservare il messaggio stesso, divulgarlo anche in parte,
distribuirlo ad altri soggetti, copiarlo, od utilizzarlo per finalità
diverse, costituisce comportamento contrario ai principi dettati dal
D.Lgs. 196/2003.
The information in this message and/or attachments, is intended solely
for the attention and use of the named addressee(s) and may be
confidential or proprietary in nature or covered by the provisions of
privacy act (Legislative Decree June, 30 2003, no.196 - Italy's New
Data Protection Code).Any use not in accord with its purpose, any
disclosure, reproduction, copying, distribution, or either
dissemination, either whole or partial, is strictly forbidden except
previous formal approval of the named addressee(s). If you are not the
intended recipient, please contact immediately the sender by
telephone, fax or e-mail and delete the information in this message
that has been received in error. The sender does not give any warranty
or accept liability as the content, accuracy or completeness of sent
messages and accepts no responsibility for changes made after they
were sent or for other risks which arise as a result of e-mail
transmission, viruses, etc.
-------------------------------------------------------
--
Peter Kovac
IMS Programmer
MicroStep-MIS
peter.ko...@microstep-mis.com
------------------------------------------------------------------------------
Attend Shape: An AT&T Tech Expo July 15-16. Meet us at AT&T Park in San
Francisco, CA to explore cutting-edge tech and listen to tech luminaries
present their vision of the future. This family event has something for
everyone, including kids. Get more information and register today.
http://sdm.link/attshape
_______________________________________________
Geoserver-users mailing list
Geoserver-users@lists.sourceforge.net
https://lists.sourceforge.net/lists/listinfo/geoserver-users
