Great, thanks to both of you. Now I see clearer on how to debug and
improve this. If I get something that would be useful to all, after
improving the queries, I'll share the trick!
Gabriel
Le 21/03/2017 à 12:58, Jonathan Moules a écrit :
Hi Gabriel,
If you turn the GeoServer logging level to Geotools-developer level,
the log file will then include the SQL query that is sent to the
database. This should facilitate debugging this issue.
You can then try running the query against your database's EXPLAIN
syntax - which shows how your database thinks it will run it (or even
better, EXPLAIN ANALYZE, which runs it and then says how it ran it).
This will let you try and determine where the bottleneck is.
I've found this to be a fairly decent tutorial:
https://www.depesz.com/2013/04/16/explaining-the-unexplainable/]
Between these you should hopefully be able to determine whether the
optimisation scope is GeoServer, PostGres, your query, or some
combination thereof.
Cheers,
Jonathan
---- On Mon, 20 Mar 2017 17:34:03 +0000 *Gabriel Vatin
<gabriel.va...@kinaxia.fr>* wrote ----
Thanks Andrea for the quick answer. What I can't really guess for
now, is when and how is the SQL view executed : does the BBOX
attribute of GetMap queries affect this SQL view (when no
parameter in the view definition) ?
The type of query to create a new layer is done so : I have a
whole "goi" table with attributes such as name etc., and a table
with all geometries (stored in geo_data column, which is a
Geometry,4326 with GIST index). Each layers of my GeoServer
changes with the "got_id" (layer theme ID), stored on the goi
table. Here is the whole view
SELECT id, id_ori, name, geo_data
FROM geometrie
LEFT JOIN goi USING (goi_id)
WHERE got_id = 105
I add this extra line for the 2nd version, where the POLYGON is
view parameter generated from the client :
AND
St_Intersects(geo_data,St_Transform(ST_GeomFromText('POLYGON((808771
5420870,810771 5420870,810771 5422870,808771 5422870,808771
5420870))',3857),4326))
Le 20/03/2017 à 17:00, Andrea Aime a écrit :
Hi Gabriel,
it seems your database of choice has real trouble figuring out
a good access plan
for those queries. Hard to say if the db should be able to
just figure out that the
outer spatial index can be executed directly in the inner
query or not without seeing the query.
Normally postgis/postgresql is pretty good with planning,
while Oracle and SQL Server are
rather poor.
Some systems allow to add a place where the current "bbox" can
be expanded as a parameter
transparently (without explicit support). GeoServer could have
it too with some development effort:
https://github.com/geoserver/geoserver/wiki/Successfully-requesting-and-integrating-new-features-and-improvements-in-GeoServer
That said, in your case it seems it's not always a win, I
guess it's a matter of figuring out
how many zoomed in vs zoomed out requests you need to produce.
Maybe you could create two views, one with, one without, bind
them in a layer group, and use scale dependencies to
switch from one to the other.
Cheers
Andrea
On Mon, Mar 20, 2017 at 3:55 PM, Gabriel Vatin
<gabriel.va...@kinaxia.fr <mailto:gabriel.va...@kinaxia.fr>>
wrote:
Hi all,
Some questions about Geoserver performances when using SQL
views as
layers. I've got many layers that are defined from SQL
views directly on
Geoserver (no viewson the database), from Postgres. It
seems that each
time the Geoserver receive a query (single WMS, or multi
WMS coming from
tiled queries in OpenLayers), it does the wole SQL query
for each GET.
And then return the image from the BBOX.
When I query a GetMap for a small area, the whole country
is queried
just to have a small extent.
I tried a new SQL view with a %polygon% attributes : this
adds "AND
ST_Intersects() " between the geometry from this polygon,
and the
geometry of the table. Now I have a strange change :
- for largest images (a whole city), the return time for
GetMap is
divided by 6 (6000ms -> 1000ms)
- for smallest images (a district), the return time for
GetMap is
multiplied by 3 (100ms -> 300ms)
The SQL with the ST_Intersects and the Bbox takes 300ms,
so I guess the
GetMap won't be faster than this...
Thanks for the advice !
Gabriel Vatin
------------------------------------------------------------------------------
Check out the vibrant tech community on one of the world's
most
engaging tech sites, Slashdot.org! http://sdm.link/slashdot
_______________________________________________
Geoserver-devel mailing list
Geoserver-devel@lists.sourceforge.net
<mailto:Geoserver-devel@lists.sourceforge.net>
https://lists.sourceforge.net/lists/listinfo/geoserver-devel
--
==
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.
-------------------------------------------------------
--
Kinaxia
Gabriel VATIN
Docteur en Géomatique
Ingénieur de recherche
Tél : 06.63.92.94.42
Email : gabriel.va...@kinaxia.fr <mailto:gabriel.va...@kinaxia.fr>
http://www.kinaxia.fr
------------------------------------------------------------------------------
Check out the vibrant tech community on one of the world's most
engaging tech sites, Slashdot.org!
http://sdm.link/slashdot_______________________________________________
Geoserver-devel mailing list
Geoserver-devel@lists.sourceforge.net
<mailto:Geoserver-devel@lists.sourceforge.net>
https://lists.sourceforge.net/lists/listinfo/geoserver-devel
--
Kinaxia
Gabriel VATIN
Docteur en Géomatique
Ingénieur de recherche
Tél : 06.63.92.94.42
Email : gabriel.va...@kinaxia.fr <mailto:gabriel.va...@kinaxia.fr>
http://www.kinaxia.fr
------------------------------------------------------------------------------
Check out the vibrant tech community on one of the world's most
engaging tech sites, Slashdot.org! http://sdm.link/slashdot
_______________________________________________
Geoserver-devel mailing list
Geoserver-devel@lists.sourceforge.net
https://lists.sourceforge.net/lists/listinfo/geoserver-devel
