Hi, can you please share your query plans?
also, `distinct on` is usually a sign of not well thought logic, a lateral join with a subquery with limit 1 can be much more performant than producing extra rows in possibly wrong join order and then getting rid of them. пт, 30 мар. 2018 г. в 14:46, sav123 <[email protected]>: > Hello All, > > context : postgres database, cartoocss > > I was trying to build the exact table for the cartoo css layer > "turning_circle_casing". > It is a join between the tables line and point at high zoom. > > sql from cartoo css authors embedded in a create table by a script : > CREATE TABLE IF NOT EXISTS turning_circle_casing_raw AS select * from > (SELECT DISTINCT ON (p.way) > p.way AS way, l.highway AS int_tc_type, > CASE WHEN l.service IN ('parking_aisle', 'drive-through', 'driveway') > THEN 'INT-minor'::text > ELSE 'INT-normal'::text > END AS int_tc_service,v.prio > FROM planet_osm_point p > JOIN planet_osm_line l ON ST_DWithin(p.way, l.way, 0.1) > JOIN (VALUES > ('tertiary', 1), > ('unclassified', 2), > ('residential', 3), > ('living_street', 4), > ('service', 5) > ) AS v (highway, prio) > ON v.highway=l.highway > WHERE p.highway = 'turning_circle' > OR p.highway = 'turning_loop' > ) as creator WHERE ST_IsValid(way) ORDER BY > ST_GeoHash(ST_Transform(ST_Envelope(way),4326),10) COLLATE "C"; > > 1,597,250 selected rows, 28800 seconds to build , 50 seconds to index on > geometry col. > > Then I produced an intermediate table to divide by 60 one of the tables : > CREATE TABLE IF NOT EXISTS planet_osm_point_casing AS ( select way from > planet_osm_point where highway in ('turning_circle','turning_loop') ) ; > CREATE INDEX IF NOT EXISTS planet_osm_point_casing_i ON > planet_osm_point_casing USING GIST (way) ; > ANALYZE planet_osm_point_casing; > > 3 or 4 minutes to get the new table with 1,631,000 record instead of > the original 103,755,000. > > > The request execution time was reduced to 25000 seconds. Still too much. > > Then I built a regular grid table 256 x 256 from -20037508 to 20037508 on x > and on y > insert into local_join_grid values > ('BOX3D(xmin ymin,xmax ymax)'::box3d), etc ... > looping on xmin ymin,xmax ymax to get 65536 records. > > and I modified the above request like that : > > CREATE TABLE IF NOT EXISTS turning_circle_casing_raw AS select * from > ( > SELECT DISTINCT ON (p.way) > p.way AS way, l.highway AS int_tc_type, > CASE WHEN l.service IN ('parking_aisle', 'drive-through', 'driveway') > THEN 'INT-minor'::text > ELSE 'INT-normal'::text > END AS int_tc_service,v.prio > FROM local_join_grid b > JOIN planet_osm_point_casing p on p.way && ST_SetSRID(b.bbox, 3857) > JOIN planet_osm_line l ON l.way && ST_SetSRID(b.bbox, 3857) and > l.highway in ('tertiary', > 'unclassified','residential','living_street','service') and > ST_DWithin(p.way, l.way, 0.1) > JOIN (VALUES > ('tertiary', 1), > ('unclassified', 2), > ('residential', 3), > ('living_street', 4), > ('service', 5) > ) AS v (highway, prio) > ON v.highway=l.highway > ) as creator WHERE ST_IsValid(way) ORDER BY > ST_GeoHash(ST_Transform(ST_Envelope(way),4326),10) COLLATE "C"; > > and I got the result in 1802 s, index in 43 s. It is 15 times faster. > I suppose that the optimal grid size depends of the hardware and the > postgresql configuration. > > > The comparison between the 2 results shows some rows missing. It is because > the grid which is not correct. It cuts streets. > I know 1 or 2 heavy heurisitics to build a good grid if it doesn't exist. > I could also start with existent boundaries. > > Does the grid exist ? some ideas ? > > :) > Igael > > > > > > > > > _______________________________________________ > dev mailing list > [email protected] > https://lists.openstreetmap.org/listinfo/dev >
_______________________________________________ dev mailing list [email protected] https://lists.openstreetmap.org/listinfo/dev
