Skip to content

Wrong estimation of a query execution cost with spatial index #3940

@ebocher

Description

@ebocher

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

No one assigned

    Labels

    No labels
    No labels

    Type

    No type

    Projects

    No projects

    Milestone

    No milestone

    Relationships

    None yet

    Development

    No branches or pull requests

    Issue actions