regress_selectivity failing on PostgreSQL 10dev

[robe]

PostgreSQL 10devel on x86_64-pc-linux-gnu, compiled by gcc (Debian 5.2.1-22) 5.2.1 20151010, 64-bit
  Postgis 2.4.0dev - r15288 - 2017-01-21 22:03:13
  scripts 2.4.0dev r15288
  GEOS: 3.6.2dev-CAPI-1.10.2 r4327
  PROJ: Rel. 4.9.2, 08 September 2015
  SFCGAL: 1.3.0

 regress_selectivity .. failed (diff expected obtained: /var/lib/jenkins/workspace/postgis/regress_pgdev/tmp/2_4_pg10.0w64/test_63_diff)
-----------------------------------------------------------------------------
--- regress_selectivity_expected	2017-01-01 03:28:18.655591934 +0000
+++ /var/lib/jenkins/workspace/postgis/regress_pgdev/tmp/2_4_pg10.0w64/test_63_out	2017-01-21 22:05:28.847702075 +0000
@@ -2,16 +2,16 @@
 ERROR:  stats for "no_stats.g" do not exist
 ERROR:  stats for "no_stats.g" do not exist
 ERROR:  stats for "no_stats_join.g" do not exist
-selectivity_00|2127
-selectivity_01|1068
+selectivity_00|715
+selectivity_01|245
 selectivity_02|actual|0.502
-selectivity_03|estimated|0.502
-selectivity_04|161
+selectivity_03|estimated|0.638
+selectivity_04|55
 selectivity_05|actual|0.076
-selectivity_06|estimated|0.076
+selectivity_06|estimated|0.119
 selectivity_07|81
 selectivity_08|actual|0.038
-selectivity_09|estimated|0.038
+selectivity_09|estimated|0.231
 selectivity_10|actual|0
 selectivity_09|estimated|0
 selectivity_10|actual|1
-----------------------------------------------------------------------------

and knn_recheck sadly is still failing

 knn_recheck .. failed (diff expected obtained: /var/lib/jenkins/workspace/postgis/regress_pgdev/tmp/2_4_pg10.0w64/test_94_diff)
-----------------------------------------------------------------------------
--- knn_recheck_expected	2017-01-07 22:06:57.264893099 +0000
+++ /var/lib/jenkins/workspace/postgis/regress_pgdev/tmp/2_4_pg10.0w64/test_94_out	2017-01-21 22:06:23.531704180 +0000
@@ -13,8 +13,8 @@
 #3|1|147|9.6598|9.6598
 #3|1|291|10.8780|10.8780
 #3|1|292|13.4929|13.4929
-#3|500003|500004|1447.7424|1447.7424
 #3|500003|500002|1447.7424|1447.7424
+#3|500003|500004|1447.7424|1447.7424
 #3|500003|500001|3423.4486|3423.4486
 #3|500003|500005|3423.4486|3423.4486
 #3|500003|22837|5123.7770|5123.7770
@@ -59,7 +59,7 @@
 #2g|30695|21264.3654|21264.3654
 #2g|30512|25313.2118|25313.2118
 #3g|1000|t
-#3g|2614|t
+#3g|2614|f
 #3g|500000|t
 #1g|500000|0.0000|0.0000
 #1g|600003|69974.6935|69974.6935
@@ -72,7 +72,7 @@
 #2g|30695|21264.3654|21264.3654
 #2g|30512|25313.2118|25313.2118
 #3g|1000|t
-#3g|2614|t
+#3g|2614|f
 #3g|500000|t
 #1nd-3|290|260.6797|260.6797
 #1nd-3|287|264.3000|264.3000
@@ -89,10 +89,10 @@
 #3nd-3|1|294|9.6598|9.6598
 #3nd-3|1|582|10.8780|10.8780
 #3nd-3|1|583|13.4929|13.4929
-#3nd-3|500003|500004|1448.3262|1448.3262
 #3nd-3|500003|500002|1448.3262|1448.3262
-#3nd-3|500003|500001|3424.9088|3424.9088
+#3nd-3|500003|500004|1448.3262|1448.3262
 #3nd-3|500003|500005|3424.9088|3424.9088
+#3nd-3|500003|500001|3424.9088|3424.9088
 #3nd-3|500003|45674|5153.7747|5153.7747
 #3nd-3|600001|600002|0.0000|0.0000
 #3nd-3|600001|9752|54.2730|54.2730
-----------------------------------------------------------------------------

Looks like selectivity started failing around January 18th.

This is the log of the PostgreSQL of the first run we see where it fails.

{{{ git revision: 69f4b9c85f168ae006929eec44fc44d569e846b9 Build #1867 (Jan 18, 2017 9:54:34 PM) add description Subproject Builds

Changes

Avoid use of DROP TABLE .. CASCADE in partitioning tests. (detail) Disable transforms that replaced AT TIME ZONE with RelabelType. (detail) Implement array version of jsonb_delete and operator (detail) Change some test macros to return true booleans (detail) Reset the proper GUC in create_index test. (detail) Improve comment in hashsearch.c. (detail) Move targetlist SRF handling from expression evaluation to new executor (detail)

}}}

[strk]

That selectivity test was written by Paul as far as I can tell. It looks like estimations grew with PostgreSQL 10 ? Like correct estimation is 50% but from PostgreSQL 10 we are estimating 63% of seletivity, if I read that correctly. What could affect this ? Maybe a change in the default stat target for tables (I see no explicit setting that stat target in the testcase)

[robe]

Changing the default statistics target didn't seem to make a difference.

[robe]

Okay I just checked

show default_statistics_target

Both my pg 10 and pg 9.6 have the same value of 100. So that can't be the culprit.

[robe]

@pramsey any thoughts on this. This is the only PostgreSQL 10 regression failure not fixed. If we can fix this then I can add PostgreSQL 10 to our regular regress run for 2.4

[robe]

pramsey,

any thoughts on this. You think it would be bad if I changed exact match to something like a difference match? I know that's shoving the problem under the covers, but not sure what else to do. I'd really like to start testing PostgreSQL 10 in our regular test runs.

[dbaston]

Fixed per @robe