From e926170dfdf4ffaa929b0b5e4721219600738320 Mon Sep 17 00:00:00 2001 From: Sandro Santilli Date: Wed, 18 Jan 2012 15:47:45 +0000 Subject: [PATCH] Rework st_estimated_extent to deal with analyzed empty tables (#818) Still doesn't distinguish between empty and not analyzed recently (might be improved in that reguard) git-svn-id: http://svn.osgeo.org/postgis/trunk@8870 b70326c6-7e19-0410-871a-916f4a2858ee --- postgis/geometry_estimate.c | 53 ++++++++++++++++++++++++++++++++----- regress/run_test | 1 + regress/tickets.sql | 4 ++- regress/tickets_expected | 8 +++--- 4 files changed, 56 insertions(+), 10 deletions(-) diff --git a/postgis/geometry_estimate.c b/postgis/geometry_estimate.c index 1f5a59e31..80040914f 100644 --- a/postgis/geometry_estimate.c +++ b/postgis/geometry_estimate.c @@ -1334,6 +1334,7 @@ Datum geometry_estimated_extent(PG_FUNCTION_ARGS) GBOX *box; size_t querysize; GEOM_STATS geomstats; + float reltuples; if ( PG_NARGS() == 3 ) { @@ -1428,11 +1429,27 @@ Datum geometry_estimated_extent(PG_FUNCTION_ARGS) /* Return the stats data */ if ( txnsp ) { - sprintf(query, "SELECT s.stanumbers1[5:8] FROM pg_statistic s, pg_class c, pg_attribute a, pg_namespace n WHERE c.relname = '%s' AND a.attrelid = c.oid AND a.attname = '%s' AND n.nspname = '%s' AND c.relnamespace = n.oid AND s.starelid=c.oid AND s.staattnum = a.attnum AND staattnum = attnum", tbl, col, nsp); + sprintf(query, + "SELECT s.stanumbers1[5:8], c.reltuples FROM pg_class c" + " LEFT OUTER JOIN pg_namespace n ON (n.oid = c.relnamespace)" + " LEFT OUTER JOIN pg_attribute a ON (a.attrelid = c.oid )" + " LEFT OUTER JOIN pg_statistic s ON (s.starelid = c.oid AND " + "s.staattnum = a.attnum )" + " WHERE c.relname = '%s' AND a.attname = '%s' " + " AND n.nspname = '%s';", + tbl, col, nsp); } else { - sprintf(query, "SELECT s.stanumbers1[5:8] FROM pg_statistic s, pg_class c, pg_attribute a, pg_namespace n WHERE c.relname = '%s' AND a.attrelid = c.oid AND a.attname = '%s' AND n.nspname = current_schema() AND c.relnamespace = n.oid AND s.starelid=c.oid AND s.staattnum = a.attnum AND staattnum = attnum", tbl, col); + sprintf(query, + "SELECT s.stanumbers1[5:8], c.reltuples FROM pg_class c" + " LEFT OUTER JOIN pg_namespace n ON (n.oid = c.relnamespace)" + " LEFT OUTER JOIN pg_attribute a ON (a.attrelid = c.oid )" + " LEFT OUTER JOIN pg_statistic s ON (s.starelid = c.oid AND " + "s.staattnum = a.attnum )" + " WHERE c.relname = '%s' AND a.attname = '%s' " + " AND n.nspname = current_schema();", + tbl, col); } POSTGIS_DEBUGF(4, " query: %s", query); @@ -1449,10 +1466,7 @@ Datum geometry_estimated_extent(PG_FUNCTION_ARGS) POSTGIS_DEBUGF(3, " %d stat rows", SPI_processed); - /* - * TODO: distinguish between empty and not analyzed ? - */ - elog(WARNING, "No stats for \"%s\".\"%s\".\"%s\" (empty or not analyzed)", + elog(ERROR, "Unexistent field \"%s\".\"%s\".\"%s\"", ( nsp ? nsp : "" ), tbl, col); SPI_finish(); @@ -1462,6 +1476,33 @@ Datum geometry_estimated_extent(PG_FUNCTION_ARGS) tuptable = SPI_tuptable; tupdesc = SPI_tuptable->tupdesc; tuple = tuptable->vals[0]; + + /* Check if the table has zero rows first */ + reltuples = DatumGetFloat4(SPI_getbinval(tuple, tupdesc, 2, &isnull)); + if (isnull) + { + + POSTGIS_DEBUG(3, " reltuples is NULL"); + + elog(ERROR, "geometry_estimated_extent: null reltuples for table"); + + SPI_finish(); + PG_RETURN_NULL(); + } + if ( ! reltuples ) + { + POSTGIS_DEBUG(3, "table has estimated zero rows"); + + /* + * TODO: distinguish between empty and not analyzed ? + */ + elog(NOTICE, "\"%s\".\"%s\".\"%s\" is empty or not analyzed", + ( nsp ? nsp : "" ), tbl, col); + + SPI_finish(); + PG_RETURN_NULL(); + } + array = DatumGetArrayTypeP(SPI_getbinval(tuple, tupdesc, 1, &isnull)); if (isnull) { diff --git a/regress/run_test b/regress/run_test index 21a13994a..93f342136 100755 --- a/regress/run_test +++ b/regress/run_test @@ -206,6 +206,7 @@ run_simple_test () | grep --binary-files=text -v "^CREATE" \ | grep --binary-files=text -v "^SELECT" \ | grep --binary-files=text -v "^SET" \ + | grep --binary-files=text -v "^TRUNCATE" \ | grep --binary-files=text -v "^LINE [0-9]" \ | grep --binary-files=text -v "^ *^$" \ | sed 's/Infinity/inf/g;s/Inf/inf/g;s/1\.#INF/inf/g' \ diff --git a/regress/tickets.sql b/regress/tickets.sql index 8cf002018..cba1487a8 100644 --- a/regress/tickets.sql +++ b/regress/tickets.sql @@ -437,7 +437,7 @@ SELECT '#1273', st_equals(p.g, postgis_addbbox(p.g)) from p; WITH p AS ( SELECT 'MULTIPOINT((832694.188 816254.625))'::geometry as g ) SELECT '#1273.1', st_equals(p.g, postgis_dropbbox(p.g)) from p; --- #877 +-- #877, #818 create table t(g geometry); select '#877.1', st_estimated_extent('t','g'); analyze t; @@ -446,6 +446,8 @@ insert into t(g) values ('LINESTRING(-10 -50, 20 30)'); select '#877.3', st_estimated_extent('t','g'); analyze t; select '#877.4', st_estimated_extent('t','g'); +truncate t; +select '#818.1', st_estimated_extent('t','g'); drop table t; -- #1320 diff --git a/regress/tickets_expected b/regress/tickets_expected index 16787e139..507367b40 100644 --- a/regress/tickets_expected +++ b/regress/tickets_expected @@ -136,13 +136,15 @@ ERROR: First argument must be a LINESTRING #1060|FFFFFFFF2 #1273|t #1273.1|t -WARNING: No stats for ""."t"."g" (empty or not analyzed) +NOTICE: ""."t"."g" is empty or not analyzed #877.1| -WARNING: No stats for "public"."t"."g" (empty or not analyzed) +NOTICE: "public"."t"."g" is empty or not analyzed #877.2| -WARNING: No stats for ""."t"."g" (empty or not analyzed) +NOTICE: ""."t"."g" is empty or not analyzed #877.3| #877.4|BOX(-10 -50,20 30) +NOTICE: ""."t"."g" is empty or not analyzed +#818.1| <#1320> #1320.geog.1|MULTIPOLYGON|4326 #1320.geom.1|MULTIPOLYGON|4326 -- 2.40.0