-
Notifications
You must be signed in to change notification settings - Fork 1.3k
Closed
Description
This issue is related to orbisgis/geoclimate#876
It seems that the spatial index is not used for some SQL query.
Here are two tests showing the execution plan.
thanks
Statement stat = connection.createStatement();
//Prepare data
stat.execute("DROP TABLE IF EXISTS WAYS_POLYGONS, OSM;" +
"CREATE TABLE WAYS_POLYGONS AS SELECT 1 as ID_WAY, 'POLYGON ((160 280, 240 280, 240 140, 160 140, 160 280))'::GEOMETRY as the_geom;" +
"CREATE TABLE OSM AS SELECT 1 as ID_WAY, 'aeroway' AS TAG_KEY, 'aeroway' AS TAG_VALUE; " +
"CREATE INDEX ON WAYS_POLYGONS(ID_WAY);" +
"CREATE SPATIAL INDEX ON WAYS_POLYGONS(THE_GEOM);" +
"CREATE INDEX ON OSM(ID_WAY);" +
"CREATE INDEX ON OSM(TAG_VALUE);" +
"CREATE INDEX ON OSM(TAG_KEY)");
ResultSet res = stat.executeQuery("EXPLAIN SELECT 'w'||a.id_way AS id, a.the_geom , MAX(CASE WHEN b.tag_key = 'aeroway' THEN b.tag_value END) AS \"aeroway\",MAX(CASE WHEN b.tag_key = 'agricultural' THEN b.tag_value END) AS \"agricultural\"," +
" FROM WAYS_POLYGONS AS a, OSM b WHERE a.id_way=b.id_way AND b.TAG_KEY IN ('height','roof:height','building:levels','roof:levels','building','amenity','layer','aeroway','historic','leisure','monument','wall') " +
"and a.the_geom && 'POLYGON ((198.5 186.5, 269.5 186.5, 269.5 115, 198.5 115, 198.5 186.5))'::geometry " +
" GROUP BY a.id_way;" +
" ");
res.next();
System.out.println("\n No spatial index in the plan \n"+ res.getString(1));
res = stat.executeQuery("EXPLAIN SELECT 'w'||a.id_way AS id, a.the_geom , MAX(CASE WHEN b.tag_key = 'aeroway' THEN b.tag_value END) AS \"aeroway\",MAX(CASE WHEN b.tag_key = 'agricultural' THEN b.tag_value END) AS \"agricultural\"," +
" FROM WAYS_POLYGONS AS a, OSM b WHERE a.id_way=b.id_way AND b.TAG_KEY IN ('height','roof:height','building:levels','roof:levels','building','amenity','layer','aeroway','historic','leisure','monument','wall') " +
" GROUP BY a.id_way;");
res.next();
System.out.println("\n Query without spatial filter to compare \n"+ res.getString(1));Metadata
Metadata
Assignees
Labels
No labels