This is an automated email from the ASF dual-hosted git repository.
desruisseaux pushed a commit to branch geoapi-4.0
in repository https://gitbox.apache.org/repos/asf/sis.git
commit 8820c6fa484369affaa029cf0c56adcdabc4ed58
Author: Martin Desruisseaux <martin.desruisse...@geomatys.com>
AuthorDate: Mon Dec 27 16:16:04 2021 +0100
Execute PostgreSQL `ANALYZE` at most once per table.
Add documentation about the rational for not using `ST_Extent`.
---
.../apache/sis/internal/sql/feature/Database.java | 3 ++-
.../org/apache/sis/internal/sql/feature/Table.java | 20 +++++++++++++++++++-
.../sis/internal/sql/postgis/ExtentEstimator.java | 16 ++++++++++++----
.../apache/sis/internal/sql/postgis/Postgres.java | 7 +++++--
4 files changed, 38 insertions(+), 8 deletions(-)
diff --git
a/storage/sis-sqlstore/src/main/java/org/apache/sis/internal/sql/feature/Database.java
b/storage/sis-sqlstore/src/main/java/org/apache/sis/internal/sql/feature/Database.java
index b898efe..79e9a9e 100644
---
a/storage/sis-sqlstore/src/main/java/org/apache/sis/internal/sql/feature/Database.java
+++
b/storage/sis-sqlstore/src/main/java/org/apache/sis/internal/sql/feature/Database.java
@@ -604,10 +604,11 @@ public class Database<G> extends Syntax {
* @param table the table for which to compute an estimation of the
envelope.
* @param columns all columns in the table. Implementation should ignore
non-geometry columns.
* This is a reference to an internal array; <strong>do
not modify</strong>.
+ * @param recall if it is at least the second time that this method is
invoked for the specified table.
* @return an estimation of the spatiotemporal resource extent, or {@code
null} if none.
* @throws SQLException if an error occurred while fetching the envelope.
*/
- protected Envelope getEstimatedExtent(TableReference table, Column[]
columns) throws SQLException {
+ protected Envelope getEstimatedExtent(TableReference table, Column[]
columns, boolean recall) throws SQLException {
return null;
}
diff --git
a/storage/sis-sqlstore/src/main/java/org/apache/sis/internal/sql/feature/Table.java
b/storage/sis-sqlstore/src/main/java/org/apache/sis/internal/sql/feature/Table.java
index 9c18aed..295cb01 100644
---
a/storage/sis-sqlstore/src/main/java/org/apache/sis/internal/sql/feature/Table.java
+++
b/storage/sis-sqlstore/src/main/java/org/apache/sis/internal/sql/feature/Table.java
@@ -157,6 +157,14 @@ final class Table extends AbstractFeatureSet {
private WeakValueHashMap<?,Object> instanceForPrimaryKeys;
/**
+ * {@code true} if {@link #getEnvelope()} has been invoked at least once
on this table.
+ * This is used for performing only once operations such as PosthreSQL
{@code ANALYZE}.
+ *
+ * @see #getEnvelope()
+ */
+ private boolean isEnvelopeAnalyzed;
+
+ /**
* Creates a description of the table analyzed by the given object.
*
* @param database information about the database (syntax for building
SQL statements, …).
@@ -323,13 +331,23 @@ final class Table extends AbstractFeatureSet {
* discouraged. Despite that, this method may return smaller envelopes
because the computation is done using
* a subset of all data.
*
+ * <h2>Limitations</h2>
+ * The exact behavior is database-dependent.
+ * For example PostGIS implementation assumes that all geometries in the
same column are in the same CRS.
+ * If geometries in different <em>rows</em> use different CRS, coordinate
transformations are <em>not</em>
+ * applied and the result is likely to be invalid. However if different
<em>column</em> use different CRS,
+ * coordinate transformations between columns is applied and the result is
in the CRS of the first column
+ * having at least one geometry.
+ *
* @return an estimation of the spatiotemporal resource extent.
* @throws DataStoreException if an error occurred while reading or
computing the envelope.
*/
@Override
public Optional<Envelope> getEnvelope() throws DataStoreException {
if (hasGeometry) try {
- return Optional.ofNullable(database.getEstimatedExtent(name,
attributes));
+ final boolean recall = isEnvelopeAnalyzed;
+ isEnvelopeAnalyzed = true;
+ return Optional.ofNullable(database.getEstimatedExtent(name,
attributes, recall));
} catch (SQLException e) {
throw new DataStoreException(e);
} else {
diff --git
a/storage/sis-sqlstore/src/main/java/org/apache/sis/internal/sql/postgis/ExtentEstimator.java
b/storage/sis-sqlstore/src/main/java/org/apache/sis/internal/sql/postgis/ExtentEstimator.java
index 2fa86da..0cbbbd2 100644
---
a/storage/sis-sqlstore/src/main/java/org/apache/sis/internal/sql/postgis/ExtentEstimator.java
+++
b/storage/sis-sqlstore/src/main/java/org/apache/sis/internal/sql/postgis/ExtentEstimator.java
@@ -31,6 +31,13 @@ import org.apache.sis.internal.metadata.sql.SQLBuilder;
/**
* Estimation of the extent of geometries in a given table or column using
statistics if available.
* Uses the PostGIS {@code ST_EstimatedExtent(…)} function to get a rough
estimation of column extent.
+ * If {@code ST_EstimatedExtent(…)} gave no result and it was the first
attempt on the specified table,
+ * then this class executes {@code ANALYZE} and tries again to get the extent.
This strategy works well
+ * when requesting envelope on newly created tables.
+ *
+ * <h2>Design notes</h2>
+ * We do not use the most accurate {@code ST_Extent} function because it is
costly on large tables.
+ * At the time of writing this class (December 2021), {@code ST_Extent} does
not use column index.
*
* @author Alexis Manin (Geomatys)
* @author Martin Desruisseaux (Geomatys)
@@ -88,12 +95,13 @@ final class ExtentEstimator {
* If there is no statistics available, then this method executes {@code
ANALYZE}
* and tries again.
*
- * @param statement statement to use for executing queries. Shall be
closed by caller.
+ * @param statement statement to use for executing queries. Shall be
closed by caller.
+ * @param recall if it is at least the second time that this method
is invoked for the table.
* @return an estimation of the union of extents in given columns, or
{@code null} if unknown.
*/
- GeneralEnvelope estimate(final Statement statement) throws SQLException {
+ GeneralEnvelope estimate(final Statement statement, final boolean recall)
throws SQLException {
query(statement);
- if (envelope == null) {
+ if (envelope == null && !recall) {
builder.append("ANALYZE ").appendIdentifier(table.catalog,
table.schema, table.table);
final String sql = builder.toString();
builder.clear();
@@ -110,7 +118,7 @@ final class ExtentEstimator {
* Estimates the extent in the specified columns using current statistics.
* If there is no statistics available, then this method returns {@code
null}.
*
- * @param statement statement to use for executing queries. Shall be
closed by caller.
+ * @param statement statement to use for executing queries. Shall be
closed by caller.
* @return an estimation of the union of extents in given columns, or
{@code null} if unknown.
*/
private void query(final Statement statement) throws SQLException {
diff --git
a/storage/sis-sqlstore/src/main/java/org/apache/sis/internal/sql/postgis/Postgres.java
b/storage/sis-sqlstore/src/main/java/org/apache/sis/internal/sql/postgis/Postgres.java
index 06925c8..66a60b1 100644
---
a/storage/sis-sqlstore/src/main/java/org/apache/sis/internal/sql/postgis/Postgres.java
+++
b/storage/sis-sqlstore/src/main/java/org/apache/sis/internal/sql/postgis/Postgres.java
@@ -177,16 +177,19 @@ public final class Postgres<G> extends Database<G> {
* @param table the table for which to compute an estimation of the
envelope.
* @param columns all columns in the table (including non-geometry
columns).
* This is a reference to an internal array; <strong>do
not modify</strong>.
+ * @param recall if it is at least the second time that this method is
invoked for the specified table.
* @return an estimation of the spatiotemporal resource extent, or {@code
null} if none.
* @throws SQLException if an error occurred while fetching the envelope.
*
* @see <a
href="https://postgis.net/docs/ST_EstimatedExtent.html";>ST_EstimatedExtent</a>
*/
@Override
- protected Envelope getEstimatedExtent(final TableReference table, final
Column[] columns) throws SQLException {
+ protected Envelope getEstimatedExtent(final TableReference table, final
Column[] columns, final boolean recall)
+ throws SQLException
+ {
final ExtentEstimator ex = new ExtentEstimator(this, table, columns);
try (Connection c = source.getConnection(); Statement statement =
c.createStatement()) {
- return ex.estimate(statement);
+ return ex.estimate(statement, recall);
}
}
}
