<div dir="ltr">Hi,<div><br></div><div>seems like you're missing a spatial index on the geometry column. </div><div>CREATE INDEX planet_osm_polygon_sidx ON planet_osm_polygon USING GIST(geom);</div><div><br></div><div>If that doesn't help, do: explain analyze verbose <query>; and send the results.</div><div><br></div><div><br></div></div><br><div class="gmail_quote"><div dir="ltr" class="gmail_attr">On Wed, Mar 6, 2019 at 2:56 PM parsis presswala <<a href="mailto:parsispresswala@gmail.com">parsispresswala@gmail.com</a>> wrote:<br></div><blockquote class="gmail_quote" style="margin:0px 0px 0px 0.8ex;border-left:1px solid rgb(204,204,204);padding-left:1ex"><div dir="ltr"><div dir="ltr"><div dir="ltr"><div dir="ltr"><div dir="ltr"><div dir="ltr"><div dir="ltr">Hello,</div><div dir="ltr"><br><div>Yes I am using OSM data. </div><div>I have imported it using osm2pgsql. Database: asiadb and tables are planet_osm_line, planet_osm_point, planet_osm_polygon, planet_osm_roads.</div><div>I have imported data of whole planet using 40 GB .osm.pbf file. </div><div>First I have only imported data of Asia and created map using map-server, which is working fine but as soon as import data of whole world, this problem occurred.</div><div>I have 61 GB RAM and 8 Core processor as i am working on <span style="background-color:rgb(250,243,215);color:rgb(0,0,0);font-family:verdana">Amazon Elastic Compute Cloud (Amazon EC2) type</span><span style="background-color:rgb(250,243,215);color:rgb(0,0,0);font-family:verdana"> </span><span style="background-color:rgb(240,240,240);color:rgb(0,0,0);font-family:verdana;white-space:pre-wrap">r4.2xlarge. </span></div><div><br></div><div>Here is my mapfile.</div><div> <a href="http://13.126.201.198/asiamap.map" target="_blank">http://13.126.201.198/asiamap.map</a></div><div><br></div><div>I have run EXPLAIN <slow request> and got following result:</div><div><div><span style="background-color:rgb(252,229,205)">"Seq Scan on planet_osm_polygon (cost=0.00..18878787.88 rows=36617920 width=277)"</span></div><div><span style="background-color:rgb(252,229,205)">" Filter: ((leisure IS NOT NULL) OR (landuse IS NOT NULL) OR (amenity IS NOT NULL))"</span></div></div><div><br></div></div></div></div></div></div></div></div><br><div class="gmail_quote"><div dir="ltr" class="gmail_attr">On Wed, Mar 6, 2019 at 7:04 PM Yves Jacolin <<a href="mailto:yves.jacolin@camptocamp.com" target="_blank">yves.jacolin@camptocamp.com</a>> wrote:<br></div><blockquote class="gmail_quote" style="margin:0px 0px 0px 0.8ex;border-left:1px solid rgb(204,204,204);padding-left:1ex"><div dir="ltr"><div>Some questions that can help you :)</div><div><br></div><div>is it OSM data? How do you import them?</div><div>What is the extent of the data? Could you check if spatial indexes exist? Could you run EXPLAIN <your slow request> and analyze it?</div><div><br></div><div>Y.<br></div></div><br><div class="gmail_quote"><div dir="ltr" class="gmail_attr">Le mer. 6 mars 2019 à 14:29, parsis presswala <<a href="mailto:parsispresswala@gmail.com" target="_blank">parsispresswala@gmail.com</a>> a écrit :<br></div><blockquote class="gmail_quote" style="margin:0px 0px 0px 0.8ex;border-left:1px solid rgb(204,204,204);padding-left:1ex"><div dir="ltr">Hello,<div><br></div><div>Here is a log of PostgreSQL. I consider that PostgreSQL takes very long time to execute query on polygon. What should I do fot it?</div><div><br></div><div><div><span style="background-color:rgb(252,229,205)">2019-03-06 11:23:49.620 UTC [2302] parsis@asiadb STATEMENT: select "highway"::text,"name"::text,"reflen"::text,"ref"::text,ST_AsBinary(ST_Force2D("way"),'NDR') as geom,"nullid"::text from (select *, NULL as nullid from ( SELECT way, highway, name, ref, oneway, CHAR_LENGTH(ref) AS reflen FROM planet_osm_line WHERE highway IN ('motorway', 'trunk','motorway_link','trunk_link') AND (name IS NOT NULL OR ref IS NOT NULL)) AS data) as nullidq where "way" && ST_GeomFromText('POLYGON((-9473834.25396072 2853929.66787328,-9473834.25396072 3446928.13330971,-8880835.78852428 3446928.13330971,-8880835.78852428 2853929.66787328,-9473834.25396072 2853929.66787328))',900913)</span></div><div><span style="background-color:rgb(252,229,205)">2019-03-06 11:24:14.911 UTC [2592] parsis@asiadb LOG: could not send data to client: Broken pipe</span></div><div><span style="background-color:rgb(252,229,205)">2019-03-06 11:24:14.911 UTC [2592] parsis@asiadb STATEMENT: select "stylegroup"::text,ST_AsBinary(ST_Force2D("way"),'NDR') as geom,"nullid"::text from (select *, NULL as nullid from ( SELECT way, CASE WHEN leisure in ('park','garden','playground','golf_course','sports_centre','pitch','stadium','nature_reserve','common') THEN 'leisuree' WHEN landuse IN('park','forest','grass','farmyard','farm','farmland','wood','meadow','village_green','recreation_ground') THEN 'landusee' WHEN amenity in ('university','school','college','library','fuel','parking','cinema','theatre','place_of_worship','hospital') THEN 'amenityy' END AS stylegroup FROM planet_osm_polygon where (leisure is not null) or (landuse is not null) or (amenity is not null)) AS data) as nullidq where "way" && ST_GeomFromText('POLYGON((8116389.75422228 -1276192.28146022,8116389.75422228 3467795.44203122,12860377.4777137 3467795.44203122,12860377.4777137 -1276192.28146022,8116389.75422228 -1276192.28146022))',900913)</span></div><div><span style="background-color:rgb(252,229,205)">2019-03-06 11:24:14.911 UTC [2617] parsis@asiadb LOG: could not send data to client: Broken pipe</span></div><div><span style="background-color:rgb(252,229,205)">2019-03-06 11:24:14.911 UTC [2617] parsis@asiadb STATEMENT: select "stylegroup"::text,ST_AsBinary(ST_Force2D("way"),'NDR') as geom,"nullid"::text from (select *, NULL as nullid from ( SELECT way, CASE WHEN leisure in ('park','garden','playground','golf_course','sports_centre','pitch','stadium','nature_reserve','common') THEN 'leisuree' WHEN landuse IN('park','forest','grass','farmyard','farm','farmland','wood','meadow','village_green','recreation_ground') THEN 'landusee' WHEN amenity in ('university','school','college','library','fuel','parking','cinema','theatre','place_of_worship','hospital') THEN 'amenityy' END AS stylegroup FROM planet_osm_polygon where (leisure is not null) or (landuse is not null) or (amenity is not null)) AS data) as nullidq where "way" && ST_GeomFromText('POLYGON((8116389.75422228 -1276192.28146022,8116389.75422228 3467795.44203122,12860377.4777137 3467795.44203122,12860377.4777137 -1276192.28146022,8116389.75422228 -1276192.28146022))',900913)</span></div><div><span style="background-color:rgb(252,229,205)">2019-03-06 11:24:14.911 UTC [2592] parsis@asiadb FATAL: connection to client lost</span></div><div><span style="background-color:rgb(252,229,205)">2019-03-06 12:08:25.564 UTC [3362] parsis@asiadb STATEMENT: select ST_AsBinary(ST_Force2D("way"),'NDR') as geom,"nullid"::text from (select *, NULL as nullid from ( SELECT way FROM planet_osm_polygon WHERE (waterway IN ('riverbank')) OR ("natural" IN ('water')) OR (landuse IN ('basin', 'reservoir')) ) AS data) as nullidq where "way" && ST_GeomFromText('POLYGON((-20228294.8225998 -20228294.8225998,-20228294.8225998 17723606.9653297,17723606.9653297 17723606.9653297,17723606.9653297 -20228294.8225998,-20228294.8225998 -20228294.8225998))',900913)</span></div><div><span style="background-color:rgb(252,229,205)">2019-03-06 12:08:25.564 UTC [3260] FATAL: terminating connection due to administrator command</span></div><div><span style="background-color:rgb(252,229,205)">2019-03-06 12:08:25.564 UTC [3260] STATEMENT: select ST_AsBinary(ST_Force2D("way"),'NDR') as geom,"nullid"::text from (select *, NULL as nullid from ( SELECT way FROM planet_osm_polygon WHERE (waterway IN ('riverbank')) OR ("natural" IN ('water')) OR (landuse IN ('basin', 'reservoir')) ) AS data) as nullidq where "way" && ST_GeomFromText('POLYGON((-20228294.8225998 -20228294.8225998,-20228294.8225998 17723606.9653297,17723606.9653297 17723606.9653297,17723606.9653297 -20228294.8225998,-20228294.8225998 -20228294.8225998))',900913)</span></div><div><span style="background-color:rgb(252,229,205)">2019-03-06 12:08:25.564 UTC [3261] FATAL: terminating connection due to administrator command</span></div><div><span style="background-color:rgb(252,229,205)">2019-03-06 12:08:25.564 UTC [3261] STATEMENT: select ST_AsBinary(ST_Force2D("way"),'NDR') as geom,"nullid"::text from (select *, NULL as nullid from ( SELECT way FROM planet_osm_polygon WHERE (waterway IN ('riverbank')) OR ("natural" IN ('water')) OR (landuse IN ('basin', 'reservoir')) ) AS data) as nullidq where "way" && ST_GeomFromText('POLYGON((-20228294.8225998 -20228294.8225998,-20228294.8225998 17723606.9653297,17723606.9653297 17723606.9653297,17723606.9653297 -20228294.8225998,-20228294.8225998 -20228294.8225998))',900913)</span></div><div><span style="background-color:rgb(252,229,205)">2019-03-06 12:08:25.564 UTC [3363] FATAL: terminating connection due to administrator command</span></div><div><span style="background-color:rgb(252,229,205)">2019-03-06 12:08:25.564 UTC [3363] STATEMENT: select ST_AsBinary(ST_Force2D("way"),'NDR') as geom,"nullid"::text from (select *, NULL as nullid from ( SELECT way FROM planet_osm_polygon WHERE (waterway IN ('riverbank')) OR ("natural" IN ('water')) OR (landuse IN ('basin', 'reservoir')) ) AS data) as nullidq where "way" && ST_GeomFromText('POLYGON((-20228294.8225998 -20228294.8225998,-20228294.8225998 17723606.9653297,17723606.9653297 17723606.9653297,17723606.9653297 -20228294.8225998,-20228294.8225998 -20228294.8225998))',900913)</span></div><div><span style="background-color:rgb(252,229,205)">2019-03-06 12:08:25.570 UTC [1535] LOG: worker process: parallel worker for PID 3255 (PID 3261) exited with exit code 1</span></div><div><span style="background-color:rgb(252,229,205)">2019-03-06 12:08:25.570 UTC [1535] LOG: worker process: parallel worker for PID 3362 (PID 3363) exited with exit code 1</span></div><div><span style="background-color:rgb(252,229,205)">2019-03-06 12:08:25.570 UTC [1535] LOG: worker process: parallel worker for PID 3255 (PID 3260) exited with exit code 1</span></div><div><span style="background-color:rgb(252,229,205)">2019-03-06 12:08:25.609 UTC [3426] parsis@asiadb LOG: could not send data to client: Broken pipe</span></div><div><span style="background-color:rgb(252,229,205)">2019-03-06 12:08:25.609 UTC [3426] parsis@asiadb STATEMENT: select "admin_level"::text,ST_AsBinary(ST_Force2D("way"),'NDR') as geom,"nullid"::text from (select *, NULL as nullid from (select * from planet_osm_line where admin_level IN('2') and boundary IN ('administrative') and way NOT IN (SELECT way FROM planet_osm_line where admin_level IN('2') and boundary like 'administrative' and (tags->'dispute' = 'yes' or tags->'ISO3166-1' IN ('IN','CN','PK') or way IN(select way from in_cn_ignored_border)))) AS data) as nullidq where "way" && ST_GeomFromText('POLYGON((-20228294.8225998 -20228294.8225998,-20228294.8225998 17723606.9653297,17723606.9653297 17723606.9653297,17723606.9653297 -20228294.8225998,-20228294.8225998 -20228294.8225998))',900913)</span></div><div><span style="background-color:rgb(252,229,205)">2019-03-06 12:08:25.609 UTC [3426] parsis@asiadb FATAL: connection to client lost</span></div></div><div><span style="background-color:rgb(252,229,205)"><br></span></div><div><div>Regards,</div><div>Parsis Presswala</div></div></div><br><div class="gmail_quote"><div dir="ltr" class="gmail_attr">On Wed, Mar 6, 2019 at 6:35 PM Yves Jacolin <<a href="mailto:yves.jacolin@camptocamp.com" target="_blank">yves.jacolin@camptocamp.com</a>> wrote:<br></div><blockquote class="gmail_quote" style="margin:0px 0px 0px 0.8ex;border-left:1px solid rgb(204,204,204);padding-left:1ex"><div dir="ltr"><div>Hello,</div><div><br></div><div>Did you try to curl the request and see what happens: timeout, etc. If so, try to get some log (mapserver or postgresql log) and see where is the bottleneck.</div><div><br></div><div>Y.<br></div></div><br><div class="gmail_quote"><div dir="ltr" class="gmail_attr">Le mer. 6 mars 2019 à 13:58, parsis presswala <<a href="mailto:parsispresswala@gmail.com" target="_blank">parsispresswala@gmail.com</a>> a écrit :<br></div><blockquote class="gmail_quote" style="margin:0px 0px 0px 0.8ex;border-left:1px solid rgb(204,204,204);padding-left:1ex"><div dir="ltr"><div dir="ltr"><div dir="ltr">Hello,<br>I am getting following error while generating tiles. I can't get the reason of this error. I an using mapcache to generate tiles.</div><div dir="ltr"><br><div><div><span style="background-color:rgb(207,226,243)">failed to seed tile z10,x645,y510:</span></div><div><span style="background-color:rgb(207,226,243)">curl failed to request url <a href="http://13.126.201.198/cgi-bin/mapserv?VERSION=1.1.1&REQUEST=GetMap&SERVICE=WMS&STYLES=&BBOX=5201998.739765%2c-81328.655306%2c5795150.079258%2c511822.684187&WIDTH=3880&HEIGHT=3880&FORMAT=image%2fpng&SRS=EPSG%3a3857&LAYERS=osm_new_admin%2cosm_new_adminocean%2cosm_new_landusages%2cosm_new_water%2cbuildings%2caeroway%2cosm_new_waterways%2cosm_new_railways%2cplanet_osm_roads%2cplanet_osm_roads_line%2cosm_new_railways1%2ccountry_line%2ccountry_line_claim%2cstate_line%2cmotorway_label%2cmainroad_label%2cminorroad_label%2cosm_new_railways_symbol%2cplace-country%2cplace-state%2cplace-city%2cplace-town%2cplace-Suburb%2cplace-village%2cplace-locality%2carea_label%2caerodrome_label%2cwaterway_label%2cwaterarea_label%2cpoi_pointlabel&MAP=%2fhome%2fubuntu%2fworldmap%2fasiamap.map" target="_blank">http://13.126.201.198/cgi-bin/mapserv?VERSION=1.1.1&REQUEST=GetMap&SERVICE=WMS&STYLES=&BBOX=5201998.739765%2c-81328.655306%2c5795150.079258%2c511822.684187&WIDTH=3880&HEIGHT=3880&FORMAT=image%2fpng&SRS=EPSG%3a3857&LAYERS=osm_new_admin%2cosm_new_adminocean%2cosm_new_landusages%2cosm_new_water%2cbuildings%2caeroway%2cosm_new_waterways%2cosm_new_railways%2cplanet_osm_roads%2cplanet_osm_roads_line%2cosm_new_railways1%2ccountry_line%2ccountry_line_claim%2cstate_line%2cmotorway_label%2cmainroad_label%2cminorroad_label%2cosm_new_railways_symbol%2cplace-country%2cplace-state%2cplace-city%2cplace-town%2cplace-Suburb%2cplace-village%2cplace-locality%2carea_label%2caerodrome_label%2cwaterway_label%2cwaterarea_label%2cpoi_pointlabel&MAP=%2fhome%2fubuntu%2fworldmap%2fasiamap.map</a> : Operation timed out after 600000 milliseconds with 0 bytes received</span></div><div><span style="background-color:rgb(207,226,243)"><br></span></div><div><span style="background-color:rgb(207,226,243)">aborting seed as 100.0% of the last 1000 requests failed</span></div><div><span style="background-color:rgb(207,226,243)"><br></span></div><div><span style="background-color:rgb(207,226,243)">seeded 225 tiles, now at z10 x660 y510</span></div><div><span style="background-color:rgb(207,226,243)">seeded 2 metatiles (450 total tiles, 450 non-empty tiles) in 1201.8 seconds at 0.4 tiles/sec (0.4 non-empty tiles/sec)</span></div></div><div><span style="background-color:rgb(207,226,243)"><br></span></div><div>If anyone could help me with this, it would be greatly appreciated.<br></div><div><br></div><div>Regards,</div><div>Parsis Presswala</div></div></div></div>
_______________________________________________<br>
mapserver-users mailing list<br>
<a href="mailto:mapserver-users@lists.osgeo.org" target="_blank">mapserver-users@lists.osgeo.org</a><br>
<a href="https://lists.osgeo.org/mailman/listinfo/mapserver-users" rel="noreferrer" target="_blank">https://lists.osgeo.org/mailman/listinfo/mapserver-users</a></blockquote></div><br clear="all"><br>-- <br><div dir="ltr" class="gmail-m_-6444661847870941735gmail-m_-4039563203088798649gmail-m_-3043632377927853859gmail-m_3989708336400865456gmail_signature"><div dir="ltr"><div><div dir="ltr"><div><div dir="ltr"><div><div dir="ltr"><div><div dir="ltr"><div><div dir="ltr"><div><div dir="ltr"><div>Yves Jacolin</div><div>Training and support manager - Team Manager<br>Camptocamp<br><br>Tel (France) : +33 4 58 48 20 43<br>Tel (Switzerland) : +41 21 619 10 43<br>Mob. : +33 6 18 75 42 21<br><br>email : <a href="mailto:yves.jacolin@camptocamp.com" target="_blank">yves.jacolin@camptocamp.com</a><br><a href="http://www.camptocamp.com" target="_blank">http://www.camptocamp.com</a></div></div></div></div></div></div></div></div></div></div></div></div></div></div></div>
</blockquote></div>
</blockquote></div><br clear="all"><br>-- <br><div dir="ltr" class="gmail-m_-6444661847870941735gmail-m_-4039563203088798649gmail_signature"><div dir="ltr"><div><div dir="ltr"><div><div dir="ltr"><div><div dir="ltr"><div><div dir="ltr"><div><div dir="ltr"><div><div dir="ltr"><div>Yves Jacolin</div><div>Training and support manager - Team Manager<br>Camptocamp<br><br>Tel (France) : +33 4 58 48 20 43<br>Tel (Switzerland) : +41 21 619 10 43<br>Mob. : +33 6 18 75 42 21<br><br>email : <a href="mailto:yves.jacolin@camptocamp.com" target="_blank">yves.jacolin@camptocamp.com</a><br><a href="http://www.camptocamp.com" target="_blank">http://www.camptocamp.com</a></div></div></div></div></div></div></div></div></div></div></div></div></div></div></div>
</blockquote></div>
_______________________________________________<br>
mapserver-users mailing list<br>
<a href="mailto:mapserver-users@lists.osgeo.org" target="_blank">mapserver-users@lists.osgeo.org</a><br>
<a href="https://lists.osgeo.org/mailman/listinfo/mapserver-users" rel="noreferrer" target="_blank">https://lists.osgeo.org/mailman/listinfo/mapserver-users</a></blockquote></div>