From b5a28c48749379594ba01069dfb45f9c3553dc44 Mon Sep 17 00:00:00 2001
From: Sandro Santilli <strk@keybit.net>
Date: Mon, 21 Jul 2014 16:12:40 +0000
Subject: [PATCH] Add test for selectivity estimator

git-svn-id: http://svn.osgeo.org/postgis/trunk@12813 b70326c6-7e19-0410-871a-916f4a2858ee
---
 regress/regress_index.sql      | 54 ++++++++++++++++++++++++++++++++++
 regress/regress_index_expected |  4 +++
 2 files changed, 58 insertions(+)

diff --git a/regress/regress_index.sql b/regress/regress_index.sql
index 8b51160c3..6cd666779 100644
--- a/regress/regress_index.sql
+++ b/regress/regress_index.sql
@@ -13,4 +13,58 @@ set enable_seqscan = off;
 
  select num,ST_astext(the_geom) from test where the_geom && 'BOX3D(125 125,135 135)'::box3d  order by num;
 
+CREATE FUNCTION estimate_error(qry text, tol int)
+RETURNS text
+LANGUAGE 'plpgsql' VOLATILE AS $$
+DECLARE
+  anl XML; -- analisys
+  err INT; -- absolute difference between planned and actual rows
+  est INT; -- estimated count
+  act INT; -- actual count
+BEGIN
+  EXECUTE 'EXPLAIN (ANALYZE, FORMAT XML) ' || qry INTO STRICT anl;
+
+  SELECT (xpath('//x:Plan-Rows/text()', anl,
+         ARRAY[ARRAY['x', 'http://www.postgresql.org/2009/explain']]))[1]
+         ::text::int
+  INTO est;
+
+  SELECT (xpath('//x:Actual-Rows/text()', anl,
+         ARRAY[ARRAY['x', 'http://www.postgresql.org/2009/explain']]))[1]
+         ::text::int
+  INTO act;
+
+  err = abs(est-act);
+
+  RETURN act || '+=' || tol || ':' || coalesce(
+    nullif((err < tol)::text,'false'),
+    'false:'||err::text
+    );
+
+END;
+$$;
+
+-- There are 50000 points in the table with full extent being
+-- BOX(0.0439142361 0.0197799355,999.955261 999.993652)
+
+ANALYZE test;
+
+SELECT estimate_error('
+ select num from test where the_geom && ST_MakeEnvelope(125,125,135,135);
+', 5);
+
+select estimate_error('
+ select num from test where the_geom && ST_MakeEnvelope(0,0,135,135);
+', 50);
+
+SELECT estimate_error('
+ select num from test where the_geom && ST_MakeEnvelope(0,0,500,500);
+', 500);
+
+SELECT estimate_error('
+ select num from test where the_geom && ST_MakeEnvelope(0,0,1000,1000);
+', 600);
+
 DROP TABLE test;
+
+DROP FUNCTION estimate_error(text, int);
diff --git a/regress/regress_index_expected b/regress/regress_index_expected
index 19dc436cf..035aa0f3d 100644
--- a/regress/regress_index_expected
+++ b/regress/regress_index_expected
@@ -4,3 +4,7 @@
 2594|POINT(130.504303 126.53112)
 3618|POINT(130.447205 131.655289)
 7245|POINT(128.10466 130.94133)
+3+=5:true
+924+=50:true
+12621+=500:true
+50000+=600:true
-- 
2.49.0