You are viewing a plain text version of this content. The canonical link for it is here.
Posted to commits@baremaps.apache.org by bc...@apache.org on 2023/10/25 11:25:57 UTC
[incubator-baremaps] branch optimize-postgres-tilestore updated: Improve query genration in tile store
This is an automated email from the ASF dual-hosted git repository.
bchapuis pushed a commit to branch optimize-postgres-tilestore
in repository https://gitbox.apache.org/repos/asf/incubator-baremaps.git
The following commit(s) were added to refs/heads/optimize-postgres-tilestore by this push:
new 3f5dc061 Improve query genration in tile store
3f5dc061 is described below
commit 3f5dc0613b20e7016c30b214003be4097fa68ed5
Author: Bertil Chapuis <bc...@gmail.com>
AuthorDate: Wed Oct 25 13:25:51 2023 +0200
Improve query genration in tile store
---
.../tilestore/postgres/PostgresTileStore.java | 270 ++++++++++++---------
.../org/apache/baremaps/utils/PostgresUtils.java | 11 +-
2 files changed, 168 insertions(+), 113 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 4e935846..e02702b0 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
@@ -18,23 +18,30 @@
package org.apache.baremaps.tilestore.postgres;
-import java.io.ByteArrayOutputStream;
-import java.io.OutputStream;
-import java.nio.ByteBuffer;
-import java.sql.Connection;
-import java.sql.ResultSet;
-import java.sql.Statement;
-import java.util.Map;
-import java.util.zip.GZIPOutputStream;
-import javax.sql.DataSource;
import org.apache.baremaps.tilestore.TileCoord;
import org.apache.baremaps.tilestore.TileStore;
import org.apache.baremaps.tilestore.TileStoreException;
+import org.apache.baremaps.vectortile.Tile;
import org.apache.baremaps.vectortile.tileset.Tileset;
import org.apache.baremaps.vectortile.tileset.TilesetQuery;
import org.slf4j.Logger;
import org.slf4j.LoggerFactory;
+import javax.sql.DataSource;
+import java.io.ByteArrayOutputStream;
+import java.io.IOException;
+import java.io.OutputStream;
+import java.nio.ByteBuffer;
+import java.sql.*;
+import java.util.Collection;
+import java.util.HashMap;
+import java.util.Map;
+import java.util.Set;
+import java.util.concurrent.ConcurrentHashMap;
+import java.util.concurrent.ConcurrentMap;
+import java.util.function.BiFunction;
+import java.util.zip.GZIPOutputStream;
+
/**
* A read-only {@code TileStore} implementation that uses the PostgreSQL to generate vector tiles.
* This {@code TileStore} combines the input queries, identifies common table expressions (CTE), and
@@ -42,126 +49,167 @@ import org.slf4j.LoggerFactory;
*/
public class PostgresTileStore implements TileStore {
- private static final Logger logger = LoggerFactory.getLogger(PostgresTileStore.class);
+ private static final Logger logger = LoggerFactory.getLogger(PostgresTileStore.class);
- private final DataSource datasource;
+ private final DataSource datasource;
- private final Tileset tileset;
+ private final Tileset tileset;
- public PostgresTileStore(DataSource datasource, Tileset tileset) {
- this.datasource = datasource;
- this.tileset = tileset;
- }
+ public PostgresTileStore(DataSource datasource, Tileset tileset) {
+ this.datasource = datasource;
+ this.tileset = tileset;
+ }
- protected boolean zoomPredicate(TilesetQuery query, int zoom) {
- return query.getMinzoom() <= zoom && zoom < query.getMaxzoom();
- }
+ private Map<Integer, TileQuery> cache = new ConcurrentHashMap<>();
- public String withQuery(TileCoord tileCoord) {
- var layers = tileset.getVectorLayers().stream()
- .map(layer -> Map.entry(layer.getId(), layer.getQueries().stream()
- .filter(
- query -> query.getMinzoom() <= tileCoord.z() && tileCoord.z() < query.getMaxzoom())
- .toList()))
- .filter(entry -> entry.getValue().size() > 0)
- .toList();
+ @Override
+ public ByteBuffer read(TileCoord tileCoord) throws TileStoreException {
+ var query = cache.computeIfAbsent(tileCoord.z(), z -> prepareQuery(tileset, z));
+ try (var connection = datasource.getConnection()) {
+ return query.execute(connection, tileCoord);
+ } catch (Exception e) {
+ throw new TileStoreException(e);
+ }
+ }
- var queryBuilder = new StringBuilder();
- queryBuilder.append("SELECT (");
+ /**
+ * This operation is not supported.
+ */
+ @Override
+ public void write(TileCoord tileCoord, ByteBuffer blob) {
+ throw new UnsupportedOperationException("The postgis tile store is read only");
+ }
- for (int i = 0; i < layers.size(); i++) {
- var layer = layers.get(i);
- var layerId = layer.getKey();
- var layerQueries = layer.getValue().stream()
- .filter(layerQuery -> zoomPredicate(layerQuery, tileCoord.z())).toList();
+ /**
+ * This operation is not supported.
+ */
+ @Override
+ public void delete(TileCoord tileCoord) {
+ throw new UnsupportedOperationException("The postgis tile store is read only");
+ }
- if (layerQueries.size() > 0) {
- if (i > 0) {
- queryBuilder.append(" || ");
- }
- var sqlBuilder = new StringBuilder();
- sqlBuilder.append("(WITH mvtgeom AS (\n");
-
- for (int j = 0; j < layerQueries.size(); j++) {
- if (j != 0) {
- sqlBuilder.append("UNION\n");
- }
- var layerQuery = layerQueries.get(j).getSql().replace(";", "");
- sqlBuilder.append(String.format("""
- SELECT ST_AsMVTGeom(t.geom, ST_TileEnvelope(%d, %d, %d)) AS geom, t.tags, t.id
- FROM (%s) AS t
- WHERE t.geom && ST_TileEnvelope(%d, %d, %d, margin => (64.0/4096))
- """,
- tileCoord.z(), tileCoord.x(), tileCoord.y(),
- layerQuery,
- tileCoord.z(), tileCoord.x(), tileCoord.y()));
+ public static TileQuery prepareQuery(Tileset tileset, int zoom) {
+ // Initialize a builder for the tile query
+ var tileQuery = new StringBuilder();
+ tileQuery.append("SELECT (");
+
+ // Iterate over the layers and keep track of the number of layers and parameters included in the final query
+ var layers = tileset.getVectorLayers();
+ var layerCount = 0;
+ var paramCount = 0;
+ for (var layer : layers) {
+
+ // Initialize a builder for the layer query
+ var layerQuery = new StringBuilder();
+ var layerHead = "(WITH mvtGeom AS (";
+ layerQuery.append(layerHead);
+
+ // Iterate over the queries and keep track of the number of queries included in the final query
+ var queries = layer.getQueries();
+ var queryCount = 0;
+ for (var query : queries) {
+
+ // Only include the query if the zoom level is in the range
+ if (query.getMinzoom() <= zoom && zoom < query.getMaxzoom()) {
+
+ // Add a union between queries
+ if (queryCount > 0) {
+ layerQuery.append("UNION ");
+ }
+
+ // Add the query to the layer query
+ var sql = query.getSql()
+ .replace(";", "")
+ .replace("?", "??")
+ .replace("$zoom", String.valueOf(zoom));
+ var queryWithParams = String.format("""
+ SELECT ST_AsMVTGeom(t.geom, ST_TileEnvelope(?, ?, ?)) AS geom, t.tags, t.id
+ FROM (%s) AS t
+ WHERE t.geom && ST_TileEnvelope(?, ?, ?, margin => (64.0/4096))
+ """, sql);
+ layerQuery.append(queryWithParams);
+
+ // Increase the parameter count (e.g. ?) and query count
+ paramCount += 6;
+ queryCount++;
+ }
+ }
+
+ // Add the tail of the layer query
+ var layerQueryTail = String.format(") SELECT ST_AsMVT(mvtGeom.*, '%s') FROM mvtGeom)", layer.getId());
+ layerQuery.append(layerQueryTail);
+
+ // Only include the layer query if queries were included for this layer
+ if (queryCount > 0) {
+
+ // Add the concatenation between layer queries
+ if (layerCount > 0) {
+ tileQuery.append(" || ");
+ }
+
+ // Add the layer query to the mvt query
+ tileQuery.append(layerQuery);
+
+ // Increase the layer count
+ layerCount++;
+ }
}
- queryBuilder.append(sqlBuilder)
- .append(String.format(") SELECT ST_AsMVT(mvtgeom.*, '%s') FROM mvtgeom\n)", layerId));
- }
- }
- queryBuilder.append(") mvtTile");
- return queryBuilder.toString().replace("$zoom", String.valueOf(tileCoord.z()));
- }
+ // Add the tail of the tile query
+ var tileQueryTail = ") mvtTile";
+ tileQuery.append(tileQueryTail);
- @Override
- public ByteBuffer read(TileCoord tileCoord) throws TileStoreException {
- String query = withQuery(tileCoord);
+ // Log the resulting query
+ var query = tileQuery.toString().replace("\n", " ");
+ logger.debug("query: {}", query);
- logger.debug("Executing query: {}", query);
+ return new TileQuery(query, paramCount);
+ }
- long start = System.currentTimeMillis();
+ public static class TileQuery {
- try (Connection connection = datasource.getConnection();
- Statement statement = connection.createStatement();
- ResultSet resultSet = statement.executeQuery(query);
- ByteArrayOutputStream data = new ByteArrayOutputStream()) {
+ private final String query;
- int length = 0;
+ private final int paramCount;
+
+ public TileQuery(String query, int paramCount) {
+ this.query = query;
+ this.paramCount = paramCount;
+ }
- try (OutputStream gzip = new GZIPOutputStream(data)) {
- while (resultSet.next()) {
- byte[] bytes = resultSet.getBytes(1);
- length += bytes.length;
- gzip.write(bytes);
+ public ByteBuffer execute(Connection connection, TileCoord tileCoord) throws SQLException, IOException {
+ long start = System.currentTimeMillis();
+ try (var statement = connection.prepareStatement(query)) {
+
+ // Set the parameters for the tile
+ for (int i = 0; i < paramCount; i += 3) {
+ statement.setInt(i + 1, tileCoord.z());
+ statement.setInt(i + 2, tileCoord.x());
+ statement.setInt(i + 3, tileCoord.y());
+ }
+
+ // Fetch and compress the tile data
+ try (ByteArrayOutputStream data = new ByteArrayOutputStream();) {
+ try (ResultSet resultSet = statement.executeQuery();
+ OutputStream gzip = new GZIPOutputStream(data)) {
+ while (resultSet.next()) {
+ byte[] bytes = resultSet.getBytes(1);
+ gzip.write(bytes);
+ }
+ }
+ return ByteBuffer.wrap(data.toByteArray());
+
+ } finally {
+ // Log slow queries (> 10s)
+ long stop = System.currentTimeMillis();
+ long duration = stop - start;
+ if (duration > 10_000) {
+ logger.warn("Executed query for tile {} in {} ms", tileCoord, duration);
+ }
+ }
+ }
}
- }
-
- long stop = System.currentTimeMillis();
- long duration = stop - start;
-
- // Log slow queries (> 10s)
- if (duration > 10_000) {
- logger.warn("Executed query for tile {} in {} ms: {}", tileCoord, duration, query);
- }
-
- if (length > 0) {
- return ByteBuffer.wrap(data.toByteArray());
- } else {
- return ByteBuffer.allocate(0);
- }
- } catch (Exception e) {
- logger.error(e.getMessage());
- throw new TileStoreException(e);
}
- }
-
- /**
- * This operation is not supported.
- */
- @Override
- public void write(TileCoord tileCoord, ByteBuffer blob) {
- throw new UnsupportedOperationException("The postgis tile store is read only");
- }
-
- /**
- * This operation is not supported.
- */
- @Override
- public void delete(TileCoord tileCoord) {
- throw new UnsupportedOperationException("The postgis tile store is read only");
- }
}
diff --git a/baremaps-core/src/main/java/org/apache/baremaps/utils/PostgresUtils.java b/baremaps-core/src/main/java/org/apache/baremaps/utils/PostgresUtils.java
index cacfab00..681743fa 100644
--- a/baremaps-core/src/main/java/org/apache/baremaps/utils/PostgresUtils.java
+++ b/baremaps-core/src/main/java/org/apache/baremaps/utils/PostgresUtils.java
@@ -100,10 +100,13 @@ public final class PostgresUtils {
if (poolSize < 1) {
throw new IllegalArgumentException("PoolSize cannot be inferior to 1");
}
- var multiQueriesJdbcUrl = withAllowMultiQueriesParameter(jdbcUrl);
+
var config = new HikariConfig();
- config.setJdbcUrl(multiQueriesJdbcUrl);
+ config.setJdbcUrl(jdbcUrl);
config.setMaximumPoolSize(poolSize);
+ config.addDataSourceProperty("allowMultiQueries", true);
+ config.addDataSourceProperty("prepareThreshold", 100);
+
return new HikariDataSource(config);
}
@@ -154,6 +157,10 @@ public final class PostgresUtils {
if (datasource.getReadOnly() != null) {
config.setReadOnly(datasource.getReadOnly());
}
+
+ config.addDataSourceProperty("allowMultiQueries", true);
+ config.addDataSourceProperty("prepareThreshold", 100);
+
return new HikariDataSource(config);
}