Content-Type: text/plain; charset=us-ascii
Mime-Version: 1.0 (Mac OS X Mail 9.3 \(3124\))
Subject: Re: [HACKERS] PATCH: multivariate histograms and MCV lists
From: Mark Dilger <hornschnorter@gmail.com>
In-Reply-To: <cf43d6de-617f-83c0-8c14-740c3fa23795@2ndquadrant.com>
Date: Sat, 25 Nov 2017 08:15:05 -0800
Cc: Adrien Nayrat <adrien.nayrat@dalibo.com>,
 Pg Hackers <pgsql-hackers@postgresql.org>
Content-Transfer-Encoding: quoted-printable
Message-Id: <0895FFB4-DEB7-459B-ABD2-E7855D0291A8@gmail.com>
References: <dfdac334-9cf2-2597-fb27-f0fb3753f435@2ndquadrant.com>
 <31ee8686-6d62-7f10-4129-7580ec74a710@dalibo.com>
 <b3688203-7c5d-2d7e-58b6-a6bb04f29b5c@2ndquadrant.com>
 <340ffd44-2d67-d39d-e54f-5a97a842cd3a@2ndquadrant.com>
 <b3e23e18-afbb-2a39-8e15-fe0a6c39a128@2ndquadrant.com>
 <67EAB5B3-F9FB-4753-8102-4D77DCFFFCD4@gmail.com>
 <cf43d6de-617f-83c0-8c14-740c3fa23795@2ndquadrant.com>
To: Tomas Vondra <tomas.vondra@2ndquadrant.com>
Precedence: bulk


> On Nov 18, 2017, at 12:28 PM, Tomas Vondra =
<tomas.vondra@2ndquadrant.com> wrote:
>=20
> Hi,
>=20
> Attached is an updated version of the patch, adopting the psql =
describe
> changes introduced by 471d55859c11b.
>=20
> regards
>=20
> --=20
> Tomas Vondra                  http://www.2ndQuadrant.com
> PostgreSQL Development, 24x7 Support, Remote DBA, Training & Services
> =
<0001-multivariate-MCV-lists.patch.gz><0002-multivariate-histograms.patch.=
gz>

Thanks, Tomas, again for your work on this feature.

Applying just the 0001-multivariate-MCV-lists.patch to the current =
master, and
then extending the stats_ext.sql test as follows, I am able to trigger =
an error,
"ERROR:  operator 4294934272 is not a valid ordering operator".

=20

diff --git a/src/test/regress/sql/stats_ext.sql =
b/src/test/regress/sql/stats_ext.sql
index e9902ced5c..5083dc05e6 100644
--- a/src/test/regress/sql/stats_ext.sql
+++ b/src/test/regress/sql/stats_ext.sql
@@ -402,4 +402,22 @@ EXPLAIN (COSTS OFF)
 EXPLAIN (COSTS OFF)
  SELECT * FROM mcv_lists WHERE a IS NULL AND b IS NULL AND c IS NULL;
=20
-RESET random_page_cost;
+DROP TABLE mcv_lists;=20
+=20
+CREATE TABLE mcv_lists (
+    a NUMERIC[],
+   b NUMERIC[]
+);
+CREATE STATISTICS mcv_lists_stats (mcv) ON a, b FROM mcv_lists;
+INSERT INTO mcv_lists (a, b)
+   (SELECT array_agg(gs::numeric) AS a, array_agg(gs::numeric) AS b
+       FROM generate_series(1,1000) gs
+   );
+ANALYZE mcv_lists;
+INSERT INTO mcv_lists (a, b)
+   (SELECT array_agg(gs::numeric) AS a, array_agg(gs::numeric) AS b
+       FROM generate_series(1,1000) gs
+   );
+ANALYZE mcv_lists;
+
+DROP TABLE mcv_lists;

Which gives me the following regression.diffs:

*** =
/Users/mark/master/postgresql/src/test/regress/expected/stats_ext.out   =
2017-11-25 08:06:37.000000000 -0800
--- /Users/mark/master/postgresql/src/test/regress/results/stats_ext.out =
   2017-11-25 08:10:18.000000000 -0800
***************
*** 721,724 ****
           Index Cond: ((a IS NULL) AND (b IS NULL))
  (5 rows)
 =20
! RESET random_page_cost;
--- 721,741 ----
           Index Cond: ((a IS NULL) AND (b IS NULL))
  (5 rows)
 =20
! DROP TABLE mcv_lists;
! CREATE TABLE mcv_lists (
!     a NUMERIC[],
!   b NUMERIC[]
! );
! CREATE STATISTICS mcv_lists_stats (mcv) ON a, b FROM mcv_lists;
! INSERT INTO mcv_lists (a, b)
!   (SELECT array_agg(gs::numeric) AS a, array_agg(gs::numeric) AS b
!       FROM generate_series(1,1000) gs
!   );
! ANALYZE mcv_lists;
! INSERT INTO mcv_lists (a, b)
!   (SELECT array_agg(gs::numeric) AS a, array_agg(gs::numeric) AS b
!       FROM generate_series(1,1000) gs
!   );
! ANALYZE mcv_lists;
! ERROR:  operator 4294934272 is not a valid ordering operator
! DROP TABLE mcv_lists;

=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=
=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=
=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D