This is an automated email from the ASF dual-hosted git repository.
bchapuis pushed a commit to branch main
in repository https://gitbox.apache.org/repos/asf/incubator-baremaps.git
The following commit(s) were added to refs/heads/main by this push:
new 51b638aa8 Fix performance issue on PG Tile Store (#909, #912)
51b638aa8 is described below
commit 51b638aa8498138f624f6dfb7dc9c5b3def96c32
Author: Bertil Chapuis <[email protected]>
AuthorDate: Thu Jan 9 09:33:29 2025 +0100
Fix performance issue on PG Tile Store (#909, #912)
The query optimizer of Postgres 15 is not able to push conditions down when
using sub-queries. Therefore, we now append the spatial conditions to the
sub-query. The unit tests have been adapted accordingly. A more robust solution
involving a query parser will be devised (#913) in the future to account for
more complex queries (GROUP BY, HAVING, etc.).
---------
Co-authored-by: AlexGacon <[email protected]>
---
.../tilestore/postgres/PostgresTileStore.java | 28 +++++++++++++++++-----
.../tilestore/postgres/PostgresTileStoreTest.java | 2 +-
2 files changed, 23 insertions(+), 7 deletions(-)
diff --git
a/baremaps-core/src/main/java/org/apache/baremaps/tilestore/postgres/PostgresTileStore.java
b/baremaps-core/src/main/java/org/apache/baremaps/tilestore/postgres/PostgresTileStore.java
index 311f86ff4..b43514593 100644
---
a/baremaps-core/src/main/java/org/apache/baremaps/tilestore/postgres/PostgresTileStore.java
+++
b/baremaps-core/src/main/java/org/apache/baremaps/tilestore/postgres/PostgresTileStore.java
@@ -21,7 +21,7 @@ package org.apache.baremaps.tilestore.postgres;
import java.io.ByteArrayOutputStream;
import java.io.OutputStream;
import java.nio.ByteBuffer;
-import java.sql.*;
+import java.sql.ResultSet;
import java.util.Map;
import java.util.concurrent.ConcurrentHashMap;
import java.util.zip.GZIPOutputStream;
@@ -64,7 +64,7 @@ public class PostgresTileStore implements
TileStore<ByteBuffer> {
/**
* A record that holds the sql of a prepared statement and the number of
parameters.
- *
+ *
* @param sql
* @param parameters
*/
@@ -163,10 +163,26 @@ public class PostgresTileStore implements
TileStore<ByteBuffer> {
.replace(";", "")
.replace("?", "??")
.replace("$zoom", String.valueOf(zoom));
+
+ // Append a new condition or a where clause
+ if (querySql.toLowerCase().contains("where")) {
+ querySql += " AND ";
+ } else {
+ querySql += " WHERE ";
+ }
+
+ // Append the condition to the query sql
+ querySql +=
+ "geom IS NOT NULL AND geom && ST_TileEnvelope(?, ?, ?, margin =>
(64.0/4096))";
+
var querySqlWithParams = String.format(
- "SELECT ST_AsMVTGeom(t.geom, ST_TileEnvelope(?, ?, ?)) AS geom,
t.tags - 'id' AS tags, t.id AS id "
- + "FROM (%s) AS t WHERE t.geom IS NOT NULL "
- + "AND t.geom && ST_TileEnvelope(?, ?, ?, margin =>
(64.0/4096))",
+ """
+ SELECT
+ tile.id AS id,
+ tile.tags - 'id' AS tags,
+ ST_AsMVTGeom(tile.geom, ST_TileEnvelope(?, ?, ?)) AS geom
+ FROM (%s) as tile
+ """,
querySql);
layerSql.append(querySqlWithParams);
@@ -201,7 +217,7 @@ public class PostgresTileStore implements
TileStore<ByteBuffer> {
tileSql.append(tileQueryTail);
// Format the sql query
- var sql = tileSql.toString().replace("\n", " ");
+ var sql = tileSql.toString().replaceAll("\\s+", " ");
return new Query(sql, paramCount);
}
diff --git
a/baremaps-core/src/test/java/org/apache/baremaps/tilestore/postgres/PostgresTileStoreTest.java
b/baremaps-core/src/test/java/org/apache/baremaps/tilestore/postgres/PostgresTileStoreTest.java
index e196b37da..bc593c4c8 100644
---
a/baremaps-core/src/test/java/org/apache/baremaps/tilestore/postgres/PostgresTileStoreTest.java
+++
b/baremaps-core/src/test/java/org/apache/baremaps/tilestore/postgres/PostgresTileStoreTest.java
@@ -40,7 +40,7 @@ class PostgresTileStoreTest {
List.of(new TilesetQuery(0, 20, "SELECT id, tags, geom FROM
table")))));
var query = PostgresTileStore.prepareQuery(tileset, 10);
assertEquals(
- "SELECT (SELECT ST_AsMVT(mvtGeom.*, 'a') FROM (SELECT
ST_AsMVTGeom(t.geom, ST_TileEnvelope(?, ?, ?)) AS geom, t.tags - 'id' AS tags,
t.id AS id FROM (SELECT id, tags, geom FROM table) AS t WHERE t.geom IS NOT
NULL AND t.geom && ST_TileEnvelope(?, ?, ?, margin => (64.0/4096))) AS mvtGeom)
|| (SELECT ST_AsMVT(mvtGeom.*, 'b') FROM (SELECT ST_AsMVTGeom(t.geom,
ST_TileEnvelope(?, ?, ?)) AS geom, t.tags - 'id' AS tags, t.id AS id FROM
(SELECT id, tags, geom FROM table) AS t WHERE t.geo [...]
+ "SELECT (SELECT ST_AsMVT(mvtGeom.*, 'a') FROM (SELECT tile.id AS id,
tile.tags - 'id' AS tags, ST_AsMVTGeom(tile.geom, ST_TileEnvelope(?, ?, ?)) AS
geom FROM (SELECT id, tags, geom FROM table WHERE geom IS NOT NULL AND geom &&
ST_TileEnvelope(?, ?, ?, margin => (64.0/4096))) as tile ) AS mvtGeom) ||
(SELECT ST_AsMVT(mvtGeom.*, 'b') FROM (SELECT tile.id AS id, tile.tags - 'id'
AS tags, ST_AsMVTGeom(tile.geom, ST_TileEnvelope(?, ?, ?)) AS geom FROM (SELECT
id, tags, geom FROM table [...]
query.sql());
}
}
