public inbox for [email protected]
help / color / mirror / Atom feedFrom: Tomas Vondra <[email protected]>
To: Thomas Munro <[email protected]>
Cc: PostgreSQL Hackers <[email protected]>
Cc: Nikita Glukhov <[email protected]>
Cc: Mahendra Thalor <[email protected]>
Cc: Oleg Bartunov <[email protected]>
Subject: Re: Collecting statistics about contents of JSONB columns
Date: Thu, 6 Jan 2022 20:26:43 +0100
Message-ID: <[email protected]> (raw)
In-Reply-To: <CA+hUKGJ4GiO_p7En2G-CkoyW=vfNx8Ka3NkbG0RD8ebn61fgCw@mail.gmail.com>
References: <[email protected]>
<CA+hUKGJ4GiO_p7En2G-CkoyW=vfNx8Ka3NkbG0RD8ebn61fgCw@mail.gmail.com>
On 1/5/22 21:13, Thomas Munro wrote:
> On Sat, Jan 1, 2022 at 11:07 AM Tomas Vondra
> <[email protected]> wrote:
>> 0006-Add-jsonb-statistics-20211230.patch
>
> Hi Tomas,
>
> -CREATE OR REPLACE FUNCTION explain_jsonb(sql_query text)
> +CREATE OR REPLACE FUNCTION explain_jsonb(sql_query text)
>
> https://cirrus-ci.com/task/6405547984420864
>
> It looks like there is a Unicode BOM sequence in there, which is
> upsetting our Windows testing but not the 3 Unixes (not sure why).
> Probably added by an editor.
>
Thanks, fixed along with some whitespace issues.
regards
--
Tomas Vondra
EnterpriseDB: http://www.enterprisedb.com
The Enterprise PostgreSQL Company
Attachments:
[text/x-patch] 0001-Add-pg_operator.oprstat-for-derived-operato-20220106.patch (12.3K, ../[email protected]/2-0001-Add-pg_operator.oprstat-for-derived-operato-20220106.patch)
download | inline diff:
From e7fa057401728f6a894866d1e422ff9ebe42c256 Mon Sep 17 00:00:00 2001
From: Nikita Glukhov <[email protected]>
Date: Sat, 12 Nov 2016 18:59:43 +0300
Subject: [PATCH 1/6] Add pg_operator.oprstat for derived operator statistics
estimation
---
src/backend/catalog/pg_operator.c | 11 +++++
src/backend/commands/operatorcmds.c | 67 ++++++++++++++++++++++++++
src/backend/utils/adt/selfuncs.c | 39 +++++++++++++++
src/backend/utils/cache/lsyscache.c | 24 +++++++++
src/include/catalog/pg_operator.h | 4 ++
src/include/utils/lsyscache.h | 1 +
src/test/regress/expected/oidjoins.out | 1 +
7 files changed, 147 insertions(+)
diff --git a/src/backend/catalog/pg_operator.c b/src/backend/catalog/pg_operator.c
index 4c5a56cb094..1df5ac8ee8b 100644
--- a/src/backend/catalog/pg_operator.c
+++ b/src/backend/catalog/pg_operator.c
@@ -256,6 +256,7 @@ OperatorShellMake(const char *operatorName,
values[Anum_pg_operator_oprcode - 1] = ObjectIdGetDatum(InvalidOid);
values[Anum_pg_operator_oprrest - 1] = ObjectIdGetDatum(InvalidOid);
values[Anum_pg_operator_oprjoin - 1] = ObjectIdGetDatum(InvalidOid);
+ values[Anum_pg_operator_oprstat - 1] = ObjectIdGetDatum(InvalidOid);
/*
* create a new operator tuple
@@ -301,6 +302,7 @@ OperatorShellMake(const char *operatorName,
* negatorName X negator operator
* restrictionId X restriction selectivity procedure ID
* joinId X join selectivity procedure ID
+ * statsId X statistics derivation procedure ID
* canMerge merge join can be used with this operator
* canHash hash join can be used with this operator
*
@@ -333,6 +335,7 @@ OperatorCreate(const char *operatorName,
List *negatorName,
Oid restrictionId,
Oid joinId,
+ Oid statsId,
bool canMerge,
bool canHash)
{
@@ -505,6 +508,7 @@ OperatorCreate(const char *operatorName,
values[Anum_pg_operator_oprcode - 1] = ObjectIdGetDatum(procedureId);
values[Anum_pg_operator_oprrest - 1] = ObjectIdGetDatum(restrictionId);
values[Anum_pg_operator_oprjoin - 1] = ObjectIdGetDatum(joinId);
+ values[Anum_pg_operator_oprstat - 1] = ObjectIdGetDatum(statsId);
pg_operator_desc = table_open(OperatorRelationId, RowExclusiveLock);
@@ -855,6 +859,13 @@ makeOperatorDependencies(HeapTuple tuple,
add_exact_object_address(&referenced, addrs);
}
+ /* Dependency on statistics derivation function */
+ if (OidIsValid(oper->oprstat))
+ {
+ ObjectAddressSet(referenced, ProcedureRelationId, oper->oprstat);
+ add_exact_object_address(&referenced, addrs);
+ }
+
record_object_address_dependencies(&myself, addrs, DEPENDENCY_NORMAL);
free_object_addresses(addrs);
diff --git a/src/backend/commands/operatorcmds.c b/src/backend/commands/operatorcmds.c
index eb50f60ed13..813e369aef6 100644
--- a/src/backend/commands/operatorcmds.c
+++ b/src/backend/commands/operatorcmds.c
@@ -53,6 +53,12 @@
static Oid ValidateRestrictionEstimator(List *restrictionName);
static Oid ValidateJoinEstimator(List *joinName);
+/*
+ * XXX Maybe not the right name, because "estimator" implies we're calculating
+ * selectivity. But we're actually deriving statistics for an expression.
+ */
+static Oid ValidateStatisticsEstimator(List *joinName);
+
/*
* DefineOperator
* this function extracts all the information from the
@@ -78,10 +84,12 @@ DefineOperator(List *names, List *parameters)
List *commutatorName = NIL; /* optional commutator operator name */
List *negatorName = NIL; /* optional negator operator name */
List *restrictionName = NIL; /* optional restrict. sel. function */
+ List *statisticsName = NIL; /* optional stats estimat. procedure */
List *joinName = NIL; /* optional join sel. function */
Oid functionOid; /* functions converted to OID */
Oid restrictionOid;
Oid joinOid;
+ Oid statisticsOid;
Oid typeId[2]; /* to hold left and right arg */
int nargs;
ListCell *pl;
@@ -131,6 +139,9 @@ DefineOperator(List *names, List *parameters)
restrictionName = defGetQualifiedName(defel);
else if (strcmp(defel->defname, "join") == 0)
joinName = defGetQualifiedName(defel);
+ /* XXX perhaps full "statistics" wording would be better */
+ else if (strcmp(defel->defname, "stats") == 0)
+ statisticsName = defGetQualifiedName(defel);
else if (strcmp(defel->defname, "hashes") == 0)
canHash = defGetBoolean(defel);
else if (strcmp(defel->defname, "merges") == 0)
@@ -246,6 +257,10 @@ DefineOperator(List *names, List *parameters)
joinOid = ValidateJoinEstimator(joinName);
else
joinOid = InvalidOid;
+ if (statisticsName)
+ statisticsOid = ValidateStatisticsEstimator(statisticsName);
+ else
+ statisticsOid = InvalidOid;
/*
* now have OperatorCreate do all the work..
@@ -260,6 +275,7 @@ DefineOperator(List *names, List *parameters)
negatorName, /* optional negator operator name */
restrictionOid, /* optional restrict. sel. function */
joinOid, /* optional join sel. function name */
+ statisticsOid, /* optional stats estimation procedure */
canMerge, /* operator merges */
canHash); /* operator hashes */
}
@@ -357,6 +373,40 @@ ValidateJoinEstimator(List *joinName)
return joinOid;
}
+/*
+ * Look up a statistics estimator function by name, and verify that it has the
+ * correct signature and we have the permissions to attach it to an operator.
+ */
+static Oid
+ValidateStatisticsEstimator(List *statName)
+{
+ Oid typeId[4];
+ Oid statOid;
+ AclResult aclresult;
+
+ typeId[0] = INTERNALOID; /* PlannerInfo */
+ typeId[1] = INTERNALOID; /* OpExpr */
+ typeId[2] = INT4OID; /* varRelid */
+ typeId[3] = INTERNALOID; /* VariableStatData */
+
+ statOid = LookupFuncName(statName, 4, typeId, false);
+
+ /* statistics estimators must return void */
+ if (get_func_rettype(statOid) != BOOLOID)
+ ereport(ERROR,
+ (errcode(ERRCODE_INVALID_OBJECT_DEFINITION),
+ errmsg("statistics estimator function %s must return type %s",
+ NameListToString(statName), "boolean")));
+
+ /* Require EXECUTE rights for the estimator */
+ aclresult = pg_proc_aclcheck(statOid, GetUserId(), ACL_EXECUTE);
+ if (aclresult != ACLCHECK_OK)
+ aclcheck_error(aclresult, OBJECT_FUNCTION,
+ NameListToString(statName));
+
+ return statOid;
+}
+
/*
* Guts of operator deletion.
*/
@@ -424,6 +474,9 @@ AlterOperator(AlterOperatorStmt *stmt)
List *joinName = NIL; /* optional join sel. function */
bool updateJoin = false;
Oid joinOid;
+ List *statName = NIL; /* optional statistics estimation procedure */
+ bool updateStat = false;
+ Oid statOid;
/* Look up the operator */
oprId = LookupOperWithArgs(stmt->opername, false);
@@ -454,6 +507,11 @@ AlterOperator(AlterOperatorStmt *stmt)
joinName = param;
updateJoin = true;
}
+ else if (pg_strcasecmp(defel->defname, "stats") == 0)
+ {
+ statName = param;
+ updateStat = true;
+ }
/*
* The rest of the options that CREATE accepts cannot be changed.
@@ -496,6 +554,10 @@ AlterOperator(AlterOperatorStmt *stmt)
joinOid = ValidateJoinEstimator(joinName);
else
joinOid = InvalidOid;
+ if (statName)
+ statOid = ValidateStatisticsEstimator(statName);
+ else
+ statOid = InvalidOid;
/* Perform additional checks, like OperatorCreate does */
if (!(OidIsValid(oprForm->oprleft) && OidIsValid(oprForm->oprright)))
@@ -536,6 +598,11 @@ AlterOperator(AlterOperatorStmt *stmt)
replaces[Anum_pg_operator_oprjoin - 1] = true;
values[Anum_pg_operator_oprjoin - 1] = joinOid;
}
+ if (updateStat)
+ {
+ replaces[Anum_pg_operator_oprstat - 1] = true;
+ values[Anum_pg_operator_oprstat - 1] = statOid;
+ }
tup = heap_modify_tuple(tup, RelationGetDescr(catalog),
values, nulls, replaces);
diff --git a/src/backend/utils/adt/selfuncs.c b/src/backend/utils/adt/selfuncs.c
index 10895fb2876..3015e949db7 100644
--- a/src/backend/utils/adt/selfuncs.c
+++ b/src/backend/utils/adt/selfuncs.c
@@ -4915,6 +4915,32 @@ ReleaseDummy(HeapTuple tuple)
pfree(tuple);
}
+/*
+ * examine_operator_expression
+ * Try to derive optimizer statistics for the operator expression using
+ * operator's oprstat function.
+ *
+ * XXX Not sure why this returns bool - we ignore the return value anyway. We
+ * might as well return the calculated vardata (or NULL).
+ *
+ * XXX Not sure what to do about recursion - there can be another OpExpr in
+ * one of the arguments, and we should call this recursively from the oprstat
+ * procedure. But that's not possible, because it's marked as static.
+ */
+static bool
+examine_operator_expression(PlannerInfo *root, OpExpr *opexpr, int varRelid,
+ VariableStatData *vardata)
+{
+ RegProcedure oprstat = get_oprstat(opexpr->opno);
+
+ return OidIsValid(oprstat) &&
+ DatumGetBool(OidFunctionCall4(oprstat,
+ PointerGetDatum(root),
+ PointerGetDatum(opexpr),
+ Int32GetDatum(varRelid),
+ PointerGetDatum(vardata)));
+}
+
/*
* examine_variable
* Try to look up statistical data about an expression.
@@ -5335,6 +5361,19 @@ examine_variable(PlannerInfo *root, Node *node, int varRelid,
pos++;
}
}
+
+ /*
+ * If there's no index or extended statistics matching the expression,
+ * try deriving the statistics from statistics on arguments of the
+ * operator expression (OpExpr). We do this last because it may be quite
+ * expensive, and it's unclear how accurate the statistics will be.
+ *
+ * XXX Shouldn't this put more restrictions on the OpExpr? E.g. that
+ * one of the arguments has to be a Const or something?
+ */
+ if (!vardata->statsTuple && IsA(basenode, OpExpr))
+ examine_operator_expression(root, (OpExpr *) basenode, varRelid,
+ vardata);
}
}
diff --git a/src/backend/utils/cache/lsyscache.c b/src/backend/utils/cache/lsyscache.c
index 9176514a962..77a8b01347f 100644
--- a/src/backend/utils/cache/lsyscache.c
+++ b/src/backend/utils/cache/lsyscache.c
@@ -1567,6 +1567,30 @@ get_oprjoin(Oid opno)
return (RegProcedure) InvalidOid;
}
+/*
+ * get_oprstat
+ *
+ * Returns procedure id for estimating statistics for an operator.
+ */
+RegProcedure
+get_oprstat(Oid opno)
+{
+ HeapTuple tp;
+
+ tp = SearchSysCache1(OPEROID, ObjectIdGetDatum(opno));
+ if (HeapTupleIsValid(tp))
+ {
+ Form_pg_operator optup = (Form_pg_operator) GETSTRUCT(tp);
+ RegProcedure result;
+
+ result = optup->oprstat;
+ ReleaseSysCache(tp);
+ return result;
+ }
+ else
+ return (RegProcedure) InvalidOid;
+}
+
/* ---------- FUNCTION CACHE ---------- */
/*
diff --git a/src/include/catalog/pg_operator.h b/src/include/catalog/pg_operator.h
index 6ab61517b1e..70d5f70964f 100644
--- a/src/include/catalog/pg_operator.h
+++ b/src/include/catalog/pg_operator.h
@@ -73,6 +73,9 @@ CATALOG(pg_operator,2617,OperatorRelationId)
/* OID of join estimator, or 0 */
regproc oprjoin BKI_DEFAULT(-) BKI_LOOKUP_OPT(pg_proc);
+
+ /* OID of statistics estimator, or 0 */
+ regproc oprstat BKI_DEFAULT(-) BKI_LOOKUP_OPT(pg_proc);
} FormData_pg_operator;
/* ----------------
@@ -95,6 +98,7 @@ extern ObjectAddress OperatorCreate(const char *operatorName,
List *negatorName,
Oid restrictionId,
Oid joinId,
+ Oid statisticsId,
bool canMerge,
bool canHash);
diff --git a/src/include/utils/lsyscache.h b/src/include/utils/lsyscache.h
index 77871aaefc3..12fc1cadc62 100644
--- a/src/include/utils/lsyscache.h
+++ b/src/include/utils/lsyscache.h
@@ -118,6 +118,7 @@ extern Oid get_commutator(Oid opno);
extern Oid get_negator(Oid opno);
extern RegProcedure get_oprrest(Oid opno);
extern RegProcedure get_oprjoin(Oid opno);
+extern RegProcedure get_oprstat(Oid opno);
extern char *get_func_name(Oid funcid);
extern Oid get_func_namespace(Oid funcid);
extern Oid get_func_rettype(Oid funcid);
diff --git a/src/test/regress/expected/oidjoins.out b/src/test/regress/expected/oidjoins.out
index 215eb899be3..111ea99cdae 100644
--- a/src/test/regress/expected/oidjoins.out
+++ b/src/test/regress/expected/oidjoins.out
@@ -113,6 +113,7 @@ NOTICE: checking pg_operator {oprnegate} => pg_operator {oid}
NOTICE: checking pg_operator {oprcode} => pg_proc {oid}
NOTICE: checking pg_operator {oprrest} => pg_proc {oid}
NOTICE: checking pg_operator {oprjoin} => pg_proc {oid}
+NOTICE: checking pg_operator {oprstat} => pg_proc {oid}
NOTICE: checking pg_opfamily {opfmethod} => pg_am {oid}
NOTICE: checking pg_opfamily {opfnamespace} => pg_namespace {oid}
NOTICE: checking pg_opfamily {opfowner} => pg_authid {oid}
--
2.31.1
[text/x-patch] 0002-Add-stats_form_tuple-20220106.patch (3.9K, ../[email protected]/3-0002-Add-stats_form_tuple-20220106.patch)
download | inline diff:
From e2768155f02b9c83af78c6b0128a7e86e8936f73 Mon Sep 17 00:00:00 2001
From: Nikita Glukhov <[email protected]>
Date: Wed, 7 Dec 2016 16:12:55 +0300
Subject: [PATCH 2/6] Add stats_form_tuple()
---
src/backend/utils/adt/selfuncs.c | 55 ++++++++++++++++++++++++++++++++
src/include/utils/selfuncs.h | 22 +++++++++++++
2 files changed, 77 insertions(+)
diff --git a/src/backend/utils/adt/selfuncs.c b/src/backend/utils/adt/selfuncs.c
index 3015e949db7..3e8fc47f662 100644
--- a/src/backend/utils/adt/selfuncs.c
+++ b/src/backend/utils/adt/selfuncs.c
@@ -7958,3 +7958,58 @@ brincostestimate(PlannerInfo *root, IndexPath *path, double loop_count,
*indexPages = index->pages;
}
+
+/*
+ * stats_form_tuple - Form pg_statistic tuple from StatsData.
+ *
+ * If 'data' parameter is NULL, form all-NULL tuple (nullfrac = 1.0).
+ */
+HeapTuple
+stats_form_tuple(StatsData *data)
+{
+ Relation rel;
+ HeapTuple tuple;
+ Datum values[Natts_pg_statistic];
+ bool nulls[Natts_pg_statistic];
+ int i;
+
+ for (i = 0; i < Natts_pg_statistic; ++i)
+ nulls[i] = false;
+
+ values[Anum_pg_statistic_starelid - 1] = ObjectIdGetDatum(InvalidOid);
+ values[Anum_pg_statistic_staattnum - 1] = Int16GetDatum(0);
+ values[Anum_pg_statistic_stainherit - 1] = BoolGetDatum(false);
+ values[Anum_pg_statistic_stanullfrac - 1] =
+ Float4GetDatum(data ? data->nullfrac : 1.0);
+ values[Anum_pg_statistic_stawidth - 1] =
+ Int32GetDatum(data ? data->width : 0);
+ values[Anum_pg_statistic_stadistinct - 1] =
+ Float4GetDatum(data ? data->distinct : 0);
+
+ for (i = 0; i < STATISTIC_NUM_SLOTS; i++)
+ {
+ StatsSlot *slot = data ? &data->slots[i] : NULL;
+
+ values[Anum_pg_statistic_stakind1 + i - 1] =
+ Int16GetDatum(slot ? slot->kind : 0);
+
+ values[Anum_pg_statistic_staop1 + i - 1] =
+ ObjectIdGetDatum(slot ? slot->opid : InvalidOid);
+
+ if (slot && DatumGetPointer(slot->numbers))
+ values[Anum_pg_statistic_stanumbers1 + i - 1] = slot->numbers;
+ else
+ nulls[Anum_pg_statistic_stanumbers1 + i - 1] = true;
+
+ if (slot && DatumGetPointer(slot->values))
+ values[Anum_pg_statistic_stavalues1 + i - 1] = slot->values;
+ else
+ nulls[Anum_pg_statistic_stavalues1 + i - 1] = true;
+ }
+
+ rel = table_open(StatisticRelationId, AccessShareLock);
+ tuple = heap_form_tuple(RelationGetDescr(rel), values, nulls);
+ table_close(rel, NoLock);
+
+ return tuple;
+}
diff --git a/src/include/utils/selfuncs.h b/src/include/utils/selfuncs.h
index 9dd444e1ff5..b90e05b8707 100644
--- a/src/include/utils/selfuncs.h
+++ b/src/include/utils/selfuncs.h
@@ -16,6 +16,7 @@
#define SELFUNCS_H
#include "access/htup.h"
+#include "catalog/pg_statistic.h"
#include "fmgr.h"
#include "nodes/pathnodes.h"
@@ -133,6 +134,24 @@ typedef struct
double num_sa_scans; /* # indexscans from ScalarArrayOpExprs */
} GenericCosts;
+/* Single pg_statistic slot */
+typedef struct StatsSlot
+{
+ int16 kind; /* stakindN: statistic kind (STATISTIC_KIND_XXX) */
+ Oid opid; /* staopN: associated operator, if needed */
+ Datum numbers; /* stanumbersN: float4 array of numbers */
+ Datum values; /* stavaluesN: anyarray of values */
+} StatsSlot;
+
+/* Deformed pg_statistic tuple */
+typedef struct StatsData
+{
+ float4 nullfrac; /* stanullfrac: fraction of NULL values */
+ float4 distinct; /* stadistinct: number of distinct non-NULL values */
+ int32 width; /* stawidth: average width in bytes of non-NULL values */
+ StatsSlot slots[STATISTIC_NUM_SLOTS]; /* slots for different statistic types */
+} StatsData;
+
/* Hooks for plugins to get control when we ask for stats */
typedef bool (*get_relation_stats_hook_type) (PlannerInfo *root,
RangeTblEntry *rte,
@@ -231,6 +250,9 @@ extern void genericcostestimate(PlannerInfo *root, IndexPath *path,
double loop_count,
GenericCosts *costs);
+extern HeapTuple stats_form_tuple(StatsData *data);
+
+
/* Functions in array_selfuncs.c */
extern Selectivity scalararraysel_containment(PlannerInfo *root,
--
2.31.1
[text/x-patch] 0003-Add-symbolic-names-for-some-jsonb-operators-20220106.patch (7.1K, ../[email protected]/4-0003-Add-symbolic-names-for-some-jsonb-operators-20220106.patch)
download | inline diff:
From 47e45ce7aac8d47c0aa4a563ebe4663d0fc56441 Mon Sep 17 00:00:00 2001
From: Nikita Glukhov <[email protected]>
Date: Sat, 12 Nov 2016 18:59:43 +0300
Subject: [PATCH 3/6] Add symbolic names for some jsonb operators
---
src/include/catalog/pg_operator.dat | 45 +++++++++++++++++------------
1 file changed, 26 insertions(+), 19 deletions(-)
diff --git a/src/include/catalog/pg_operator.dat b/src/include/catalog/pg_operator.dat
index 2bc7cc35484..c0ff8da722c 100644
--- a/src/include/catalog/pg_operator.dat
+++ b/src/include/catalog/pg_operator.dat
@@ -2067,7 +2067,7 @@
{ oid => '1751', descr => 'negate',
oprname => '-', oprkind => 'l', oprleft => '0', oprright => 'numeric',
oprresult => 'numeric', oprcode => 'numeric_uminus' },
-{ oid => '1752', descr => 'equal',
+{ oid => '1752', oid_symbol => 'NumericEqOperator', descr => 'equal',
oprname => '=', oprcanmerge => 't', oprcanhash => 't', oprleft => 'numeric',
oprright => 'numeric', oprresult => 'bool', oprcom => '=(numeric,numeric)',
oprnegate => '<>(numeric,numeric)', oprcode => 'numeric_eq',
@@ -2077,7 +2077,7 @@
oprresult => 'bool', oprcom => '<>(numeric,numeric)',
oprnegate => '=(numeric,numeric)', oprcode => 'numeric_ne',
oprrest => 'neqsel', oprjoin => 'neqjoinsel' },
-{ oid => '1754', descr => 'less than',
+{ oid => '1754', oid_symbol => 'NumericLtOperator', descr => 'less than',
oprname => '<', oprleft => 'numeric', oprright => 'numeric',
oprresult => 'bool', oprcom => '>(numeric,numeric)',
oprnegate => '>=(numeric,numeric)', oprcode => 'numeric_lt',
@@ -3172,70 +3172,77 @@
{ oid => '3967', descr => 'get value from json as text with path elements',
oprname => '#>>', oprleft => 'json', oprright => '_text', oprresult => 'text',
oprcode => 'json_extract_path_text' },
-{ oid => '3211', descr => 'get jsonb object field',
+{ oid => '3211', oid_symbol => 'JsonbObjectFieldOperator',
+ descr => 'get jsonb object field',
oprname => '->', oprleft => 'jsonb', oprright => 'text', oprresult => 'jsonb',
oprcode => 'jsonb_object_field' },
-{ oid => '3477', descr => 'get jsonb object field as text',
+{ oid => '3477', oid_symbol => 'JsonbObjectFieldTextOperator',
+ descr => 'get jsonb object field as text',
oprname => '->>', oprleft => 'jsonb', oprright => 'text', oprresult => 'text',
oprcode => 'jsonb_object_field_text' },
-{ oid => '3212', descr => 'get jsonb array element',
+{ oid => '3212', oid_symbol => 'JsonbArrayElementOperator',
+ descr => 'get jsonb array element',
oprname => '->', oprleft => 'jsonb', oprright => 'int4', oprresult => 'jsonb',
oprcode => 'jsonb_array_element' },
-{ oid => '3481', descr => 'get jsonb array element as text',
+{ oid => '3481', oid_symbol => 'JsonbArrayElementTextOperator',
+ descr => 'get jsonb array element as text',
oprname => '->>', oprleft => 'jsonb', oprright => 'int4', oprresult => 'text',
oprcode => 'jsonb_array_element_text' },
-{ oid => '3213', descr => 'get value from jsonb with path elements',
+{ oid => '3213', oid_symbol => 'JsonbExtractPathOperator',
+ descr => 'get value from jsonb with path elements',
oprname => '#>', oprleft => 'jsonb', oprright => '_text',
oprresult => 'jsonb', oprcode => 'jsonb_extract_path' },
-{ oid => '3206', descr => 'get value from jsonb as text with path elements',
+{ oid => '3206', oid_symbol => 'JsonbExtractPathTextOperator',
+ descr => 'get value from jsonb as text with path elements',
oprname => '#>>', oprleft => 'jsonb', oprright => '_text',
oprresult => 'text', oprcode => 'jsonb_extract_path_text' },
-{ oid => '3240', descr => 'equal',
+{ oid => '3240', oid_symbol => 'JsonbEqOperator', descr => 'equal',
oprname => '=', oprcanmerge => 't', oprcanhash => 't', oprleft => 'jsonb',
oprright => 'jsonb', oprresult => 'bool', oprcom => '=(jsonb,jsonb)',
oprnegate => '<>(jsonb,jsonb)', oprcode => 'jsonb_eq', oprrest => 'eqsel',
oprjoin => 'eqjoinsel' },
-{ oid => '3241', descr => 'not equal',
+{ oid => '3241', oid_symbol => 'JsonbNeOperator', descr => 'not equal',
oprname => '<>', oprleft => 'jsonb', oprright => 'jsonb', oprresult => 'bool',
oprcom => '<>(jsonb,jsonb)', oprnegate => '=(jsonb,jsonb)',
oprcode => 'jsonb_ne', oprrest => 'neqsel', oprjoin => 'neqjoinsel' },
-{ oid => '3242', descr => 'less than',
+{ oid => '3242', oid_symbol => 'JsonbLtOperator', descr => 'less than',
oprname => '<', oprleft => 'jsonb', oprright => 'jsonb', oprresult => 'bool',
oprcom => '>(jsonb,jsonb)', oprnegate => '>=(jsonb,jsonb)',
oprcode => 'jsonb_lt', oprrest => 'scalarltsel',
oprjoin => 'scalarltjoinsel' },
-{ oid => '3243', descr => 'greater than',
+{ oid => '3243', oid_symbol => 'JsonbGtOperator', descr => 'greater than',
oprname => '>', oprleft => 'jsonb', oprright => 'jsonb', oprresult => 'bool',
oprcom => '<(jsonb,jsonb)', oprnegate => '<=(jsonb,jsonb)',
oprcode => 'jsonb_gt', oprrest => 'scalargtsel',
oprjoin => 'scalargtjoinsel' },
-{ oid => '3244', descr => 'less than or equal',
+{ oid => '3244', oid_symbol => 'JsonbLeOperator', descr => 'less than or equal',
oprname => '<=', oprleft => 'jsonb', oprright => 'jsonb', oprresult => 'bool',
oprcom => '>=(jsonb,jsonb)', oprnegate => '>(jsonb,jsonb)',
oprcode => 'jsonb_le', oprrest => 'scalarlesel',
oprjoin => 'scalarlejoinsel' },
-{ oid => '3245', descr => 'greater than or equal',
+{ oid => '3245', oid_symbol => 'JsonbGeOperator',
+ descr => 'greater than or equal',
oprname => '>=', oprleft => 'jsonb', oprright => 'jsonb', oprresult => 'bool',
oprcom => '<=(jsonb,jsonb)', oprnegate => '<(jsonb,jsonb)',
oprcode => 'jsonb_ge', oprrest => 'scalargesel',
oprjoin => 'scalargejoinsel' },
-{ oid => '3246', descr => 'contains',
+{ oid => '3246', oid_symbol => 'JsonbContainsOperator', descr => 'contains',
oprname => '@>', oprleft => 'jsonb', oprright => 'jsonb', oprresult => 'bool',
oprcom => '<@(jsonb,jsonb)', oprcode => 'jsonb_contains',
oprrest => 'matchingsel', oprjoin => 'matchingjoinsel' },
-{ oid => '3247', descr => 'key exists',
+{ oid => '3247', oid_symbol => 'JsonbExistsOperator', descr => 'key exists',
oprname => '?', oprleft => 'jsonb', oprright => 'text', oprresult => 'bool',
oprcode => 'jsonb_exists', oprrest => 'matchingsel',
oprjoin => 'matchingjoinsel' },
-{ oid => '3248', descr => 'any key exists',
+{ oid => '3248', oid_symbol => 'JsonbExistsAnyOperator', descr => 'any key exists',
oprname => '?|', oprleft => 'jsonb', oprright => '_text', oprresult => 'bool',
oprcode => 'jsonb_exists_any', oprrest => 'matchingsel',
oprjoin => 'matchingjoinsel' },
-{ oid => '3249', descr => 'all keys exist',
+{ oid => '3249', oid_symbol => 'JsonbExistsAllOperator', descr => 'all keys exist',
oprname => '?&', oprleft => 'jsonb', oprright => '_text', oprresult => 'bool',
oprcode => 'jsonb_exists_all', oprrest => 'matchingsel',
oprjoin => 'matchingjoinsel' },
-{ oid => '3250', descr => 'is contained by',
+{ oid => '3250', oid_symbol => 'JsonbContainedOperator', descr => 'is contained by',
oprname => '<@', oprleft => 'jsonb', oprright => 'jsonb', oprresult => 'bool',
oprcom => '@>(jsonb,jsonb)', oprcode => 'jsonb_contained',
oprrest => 'matchingsel', oprjoin => 'matchingjoinsel' },
--
2.31.1
[text/x-patch] 0004-Add-helper-jsonb-functions-and-macros-20220106.patch (9.7K, ../[email protected]/5-0004-Add-helper-jsonb-functions-and-macros-20220106.patch)
download | inline diff:
From 77b50a382338ce29cb963706d29de3fa6a5a3514 Mon Sep 17 00:00:00 2001
From: Nikita Glukhov <[email protected]>
Date: Fri, 16 Dec 2016 17:16:47 +0300
Subject: [PATCH 4/6] Add helper jsonb functions and macros
---
src/backend/utils/adt/jsonb_util.c | 27 +++++
src/backend/utils/adt/jsonfuncs.c | 10 +-
src/include/utils/jsonb.h | 166 ++++++++++++++++++++++++++++-
3 files changed, 196 insertions(+), 7 deletions(-)
diff --git a/src/backend/utils/adt/jsonb_util.c b/src/backend/utils/adt/jsonb_util.c
index 57111877959..8538fc6c37f 100644
--- a/src/backend/utils/adt/jsonb_util.c
+++ b/src/backend/utils/adt/jsonb_util.c
@@ -388,6 +388,22 @@ findJsonbValueFromContainer(JsonbContainer *container, uint32 flags,
return NULL;
}
+/*
+ * findJsonbValueFromContainer() wrapper that sets up JsonbValue key string.
+ */
+JsonbValue *
+findJsonbValueFromContainerLen(JsonbContainer *container, uint32 flags,
+ const char *key, uint32 keylen)
+{
+ JsonbValue k;
+
+ k.type = jbvString;
+ k.val.string.val = key;
+ k.val.string.len = keylen;
+
+ return findJsonbValueFromContainer(container, flags, &k);
+}
+
/*
* Find value by key in Jsonb object and fetch it into 'res', which is also
* returned.
@@ -602,6 +618,17 @@ pushJsonbValue(JsonbParseState **pstate, JsonbIteratorToken seq,
return pushJsonbValueScalar(pstate, seq, jbval);
}
+ /*
+ * XXX I'm not quite sure why we actually do this? Why do we need to change
+ * how JsonbValue is converted to Jsonb for the statistics patch?
+ */
+ /* push value from scalar container without its enclosing array */
+ if (*pstate && JsonbExtractScalar(jbval->val.binary.data, &v))
+ {
+ Assert(IsAJsonbScalar(&v));
+ return pushJsonbValueScalar(pstate, seq, &v);
+ }
+
/* unpack the binary and add each piece to the pstate */
it = JsonbIteratorInit(jbval->val.binary.data);
diff --git a/src/backend/utils/adt/jsonfuncs.c b/src/backend/utils/adt/jsonfuncs.c
index 6335845d08e..adfaa8ec5ce 100644
--- a/src/backend/utils/adt/jsonfuncs.c
+++ b/src/backend/utils/adt/jsonfuncs.c
@@ -5371,7 +5371,8 @@ iterate_jsonb_values(Jsonb *jb, uint32 flags, void *state,
if (type == WJB_KEY)
{
if (flags & jtiKey)
- action(state, v.val.string.val, v.val.string.len);
+ action(state, unconstify(char *, v.val.string.val),
+ v.val.string.len);
continue;
}
@@ -5386,7 +5387,8 @@ iterate_jsonb_values(Jsonb *jb, uint32 flags, void *state,
{
case jbvString:
if (flags & jtiString)
- action(state, v.val.string.val, v.val.string.len);
+ action(state, unconstify(char *, v.val.string.val),
+ v.val.string.len);
break;
case jbvNumeric:
if (flags & jtiNumeric)
@@ -5508,7 +5510,9 @@ transform_jsonb_string_values(Jsonb *jsonb, void *action_state,
{
if ((type == WJB_VALUE || type == WJB_ELEM) && v.type == jbvString)
{
- out = transform_action(action_state, v.val.string.val, v.val.string.len);
+ out = transform_action(action_state,
+ unconstify(char *, v.val.string.val),
+ v.val.string.len);
v.val.string.val = VARDATA_ANY(out);
v.val.string.len = VARSIZE_ANY_EXHDR(out);
res = pushJsonbValue(&st, type, type < WJB_BEGIN_ARRAY ? &v : NULL);
diff --git a/src/include/utils/jsonb.h b/src/include/utils/jsonb.h
index 4e07debf785..95cecb63ce7 100644
--- a/src/include/utils/jsonb.h
+++ b/src/include/utils/jsonb.h
@@ -14,6 +14,7 @@
#include "lib/stringinfo.h"
#include "utils/array.h"
+#include "utils/builtins.h"
#include "utils/numeric.h"
/* Tokens used when sequentially processing a jsonb value */
@@ -229,8 +230,7 @@ typedef struct
#define JB_ROOT_IS_OBJECT(jbp_) ((*(uint32 *) VARDATA(jbp_) & JB_FOBJECT) != 0)
#define JB_ROOT_IS_ARRAY(jbp_) ((*(uint32 *) VARDATA(jbp_) & JB_FARRAY) != 0)
-
-enum jbvType
+typedef enum jbvType
{
/* Scalar types */
jbvNull = 0x0,
@@ -250,7 +250,7 @@ enum jbvType
* into JSON strings when outputted to json/jsonb.
*/
jbvDatetime = 0x20,
-};
+} JsonbValueType;
/*
* JsonbValue: In-memory representation of Jsonb. This is a convenient
@@ -269,7 +269,7 @@ struct JsonbValue
struct
{
int len;
- char *val; /* Not necessarily null-terminated */
+ const char *val; /* Not necessarily null-terminated */
} string; /* String primitive type */
struct
@@ -382,6 +382,10 @@ extern int compareJsonbContainers(JsonbContainer *a, JsonbContainer *b);
extern JsonbValue *findJsonbValueFromContainer(JsonbContainer *sheader,
uint32 flags,
JsonbValue *key);
+extern JsonbValue *findJsonbValueFromContainerLen(JsonbContainer *container,
+ uint32 flags,
+ const char *key,
+ uint32 keylen);
extern JsonbValue *getKeyJsonValueFromContainer(JsonbContainer *container,
const char *keyVal, int keyLen,
JsonbValue *res);
@@ -399,6 +403,7 @@ extern bool JsonbDeepContains(JsonbIterator **val,
extern void JsonbHashScalarValue(const JsonbValue *scalarVal, uint32 *hash);
extern void JsonbHashScalarValueExtended(const JsonbValue *scalarVal,
uint64 *hash, uint64 seed);
+extern bool JsonbExtractScalar(JsonbContainer *jbc, JsonbValue *res);
/* jsonb.c support functions */
extern char *JsonbToCString(StringInfo out, JsonbContainer *in,
@@ -412,4 +417,157 @@ extern Datum jsonb_set_element(Jsonb *jb, Datum *path, int path_len,
JsonbValue *newval);
extern Datum jsonb_get_element(Jsonb *jb, Datum *path, int npath,
bool *isnull, bool as_text);
+
+/*
+ * XXX Not sure we want to add these functions to jsonb.h, which is the
+ * public API. Maybe it belongs rather to jsonb_typanalyze.c or elsewhere,
+ * closer to how it's used?
+ */
+
+/* helper inline functions for JsonbValue initialization */
+static inline JsonbValue *
+JsonValueInitObject(JsonbValue *val, int nPairs, int nPairsAllocated)
+{
+ val->type = jbvObject;
+ val->val.object.nPairs = nPairs;
+ val->val.object.pairs = nPairsAllocated ?
+ palloc(sizeof(JsonbPair) * nPairsAllocated) : NULL;
+
+ return val;
+}
+
+static inline JsonbValue *
+JsonValueInitArray(JsonbValue *val, int nElems, int nElemsAllocated,
+ bool rawScalar)
+{
+ val->type = jbvArray;
+ val->val.array.nElems = nElems;
+ val->val.array.elems = nElemsAllocated ?
+ palloc(sizeof(JsonbValue) * nElemsAllocated) : NULL;
+ val->val.array.rawScalar = rawScalar;
+
+ return val;
+}
+
+static inline JsonbValue *
+JsonValueInitBinary(JsonbValue *val, Jsonb *jb)
+{
+ val->type = jbvBinary;
+ val->val.binary.data = &(jb)->root;
+ val->val.binary.len = VARSIZE_ANY_EXHDR(jb);
+ return val;
+}
+
+
+static inline JsonbValue *
+JsonValueInitString(JsonbValue *jbv, const char *str)
+{
+ jbv->type = jbvString;
+ jbv->val.string.len = strlen(str);
+ jbv->val.string.val = memcpy(palloc(jbv->val.string.len + 1), str,
+ jbv->val.string.len + 1);
+ return jbv;
+}
+
+static inline JsonbValue *
+JsonValueInitStringWithLen(JsonbValue *jbv, const char *str, int len)
+{
+ jbv->type = jbvString;
+ jbv->val.string.val = str;
+ jbv->val.string.len = len;
+ return jbv;
+}
+
+static inline JsonbValue *
+JsonValueInitText(JsonbValue *jbv, text *txt)
+{
+ jbv->type = jbvString;
+ jbv->val.string.val = VARDATA_ANY(txt);
+ jbv->val.string.len = VARSIZE_ANY_EXHDR(txt);
+ return jbv;
+}
+
+static inline JsonbValue *
+JsonValueInitNumeric(JsonbValue *jbv, Numeric num)
+{
+ jbv->type = jbvNumeric;
+ jbv->val.numeric = num;
+ return jbv;
+}
+
+static inline JsonbValue *
+JsonValueInitInteger(JsonbValue *jbv, int64 i)
+{
+ jbv->type = jbvNumeric;
+ jbv->val.numeric = DatumGetNumeric(DirectFunctionCall1(
+ int8_numeric, Int64GetDatum(i)));
+ return jbv;
+}
+
+static inline JsonbValue *
+JsonValueInitFloat(JsonbValue *jbv, float4 f)
+{
+ jbv->type = jbvNumeric;
+ jbv->val.numeric = DatumGetNumeric(DirectFunctionCall1(
+ float4_numeric, Float4GetDatum(f)));
+ return jbv;
+}
+
+static inline JsonbValue *
+JsonValueInitDouble(JsonbValue *jbv, float8 f)
+{
+ jbv->type = jbvNumeric;
+ jbv->val.numeric = DatumGetNumeric(DirectFunctionCall1(
+ float8_numeric, Float8GetDatum(f)));
+ return jbv;
+}
+
+/* helper macros for jsonb building */
+#define pushJsonbKey(pstate, jbv, key) \
+ pushJsonbValue(pstate, WJB_KEY, JsonValueInitString(jbv, key))
+
+#define pushJsonbValueGeneric(Type, pstate, jbv, val) \
+ pushJsonbValue(pstate, WJB_VALUE, JsonValueInit##Type(jbv, val))
+
+#define pushJsonbElemGeneric(Type, pstate, jbv, val) \
+ pushJsonbValue(pstate, WJB_ELEM, JsonValueInit##Type(jbv, val))
+
+#define pushJsonbValueInteger(pstate, jbv, i) \
+ pushJsonbValueGeneric(Integer, pstate, jbv, i)
+
+#define pushJsonbValueFloat(pstate, jbv, f) \
+ pushJsonbValueGeneric(Float, pstate, jbv, f)
+
+#define pushJsonbElemFloat(pstate, jbv, f) \
+ pushJsonbElemGeneric(Float, pstate, jbv, f)
+
+#define pushJsonbElemString(pstate, jbv, txt) \
+ pushJsonbElemGeneric(String, pstate, jbv, txt)
+
+#define pushJsonbElemText(pstate, jbv, txt) \
+ pushJsonbElemGeneric(Text, pstate, jbv, txt)
+
+#define pushJsonbElemNumeric(pstate, jbv, num) \
+ pushJsonbElemGeneric(Numeric, pstate, jbv, num)
+
+#define pushJsonbElemInteger(pstate, jbv, num) \
+ pushJsonbElemGeneric(Integer, pstate, jbv, num)
+
+#define pushJsonbElemBinary(pstate, jbv, jbcont) \
+ pushJsonbElemGeneric(Binary, pstate, jbv, jbcont)
+
+#define pushJsonbKeyValueGeneric(Type, pstate, jbv, key, val) ( \
+ pushJsonbKey(pstate, jbv, key), \
+ pushJsonbValueGeneric(Type, pstate, jbv, val) \
+ )
+
+#define pushJsonbKeyValueString(pstate, jbv, key, val) \
+ pushJsonbKeyValueGeneric(String, pstate, jbv, key, val)
+
+#define pushJsonbKeyValueFloat(pstate, jbv, key, val) \
+ pushJsonbKeyValueGeneric(Float, pstate, jbv, key, val)
+
+#define pushJsonbKeyValueInteger(pstate, jbv, key, val) \
+ pushJsonbKeyValueGeneric(Integer, pstate, jbv, key, val)
+
#endif /* __JSONB_H__ */
--
2.31.1
[text/x-patch] 0005-Export-scalarineqsel-20220106.patch (1.5K, ../[email protected]/6-0005-Export-scalarineqsel-20220106.patch)
download | inline diff:
From 4a6290e876a7d83129f7a01a8549d5d356a0c287 Mon Sep 17 00:00:00 2001
From: Nikita Glukhov <[email protected]>
Date: Sat, 12 Nov 2016 19:01:43 +0300
Subject: [PATCH 5/6] Export scalarineqsel()
---
src/backend/utils/adt/selfuncs.c | 2 +-
src/include/utils/selfuncs.h | 4 ++++
2 files changed, 5 insertions(+), 1 deletion(-)
diff --git a/src/backend/utils/adt/selfuncs.c b/src/backend/utils/adt/selfuncs.c
index 3e8fc47f662..1478ee536ad 100644
--- a/src/backend/utils/adt/selfuncs.c
+++ b/src/backend/utils/adt/selfuncs.c
@@ -573,7 +573,7 @@ neqsel(PG_FUNCTION_ARGS)
* it will return an approximate estimate based on assuming that the constant
* value falls in the middle of the bin identified by binary search.
*/
-static double
+double
scalarineqsel(PlannerInfo *root, Oid operator, bool isgt, bool iseq,
Oid collation,
VariableStatData *vardata, Datum constval, Oid consttype)
diff --git a/src/include/utils/selfuncs.h b/src/include/utils/selfuncs.h
index b90e05b8707..74841a108b4 100644
--- a/src/include/utils/selfuncs.h
+++ b/src/include/utils/selfuncs.h
@@ -249,6 +249,10 @@ extern List *add_predicate_to_index_quals(IndexOptInfo *index,
extern void genericcostestimate(PlannerInfo *root, IndexPath *path,
double loop_count,
GenericCosts *costs);
+extern double scalarineqsel(PlannerInfo *root, Oid operator, bool isgt,
+ bool iseq, Oid collation,
+ VariableStatData *vardata, Datum constval,
+ Oid consttype);
extern HeapTuple stats_form_tuple(StatsData *data);
--
2.31.1
[text/x-patch] 0006-Add-jsonb-statistics-20220106.patch (121.4K, ../[email protected]/7-0006-Add-jsonb-statistics-20220106.patch)
download | inline diff:
From a44d9cfa7a7c1a41c110f717082af77a00202402 Mon Sep 17 00:00:00 2001
From: Nikita Glukhov <[email protected]>
Date: Sat, 12 Nov 2016 19:19:33 +0300
Subject: [PATCH 6/6] Add jsonb statistics
---
src/backend/catalog/system_functions.sql | 36 +
src/backend/catalog/system_views.sql | 56 +
src/backend/utils/adt/Makefile | 2 +
src/backend/utils/adt/jsonb_selfuncs.c | 1353 ++++++++++++++++++++
src/backend/utils/adt/jsonb_typanalyze.c | 1379 +++++++++++++++++++++
src/backend/utils/adt/jsonpath_exec.c | 2 +-
src/include/catalog/pg_operator.dat | 15 +-
src/include/catalog/pg_proc.dat | 11 +
src/include/catalog/pg_statistic.h | 2 +
src/include/catalog/pg_type.dat | 2 +-
src/include/utils/json_selfuncs.h | 100 ++
src/test/regress/expected/jsonb_stats.out | 713 +++++++++++
src/test/regress/expected/rules.out | 32 +
src/test/regress/parallel_schedule | 2 +-
src/test/regress/sql/jsonb_stats.sql | 249 ++++
15 files changed, 3944 insertions(+), 10 deletions(-)
create mode 100644 src/backend/utils/adt/jsonb_selfuncs.c
create mode 100644 src/backend/utils/adt/jsonb_typanalyze.c
create mode 100644 src/include/utils/json_selfuncs.h
create mode 100644 src/test/regress/expected/jsonb_stats.out
create mode 100644 src/test/regress/sql/jsonb_stats.sql
diff --git a/src/backend/catalog/system_functions.sql b/src/backend/catalog/system_functions.sql
index 3a4fa9091b1..53cf6fc893a 100644
--- a/src/backend/catalog/system_functions.sql
+++ b/src/backend/catalog/system_functions.sql
@@ -594,6 +594,42 @@ LANGUAGE internal
STRICT IMMUTABLE PARALLEL SAFE
AS 'unicode_is_normalized';
+-- XXX is this function immutable / parallel safe?
+-- XXX do we actually need to cast to text and then to jsonb?
+CREATE FUNCTION pg_json_path_stats(tab regclass, path_index integer) RETURNS text
+AS $$
+ SELECT jsonb_pretty((
+ CASE
+ WHEN stakind1 = 8 THEN stavalues1
+ WHEN stakind2 = 8 THEN stavalues2
+ WHEN stakind3 = 8 THEN stavalues3
+ WHEN stakind4 = 8 THEN stavalues4
+ WHEN stakind5 = 8 THEN stavalues5
+ END::text::jsonb[])[$2])
+ FROM pg_statistic
+ WHERE starelid = $1
+$$ LANGUAGE 'sql';
+
+-- XXX is this function immutable / parallel safe?
+-- XXX do we actually need to cast to text and then to jsonb?
+CREATE FUNCTION pg_json_path_stats(tab regclass, path text) RETURNS text
+AS $$
+ SELECT jsonb_pretty(pathstats)
+ FROM (
+ SELECT unnest(
+ CASE
+ WHEN stakind1 = 8 THEN stavalues1
+ WHEN stakind2 = 8 THEN stavalues2
+ WHEN stakind3 = 8 THEN stavalues3
+ WHEN stakind4 = 8 THEN stavalues4
+ WHEN stakind5 = 8 THEN stavalues5
+ END::text::jsonb[]) pathstats
+ FROM pg_statistic
+ WHERE starelid = $1
+ ) paths
+ WHERE pathstats->>'path' = $2
+$$ LANGUAGE 'sql';
+
--
-- The default permissions for functions mean that anyone can execute them.
-- A number of functions shouldn't be executable by just anyone, but rather
diff --git a/src/backend/catalog/system_views.sql b/src/backend/catalog/system_views.sql
index 61b515cdb85..357b8b7c5ed 100644
--- a/src/backend/catalog/system_views.sql
+++ b/src/backend/catalog/system_views.sql
@@ -362,6 +362,62 @@ CREATE VIEW pg_stats_ext_exprs WITH (security_barrier) AS
-- unprivileged users may read pg_statistic_ext but not pg_statistic_ext_data
REVOKE ALL ON pg_statistic_ext_data FROM public;
+-- XXX This probably needs to do the same checks as pg_stats, i.e.
+-- WHERE NOT attisdropped
+-- AND has_column_privilege(c.oid, a.attnum, 'select')
+-- AND (c.relrowsecurity = false OR NOT row_security_active(c.oid));
+CREATE VIEW pg_stats_json AS
+ SELECT
+ nspname AS schemaname,
+ relname AS tablename,
+ attname AS attname,
+
+ path->>'path' AS json_path,
+
+ stainherit AS inherited,
+
+ (path->'json'->>'nullfrac')::float4 AS null_frac,
+ (path->'json'->>'width')::float4 AS avg_width,
+ (path->'json'->>'distinct')::float4 AS n_distinct,
+
+ ARRAY(SELECT val FROM jsonb_array_elements(
+ path->'json'->'mcv'->'values') val)::anyarray
+ AS most_common_vals,
+
+ ARRAY(SELECT num::text::float4 FROM jsonb_array_elements(
+ path->'json'->'mcv'->'numbers') num)
+ AS most_common_freqs,
+
+ ARRAY(SELECT val FROM jsonb_array_elements(
+ path->'json'->'histogram'->'values') val)
+ AS histogram_bounds,
+
+ ARRAY(SELECT val::text::int FROM jsonb_array_elements(
+ path->'array_length'->'mcv'->'values') val)
+ AS most_common_array_lengths,
+
+ ARRAY(SELECT num::text::float4 FROM jsonb_array_elements(
+ path->'array_length'->'mcv'->'numbers') num)
+ AS most_common_array_length_freqs,
+
+ (path->'json'->>'correlation')::float4 AS correlation
+
+ FROM
+ pg_statistic s JOIN pg_class c ON (c.oid = s.starelid)
+ JOIN pg_attribute a ON (c.oid = attrelid AND attnum = s.staattnum)
+ LEFT JOIN pg_namespace n ON (n.oid = c.relnamespace),
+ LATERAL (
+ SELECT unnest((CASE
+ WHEN stakind1 = 8 THEN stavalues1
+ WHEN stakind2 = 8 THEN stavalues2
+ WHEN stakind3 = 8 THEN stavalues3
+ WHEN stakind4 = 8 THEN stavalues4
+ WHEN stakind5 = 8 THEN stavalues5
+ END ::text::jsonb[])[2:]) AS path
+ ) paths;
+
+-- no need to revoke any privileges, we've already revoked accss to pg_statistic
+
CREATE VIEW pg_publication_tables AS
SELECT
P.pubname AS pubname,
diff --git a/src/backend/utils/adt/Makefile b/src/backend/utils/adt/Makefile
index 41b486bceff..5e359ccf4fb 100644
--- a/src/backend/utils/adt/Makefile
+++ b/src/backend/utils/adt/Makefile
@@ -50,6 +50,8 @@ OBJS = \
jsonb.o \
jsonb_gin.o \
jsonb_op.o \
+ jsonb_selfuncs.o \
+ jsonb_typanalyze.o \
jsonb_util.o \
jsonfuncs.o \
jsonbsubs.o \
diff --git a/src/backend/utils/adt/jsonb_selfuncs.c b/src/backend/utils/adt/jsonb_selfuncs.c
new file mode 100644
index 00000000000..f144b8c0db3
--- /dev/null
+++ b/src/backend/utils/adt/jsonb_selfuncs.c
@@ -0,0 +1,1353 @@
+/*-------------------------------------------------------------------------
+ *
+ * jsonb_selfuncs.c
+ * Functions for selectivity estimation of jsonb operators
+ *
+ * Copyright (c) 2016, PostgreSQL Global Development Group
+ *
+ * IDENTIFICATION
+ * src/backend/utils/adt/jsonb_selfuncs.c
+ *
+ *-------------------------------------------------------------------------
+ */
+
+#include "postgres.h"
+
+#include <math.h>
+
+#include "fmgr.h"
+#include "access/heapam.h"
+#include "access/htup_details.h"
+#include "catalog/pg_operator.h"
+#include "catalog/pg_statistic.h"
+#include "catalog/pg_type.h"
+#include "nodes/primnodes.h"
+#include "utils/builtins.h"
+#include "utils/json.h"
+#include "utils/jsonb.h"
+#include "utils/json_selfuncs.h"
+#include "utils/lsyscache.h"
+#include "utils/rel.h"
+#include "utils/selfuncs.h"
+
+#define DEFAULT_JSON_CONTAINS_SEL 0.001
+
+/*
+ * jsonGetField
+ * Given a JSONB document and a key, extract the JSONB value for the key.
+ */
+static inline Datum
+jsonGetField(Datum obj, const char *field)
+{
+ Jsonb *jb = DatumGetJsonbP(obj);
+ JsonbValue *jbv = findJsonbValueFromContainerLen(&jb->root, JB_FOBJECT,
+ field, strlen(field));
+ return jbv ? JsonbPGetDatum(JsonbValueToJsonb(jbv)) : PointerGetDatum(NULL);
+}
+
+/*
+ * jsonGetFloat4
+ * Given a JSONB value, interpret it as a float4 value.
+ *
+ * This expects the JSONB value to be a numeric, because that's how we store
+ * floats in JSONB, and we cast it to float4.
+ *
+ * XXX Not sure assert is a sufficient protection against different types of
+ * JSONB values to be passed in.
+ */
+static inline Datum
+jsonGetFloat4(Datum jsonb)
+{
+ Jsonb *jb = DatumGetJsonbP(jsonb);
+ JsonbValue jv;
+
+ JsonbExtractScalar(&jb->root, &jv);
+ Assert(jv.type == jbvNumeric);
+
+ return DirectFunctionCall1(numeric_float4, NumericGetDatum(jv.val.numeric));
+}
+
+/*
+ * jsonStatsInit
+ * Given a pg_statistic tuple, expand STATISTIC_KIND_JSON into JsonStats.
+ */
+bool
+jsonStatsInit(JsonStats data, const VariableStatData *vardata)
+{
+ Jsonb *jb;
+ JsonbValue prefix;
+
+ memset(&data->attslot, 0, sizeof(data->attslot));
+ data->statsTuple = vardata->statsTuple;
+
+ /* FIXME Could be before the memset, I guess? Checking vardata->statsTuple. */
+ if (!data->statsTuple)
+ return false;
+
+ /* Were there just NULL values in the column? No JSON stats, but still useful. */
+ if (((Form_pg_statistic) GETSTRUCT(data->statsTuple))->stanullfrac >= 1.0)
+ {
+ data->nullfrac = 1.0;
+ return true;
+ }
+
+ /* Do we have the JSON stats built in the pg_statistic? */
+ if (!get_attstatsslot(&data->attslot, data->statsTuple,
+ STATISTIC_KIND_JSON, InvalidOid,
+ ATTSTATSSLOT_NUMBERS | ATTSTATSSLOT_VALUES))
+ return false;
+
+ /* XXX Not sure what this means / how could it happen? */
+ if (data->attslot.nvalues < 2)
+ {
+ free_attstatsslot(&data->attslot);
+ return false;
+ }
+
+ /* XXX If the ACL check was not OK, would we even get here? */
+ data->acl_ok = vardata->acl_ok;
+ data->rel = vardata->rel;
+ data->nullfrac =
+ data->attslot.nnumbers > 0 ? data->attslot.numbers[0] : 0.0;
+ data->values = data->attslot.values;
+ data->nvalues = data->attslot.nvalues;
+
+ jb = DatumGetJsonbP(data->values[0]);
+ JsonbExtractScalar(&jb->root, &prefix);
+ Assert(prefix.type == jbvString);
+ data->prefix = prefix.val.string.val;
+ data->prefixlen = prefix.val.string.len;
+
+ return true;
+}
+
+/*
+ * jsonStatsRelease
+ * Release resources (statistics slot) associated with the JsonStats value.
+ */
+void
+jsonStatsRelease(JsonStats data)
+{
+ free_attstatsslot(&data->attslot);
+}
+
+/*
+ * jsonPathStatsGetSpecialStats
+ * Extract statistics of given type for JSON path.
+ *
+ * XXX This does not really extract any stats, it merely allocates the struct?
+ */
+static JsonPathStats
+jsonPathStatsGetSpecialStats(JsonPathStats pstats, JsonPathStatsType type)
+{
+ JsonPathStats stats;
+
+ if (!pstats)
+ return NULL;
+
+ stats = palloc(sizeof(*stats));
+ *stats = *pstats;
+ stats->path = memcpy(palloc(stats->pathlen), stats->path, stats->pathlen);
+ stats->type = type;
+
+ return stats;
+}
+
+/*
+ * jsonPathStatsGetLengthStats
+ * Extract statistics of lengths (for arrays or objects) for the path.
+ */
+JsonPathStats
+jsonPathStatsGetLengthStats(JsonPathStats pstats)
+{
+ /*
+ * The length statistics is relevant only for values that are objects or
+ * arrays. So if we observed no such values, we know there can't be such
+ * statistics and so we simply return NULL.
+ */
+ if (jsonPathStatsGetTypeFreq(pstats, jbvObject, 0.0) <= 0.0 &&
+ jsonPathStatsGetTypeFreq(pstats, jbvArray, 0.0) <= 0.0)
+ return NULL;
+
+ return jsonPathStatsGetSpecialStats(pstats, JsonPathStatsLength);
+}
+
+/*
+ * jsonPathStatsGetArrayLengthStats
+ * Extract statistics of lengths for arrays.
+ *
+ * XXX Why doesn't this do jsonPathStatsGetTypeFreq check similar to what
+ * jsonPathStatsGetLengthStats does?
+ */
+static JsonPathStats
+jsonPathStatsGetArrayLengthStats(JsonPathStats pstats)
+{
+ return jsonPathStatsGetSpecialStats(pstats, JsonPathStatsArrayLength);
+}
+
+/*
+ * jsonPathStatsCompare
+ * Compare two JsonPathStats structs, so that we can sort them.
+ *
+ * We do this so that we can search for stats for a given path simply by
+ * bsearch().
+ *
+ * XXX We never build two structs for the same path, so we know the paths
+ * are different - one may be a prefix of the other, but then we sort the
+ * strings by length.
+ */
+static int
+jsonPathStatsCompare(const void *pv1, const void *pv2)
+{
+ JsonbValue pathkey;
+ JsonbValue *path2;
+ JsonbValue const *path1 = pv1;
+ /* XXX Seems a bit convoluted to first cast it to Datum, then Jsonb ... */
+ Datum const *pdatum = pv2;
+ Jsonb *jsonb = DatumGetJsonbP(*pdatum);
+ int res;
+
+ /* extract path from the statistics represented as jsonb document */
+ JsonValueInitStringWithLen(&pathkey, "path", 4);
+ path2 = findJsonbValueFromContainer(&jsonb->root, JB_FOBJECT, &pathkey);
+
+ /* XXX Not sure about this? Does empty path mean global stats? */
+ if (!path2 || path2->type != jbvString)
+ return 1;
+
+ /* compare the shared part first, then compare by length */
+ res = strncmp(path1->val.string.val, path2->val.string.val,
+ Min(path1->val.string.len, path2->val.string.len));
+
+ return res ? res : path1->val.string.len - path2->val.string.len;
+}
+
+/*
+ * jsonStatsFindPathStats
+ * Find stats for a given path.
+ *
+ * The stats are sorted by path, so we can simply do bsearch().
+ */
+static JsonPathStats
+jsonStatsFindPathStats(JsonStats jsdata, char *path, int pathlen)
+{
+ JsonPathStats stats;
+ JsonbValue jbvkey;
+ Datum *pdatum;
+
+ JsonValueInitStringWithLen(&jbvkey, path, pathlen);
+
+ pdatum = bsearch(&jbvkey, jsdata->values + 1, jsdata->nvalues - 1,
+ sizeof(*jsdata->values), jsonPathStatsCompare);
+
+ if (!pdatum)
+ return NULL;
+
+ stats = palloc(sizeof(*stats));
+ stats->datum = pdatum;
+ stats->data = jsdata;
+ stats->path = path;
+ stats->pathlen = pathlen;
+ stats->type = JsonPathStatsValues;
+
+ return stats;
+}
+
+/*
+ * jsonStatsGetPathStatsStr
+ * ???
+ *
+ * XXX Seems to do essentially what jsonStatsFindPathStats, except that it also
+ * considers jsdata->prefix. Seems fairly easy to combine those into a single
+ * function.
+ */
+JsonPathStats
+jsonStatsGetPathStatsStr(JsonStats jsdata, const char *subpath, int subpathlen)
+{
+ JsonPathStats stats;
+ char *path;
+ int pathlen;
+
+ if (jsdata->nullfrac >= 1.0)
+ return NULL;
+
+ pathlen = jsdata->prefixlen + subpathlen - 1;
+ path = palloc(pathlen);
+
+ memcpy(path, jsdata->prefix, jsdata->prefixlen);
+ memcpy(&path[jsdata->prefixlen], &subpath[1], subpathlen - 1);
+
+ stats = jsonStatsFindPathStats(jsdata, path, pathlen);
+
+ if (!stats)
+ pfree(path);
+
+ return stats;
+}
+
+/*
+ * jsonPathAppendEntry
+ * Append entry (represented as simple string) to a path.
+ */
+static void
+jsonPathAppendEntry(StringInfo path, const char *entry)
+{
+ appendStringInfoCharMacro(path, '.');
+ escape_json(path, entry);
+}
+
+/*
+ * jsonPathAppendEntryWithLen
+ * Append string (represented as string + length) to a path.
+ *
+ * XXX Doesn't this need ecape_json too?
+ */
+static void
+jsonPathAppendEntryWithLen(StringInfo path, const char *entry, int len)
+{
+ char *tmpentry = pnstrdup(entry, len);
+ jsonPathAppendEntry(path, tmpentry);
+ pfree(tmpentry);
+}
+
+/*
+ * jsonPathStatsGetSubpath
+ * ???
+ */
+JsonPathStats
+jsonPathStatsGetSubpath(JsonPathStats pstats, const char *key, int keylen)
+{
+ JsonPathStats spstats;
+ char *path;
+ int pathlen;
+
+ if (key)
+ {
+ StringInfoData str;
+
+ initStringInfo(&str);
+ appendBinaryStringInfo(&str, pstats->path, pstats->pathlen);
+ jsonPathAppendEntryWithLen(&str, key, keylen);
+
+ path = str.data;
+ pathlen = str.len;
+ }
+ else
+ {
+ pathlen = pstats->pathlen + 2;
+ path = palloc(pathlen + 1);
+ snprintf(path, pstats->pathlen + pathlen, "%.*s.#",
+ pstats->pathlen, pstats->path);
+ }
+
+ spstats = jsonStatsFindPathStats(pstats->data, path, pathlen);
+ if (!spstats)
+ pfree(path);
+
+ return spstats;
+}
+
+/*
+ * jsonPathStatsGetArrayIndexSelectivity
+ * Given stats for a path, determine selectivity for an array index.
+ */
+Selectivity
+jsonPathStatsGetArrayIndexSelectivity(JsonPathStats pstats, int index)
+{
+ JsonPathStats lenstats = jsonPathStatsGetArrayLengthStats(pstats);
+ JsonbValue tmpjbv;
+ Jsonb *jb;
+
+ /*
+ * If we have no array length stats, assume all documents match.
+ *
+ * XXX Shouldn't this use a default smaller than 1.0? What do the selfuncs
+ * for regular arrays use?
+ */
+ if (!lenstats)
+ return 1.0;
+
+ jb = JsonbValueToJsonb(JsonValueInitInteger(&tmpjbv, index));
+
+ /* calculate fraction of elements smaller than the index */
+ return jsonSelectivity(lenstats, JsonbPGetDatum(jb), JsonbGtOperator);
+}
+
+/*
+ * jsonStatsGetPathStats
+ * ???
+ *
+ * XXX I guess pathLen stored number of pathEntries elements, so it should be
+ * nEntries or something. pathLen implies it's a string length.
+ */
+static JsonPathStats
+jsonStatsGetPathStats(JsonStats jsdata, Datum *pathEntries, int pathLen,
+ float4 *nullfrac)
+{
+ JsonPathStats pstats;
+ Selectivity sel = 1.0;
+ int i;
+
+ if (!pathEntries && pathLen < 0)
+ {
+ if ((pstats = jsonStatsGetPathStatsStr(jsdata, "$.#", 3)))
+ {
+ sel = jsonPathStatsGetArrayIndexSelectivity(pstats, -1 - pathLen);
+ sel /= jsonPathStatsGetFreq(pstats, 0.0);
+ }
+ }
+ else
+ {
+ pstats = jsonStatsGetPathStatsStr(jsdata, "$", 1);
+
+ for (i = 0; pstats && i < pathLen; i++)
+ {
+ char *key = text_to_cstring(DatumGetTextP(pathEntries[i]));
+ int keylen = strlen(key);
+
+ /* XXX What's this key "0123456789" about? */
+ if (key[0] >= '0' && key[0] <= '9' &&
+ key[strspn(key, "0123456789")] == '\0')
+ {
+ char *tail;
+ long index;
+
+ errno = 0;
+ index = strtol(key, &tail, 10);
+
+ if (*tail || errno || index > INT_MAX || index < 0)
+ pstats = jsonPathStatsGetSubpath(pstats, key, keylen);
+ else
+ {
+ float4 arrfreq;
+
+ /* FIXME consider key also */
+ pstats = jsonPathStatsGetSubpath(pstats, NULL, 0);
+ sel *= jsonPathStatsGetArrayIndexSelectivity(pstats, index);
+ arrfreq = jsonPathStatsGetFreq(pstats, 0.0);
+
+ if (arrfreq > 0.0)
+ sel /= arrfreq;
+ }
+ }
+ else
+ pstats = jsonPathStatsGetSubpath(pstats, key, keylen);
+
+ pfree(key);
+ }
+ }
+
+ *nullfrac = 1.0 - sel;
+
+ return pstats;
+}
+
+/*
+ * jsonPathStatsGetNextKeyStats
+ * ???
+ */
+bool
+jsonPathStatsGetNextKeyStats(JsonPathStats stats, JsonPathStats *pkeystats,
+ bool keysOnly)
+{
+ JsonPathStats keystats = *pkeystats;
+ int index =
+ (keystats ? keystats->datum : stats->datum) - stats->data->values + 1;
+
+ for (; index < stats->data->nvalues; index++)
+ {
+ JsonbValue pathkey;
+ JsonbValue *jbvpath;
+ Jsonb *jb = DatumGetJsonbP(stats->data->values[index]);
+
+ JsonValueInitStringWithLen(&pathkey, "path", 4);
+ jbvpath = findJsonbValueFromContainer(&jb->root, JB_FOBJECT, &pathkey);
+
+ if (jbvpath->type != jbvString ||
+ jbvpath->val.string.len <= stats->pathlen ||
+ memcmp(jbvpath->val.string.val, stats->path, stats->pathlen))
+ break;
+
+ if (keysOnly)
+ {
+ const char *c = &jbvpath->val.string.val[stats->pathlen];
+
+ Assert(*c == '.');
+ c++;
+
+ if (*c == '#')
+ {
+ if (keysOnly || jbvpath->val.string.len > stats->pathlen + 2)
+ continue;
+ }
+ else
+ {
+ Assert(*c == '"');
+
+ while (*++c != '"')
+ if (*c == '\\')
+ c++;
+
+ if (c - jbvpath->val.string.val < jbvpath->val.string.len - 1)
+ continue;
+ }
+ }
+
+ if (!keystats)
+ keystats = palloc(sizeof(*keystats));
+
+ keystats->datum = &stats->data->values[index];
+ keystats->data = stats->data;
+ keystats->pathlen = jbvpath->val.string.len;
+ keystats->path = memcpy(palloc(keystats->pathlen),
+ jbvpath->val.string.val, keystats->pathlen);
+ keystats->type = JsonPathStatsValues;
+
+ *pkeystats = keystats;
+
+ return true;
+ }
+
+ return false;
+}
+
+/*
+ * jsonStatsConvertArray
+ * Convert a JSONB array into an array of some regular data type.
+ *
+ * The "type" identifies what elements are in the input JSONB array, while
+ * typid determines the target type.
+ */
+static Datum
+jsonStatsConvertArray(Datum jsonbValueArray, JsonStatType type, Oid typid,
+ float4 multiplier)
+{
+ Datum *values;
+ Jsonb *jbvals;
+ JsonbValue jbv;
+ JsonbIterator *it;
+ JsonbIteratorToken r;
+ int nvalues;
+ int i;
+
+ if (!DatumGetPointer(jsonbValueArray))
+ return PointerGetDatum(NULL);
+
+ jbvals = DatumGetJsonbP(jsonbValueArray);
+
+ nvalues = JsonContainerSize(&jbvals->root);
+
+ values = palloc(sizeof(Datum) * nvalues);
+
+ for (i = 0, it = JsonbIteratorInit(&jbvals->root);
+ (r = JsonbIteratorNext(&it, &jbv, true)) != WJB_DONE;)
+ {
+ if (r == WJB_ELEM)
+ {
+ Datum value;
+
+ switch (type)
+ {
+ case JsonStatJsonb:
+ case JsonStatJsonbWithoutSubpaths:
+ value = JsonbPGetDatum(JsonbValueToJsonb(&jbv));
+ break;
+
+ case JsonStatText:
+ case JsonStatString:
+ Assert(jbv.type == jbvString);
+ value = PointerGetDatum(
+ cstring_to_text_with_len(jbv.val.string.val,
+ jbv.val.string.len));
+ break;
+
+ case JsonStatNumeric:
+ Assert(jbv.type == jbvNumeric);
+ value = DirectFunctionCall1(numeric_float4,
+ NumericGetDatum(jbv.val.numeric));
+ value = Float4GetDatum(DatumGetFloat4(value) * multiplier);
+ break;
+
+ default:
+ elog(ERROR, "invalid json stat type %d", type);
+ value = (Datum) 0;
+ break;
+ }
+
+ Assert(i < nvalues);
+ values[i++] = value;
+ }
+ }
+
+ Assert(i == nvalues);
+
+ /*
+ * FIXME Does this actually work on all 32/64-bit systems? What if typid is
+ * FLOAT8OID or something? Should look at TypeCache instead, probably.
+ */
+ return PointerGetDatum(
+ construct_array(values, nvalues,
+ typid,
+ typid == FLOAT4OID ? 4 : -1,
+ typid == FLOAT4OID ? true /* FLOAT4PASSBYVAL */ : false,
+ 'i'));
+}
+
+/*
+ * jsonPathStatsExtractData
+ * Extract pg_statistics values from statistics for a single path.
+ *
+ *
+ */
+static bool
+jsonPathStatsExtractData(JsonPathStats pstats, JsonStatType stattype,
+ float4 nullfrac, StatsData *statdata)
+{
+ Datum data;
+ Datum nullf;
+ Datum dist;
+ Datum width;
+ Datum mcv;
+ Datum hst;
+ Datum corr;
+ Oid type;
+ Oid eqop;
+ Oid ltop;
+ const char *key;
+ StatsSlot *slot = statdata->slots;
+
+ nullfrac = 1.0 - (1.0 - pstats->data->nullfrac) * (1.0 - nullfrac);
+
+ switch (stattype)
+ {
+ case JsonStatJsonb:
+ case JsonStatJsonbWithoutSubpaths:
+ key = pstats->type == JsonPathStatsArrayLength ? "array_length" :
+ pstats->type == JsonPathStatsLength ? "length" : "json";
+ type = JSONBOID;
+ eqop = JsonbEqOperator;
+ ltop = JsonbLtOperator;
+ break;
+ case JsonStatText:
+ key = "text";
+ type = TEXTOID;
+ eqop = TextEqualOperator;
+ ltop = TextLessOperator;
+ break;
+ case JsonStatString:
+ key = "string";
+ type = TEXTOID;
+ eqop = TextEqualOperator;
+ ltop = TextLessOperator;
+ break;
+ case JsonStatNumeric:
+ key = "numeric";
+ type = NUMERICOID;
+ eqop = NumericEqOperator;
+ ltop = NumericLtOperator;
+ break;
+ default:
+ elog(ERROR, "invalid json statistic type %d", stattype);
+ break;
+ }
+
+ data = jsonGetField(*pstats->datum, key);
+
+ if (!DatumGetPointer(data))
+ return false;
+
+ nullf = jsonGetField(data, "nullfrac");
+ dist = jsonGetField(data, "distinct");
+ width = jsonGetField(data, "width");
+ mcv = jsonGetField(data, "mcv");
+ hst = jsonGetField(data, "histogram");
+ corr = jsonGetField(data, "correlation");
+
+ statdata->nullfrac = DatumGetPointer(nullf) ?
+ DatumGetFloat4(jsonGetFloat4(nullf)) : 0.0;
+ statdata->distinct = DatumGetPointer(dist) ?
+ DatumGetFloat4(jsonGetFloat4(dist)) : 0.0;
+ statdata->width = DatumGetPointer(width) ?
+ (int32) DatumGetFloat4(jsonGetFloat4(width)) : 0;
+
+ statdata->nullfrac += (1.0 - statdata->nullfrac) * nullfrac;
+
+ if (DatumGetPointer(mcv))
+ {
+ slot->kind = STATISTIC_KIND_MCV;
+ slot->opid = eqop;
+ slot->numbers = jsonStatsConvertArray(jsonGetField(mcv, "numbers"),
+ JsonStatNumeric, FLOAT4OID,
+ 1.0 - nullfrac);
+ slot->values = jsonStatsConvertArray(jsonGetField(mcv, "values"),
+ stattype, type, 0);
+ slot++;
+ }
+
+ if (DatumGetPointer(hst))
+ {
+ slot->kind = STATISTIC_KIND_HISTOGRAM;
+ slot->opid = ltop;
+ slot->numbers = jsonStatsConvertArray(jsonGetField(hst, "numbers"),
+ JsonStatNumeric, FLOAT4OID, 1.0);
+ slot->values = jsonStatsConvertArray(jsonGetField(hst, "values"),
+ stattype, type, 0);
+ slot++;
+ }
+
+ if (DatumGetPointer(corr))
+ {
+ Datum correlation = jsonGetFloat4(corr);
+ slot->kind = STATISTIC_KIND_CORRELATION;
+ slot->opid = ltop;
+ slot->numbers = PointerGetDatum(construct_array(&correlation, 1,
+ FLOAT4OID, 4, true,
+ 'i'));
+ slot++;
+ }
+
+ if ((stattype == JsonStatJsonb ||
+ stattype == JsonStatJsonbWithoutSubpaths) &&
+ jsonAnalyzeBuildSubPathsData(pstats->data->values,
+ pstats->data->nvalues,
+ pstats->datum - pstats->data->values,
+ pstats->path,
+ pstats->pathlen,
+ stattype == JsonStatJsonb,
+ nullfrac,
+ &slot->values,
+ &slot->numbers))
+ {
+ slot->kind = STATISTIC_KIND_JSON;
+ slot++;
+ }
+
+ return true;
+}
+
+static float4
+jsonPathStatsGetFloat(JsonPathStats pstats, const char *key,
+ float4 defaultval)
+{
+ Datum freq;
+
+ if (!pstats || !(freq = jsonGetField(*pstats->datum, key)))
+ return defaultval;
+
+ return DatumGetFloat4(jsonGetFloat4(freq));
+}
+
+float4
+jsonPathStatsGetFreq(JsonPathStats pstats, float4 defaultfreq)
+{
+ return jsonPathStatsGetFloat(pstats, "freq", defaultfreq);
+}
+
+float4
+jsonPathStatsGetAvgArraySize(JsonPathStats pstats)
+{
+ return jsonPathStatsGetFloat(pstats, "avg_array_length", 1.0);
+}
+
+/*
+ * jsonPathStatsGetTypeFreq
+ * Get frequency of different JSON object types for a given path.
+ *
+ * JSON documents don't have any particular schema, and the same path may point
+ * to values with different types in multiple documents. Consider for example
+ * two documents {"a" : "b"} and {"a" : 100} which have both a string and int
+ * for the same path. So we track the frequency of different JSON types for
+ * each path, so that we can consider this later.
+ */
+float4
+jsonPathStatsGetTypeFreq(JsonPathStats pstats, JsonbValueType type,
+ float4 defaultfreq)
+{
+ const char *key;
+
+ if (!pstats)
+ return defaultfreq;
+
+ /*
+ * When dealing with (object/array) length stats, we only really care about
+ * objects and arrays.
+ */
+ if (pstats->type == JsonPathStatsLength ||
+ pstats->type == JsonPathStatsArrayLength)
+ {
+ /* XXX Seems more like an error, no? Why ignore it? */
+ if (type != jbvNumeric)
+ return 0.0;
+
+ /* FIXME This is really hard to read/understand, with two nested ternary operators. */
+ return pstats->type == JsonPathStatsArrayLength
+ ? jsonPathStatsGetFreq(pstats, defaultfreq)
+ : jsonPathStatsGetFloat(pstats, "freq_array", defaultfreq) +
+ jsonPathStatsGetFloat(pstats, "freq_object", defaultfreq);
+ }
+
+ /* Which JSON type are we interested in? Pick the right freq_type key. */
+ switch (type)
+ {
+ case jbvNull:
+ key = "freq_null";
+ break;
+ case jbvString:
+ key = "freq_string";
+ break;
+ case jbvNumeric:
+ key = "freq_numeric";
+ break;
+ case jbvBool:
+ key = "freq_boolean";
+ break;
+ case jbvObject:
+ key = "freq_object";
+ break;
+ case jbvArray:
+ key = "freq_array";
+ break;
+ default:
+ elog(ERROR, "Invalid jsonb value type: %d", type);
+ break;
+ }
+
+ return jsonPathStatsGetFloat(pstats, key, defaultfreq);
+}
+
+/*
+ * jsonPathStatsFormTuple
+ * For a pg_statistic tuple representing JSON statistics.
+ *
+ * XXX Maybe it's a bit expensive to first build StatsData and then transform it
+ * again while building the tuple. Could it be done in a single step? Would it be
+ * more efficient? Not sure how expensive it actually is, though.
+ */
+static HeapTuple
+jsonPathStatsFormTuple(JsonPathStats pstats, JsonStatType type, float4 nullfrac)
+{
+ StatsData statdata;
+
+ if (!pstats || !pstats->datum)
+ return NULL;
+
+ /* FIXME What does this mean? */
+ if (pstats->datum == &pstats->data->values[1] &&
+ pstats->type == JsonPathStatsValues)
+ return heap_copytuple(pstats->data->statsTuple);
+
+ MemSet(&statdata, 0, sizeof(statdata));
+
+ if (!jsonPathStatsExtractData(pstats, type, nullfrac, &statdata))
+ return NULL;
+
+ return stats_form_tuple(&statdata);
+}
+
+/*
+ * jsonStatsGetPathStatsTuple
+ * ???
+ */
+static HeapTuple
+jsonStatsGetPathStatsTuple(JsonStats jsdata, JsonStatType type,
+ Datum *path, int pathlen)
+{
+ float4 nullfrac;
+ JsonPathStats pstats = jsonStatsGetPathStats(jsdata, path, pathlen,
+ &nullfrac);
+
+ return jsonPathStatsFormTuple(pstats, type, nullfrac);
+}
+
+/*
+ * jsonStatsGetPathFreq
+ * Return frequency of a path (fraction of documents containing it).
+ */
+static float4
+jsonStatsGetPathFreq(JsonStats jsdata, Datum *path, int pathlen)
+{
+ float4 nullfrac;
+ JsonPathStats pstats = jsonStatsGetPathStats(jsdata, path, pathlen,
+ &nullfrac);
+ float4 freq = (1.0 - nullfrac) * jsonPathStatsGetFreq(pstats, 0.0);
+
+ CLAMP_PROBABILITY(freq);
+ return freq;
+}
+
+/*
+ * jsonbStatsVarOpConst
+ * Prepare optimizer statistics for a given operator, from JSON stats.
+ *
+ * This handles only OpExpr expressions, with variable and a constant. We get
+ * the constant as is, and the variable is represented by statistics fetched
+ * by get_restriction_variable().
+ *
+ * opid - OID of the operator (input parameter)
+ * resdata - pointer to calculated statistics for result of operator
+ * vardata - statistics for the restriction variable
+ * cnst - constant from the operator expression
+ *
+ * Returns true when useful optimizer statistics have been calculated.
+ */
+static bool
+jsonbStatsVarOpConst(Oid opid, VariableStatData *resdata,
+ const VariableStatData *vardata, Const *cnst)
+{
+ JsonStatData jsdata;
+ JsonStatType statype = JsonStatJsonb;
+
+ if (!jsonStatsInit(&jsdata, vardata))
+ return false;
+
+ switch (opid)
+ {
+ case JsonbObjectFieldTextOperator:
+ statype = JsonStatText;
+ /* fall through */
+ case JsonbObjectFieldOperator:
+ {
+ if (cnst->consttype != TEXTOID)
+ {
+ jsonStatsRelease(&jsdata);
+ return false;
+ }
+
+ resdata->statsTuple =
+ jsonStatsGetPathStatsTuple(&jsdata, statype,
+ &cnst->constvalue, 1);
+ break;
+ }
+
+ case JsonbArrayElementTextOperator:
+ statype = JsonStatText;
+ /* fall through */
+ case JsonbArrayElementOperator:
+ {
+ if (cnst->consttype != INT4OID)
+ {
+ jsonStatsRelease(&jsdata);
+ return false;
+ }
+
+ resdata->statsTuple =
+ jsonStatsGetPathStatsTuple(&jsdata, statype, NULL,
+ -1 - DatumGetInt32(cnst->constvalue));
+ break;
+ }
+
+ case JsonbExtractPathTextOperator:
+ statype = JsonStatText;
+ /* fall through */
+ case JsonbExtractPathOperator:
+ {
+ Datum *path;
+ bool *nulls;
+ int pathlen;
+ int i;
+
+ if (cnst->consttype != TEXTARRAYOID)
+ {
+ jsonStatsRelease(&jsdata);
+ return false;
+ }
+
+ deconstruct_array(DatumGetArrayTypeP(cnst->constvalue), TEXTOID,
+ -1, false, 'i', &path, &nulls, &pathlen);
+
+ for (i = 0; i < pathlen; i++)
+ {
+ if (nulls[i])
+ {
+ pfree(path);
+ pfree(nulls);
+ PG_RETURN_VOID();
+ }
+ }
+
+ resdata->statsTuple =
+ jsonStatsGetPathStatsTuple(&jsdata, statype, path, pathlen);
+
+ pfree(path);
+ pfree(nulls);
+ break;
+ }
+
+ default:
+ jsonStatsRelease(&jsdata);
+ return false;
+ }
+
+ if (!resdata->statsTuple)
+ resdata->statsTuple = stats_form_tuple(NULL); /* form all-NULL tuple */
+
+ resdata->acl_ok = vardata->acl_ok;
+ resdata->freefunc = heap_freetuple;
+ Assert(resdata->rel == vardata->rel);
+ Assert(resdata->atttype ==
+ (statype == JsonStatJsonb ? JSONBOID :
+ statype == JsonStatText ? TEXTOID :
+ /* statype == JsonStatFreq */ BOOLOID));
+
+ jsonStatsRelease(&jsdata);
+ return true;
+}
+
+/*
+ * jsonb_stats
+ * Statistics estimation procedure for JSONB data type.
+ *
+ * This only supports OpExpr expressions, with (Var op Const) shape.
+ *
+ * XXX It might be useful to allow recursion, i.e. get_restriction_variable
+ * might derive statistics too. I don't think it does that now, right?
+ */
+Datum
+jsonb_stats(PG_FUNCTION_ARGS)
+{
+ PlannerInfo *root = (PlannerInfo *) PG_GETARG_POINTER(0);
+ OpExpr *opexpr = (OpExpr *) PG_GETARG_POINTER(1);
+ int varRelid = PG_GETARG_INT32(2);
+ VariableStatData *resdata = (VariableStatData *) PG_GETARG_POINTER(3);
+ VariableStatData vardata;
+ Node *constexpr;
+ bool result;
+ bool varonleft;
+
+ /* should only be called for OpExpr expressions */
+ Assert(IsA(opexpr, OpExpr));
+
+ /* Is the expression simple enough? (Var op Const) or similar? */
+ if (!get_restriction_variable(root, opexpr->args, varRelid,
+ &vardata, &constexpr, &varonleft))
+ return false;
+
+ /* XXX Could we also get varonleft=false in useful cases? */
+ result = IsA(constexpr, Const) && varonleft &&
+ jsonbStatsVarOpConst(opexpr->opno, resdata, &vardata,
+ (Const *) constexpr);
+
+ ReleaseVariableStats(vardata);
+
+ return result;
+}
+
+/*
+ * jsonSelectivity
+ * Use JSON statistics to estimate selectivity for (in)equalities.
+ *
+ * The statistics is represented as (arrays of) JSON values etc. so we
+ * need to pass the right operators to the functions.
+ */
+Selectivity
+jsonSelectivity(JsonPathStats stats, Datum scalar, Oid operator)
+{
+ VariableStatData vardata;
+ Selectivity sel;
+
+ if (!stats)
+ return 0.0;
+
+ vardata.atttype = JSONBOID;
+ vardata.atttypmod = -1;
+ vardata.isunique = false;
+ vardata.rel = stats->data->rel;
+ vardata.var = NULL;
+ vardata.vartype = JSONBOID;
+ vardata.acl_ok = stats->data->acl_ok;
+ vardata.statsTuple = jsonPathStatsFormTuple(stats,
+ JsonStatJsonbWithoutSubpaths, 0.0);
+
+ if (operator == JsonbEqOperator)
+ sel = var_eq_const(&vardata, operator, InvalidOid, scalar, false, true, false);
+ else
+ sel = scalarineqsel(NULL, operator,
+ operator == JsonbGtOperator ||
+ operator == JsonbGeOperator,
+ operator == JsonbLeOperator ||
+ operator == JsonbGeOperator,
+ InvalidOid,
+ &vardata, scalar, JSONBOID);
+
+ if (vardata.statsTuple)
+ heap_freetuple(vardata.statsTuple);
+
+ return sel;
+}
+
+/*
+ * jsonSelectivityContains
+ * Estimate selectivity for containment operator on JSON.
+ *
+ * XXX This really needs more comments explaining the logic.
+ */
+static Selectivity
+jsonSelectivityContains(JsonStats stats, Jsonb *jb)
+{
+ JsonbValue v;
+ JsonbIterator *it;
+ JsonbIteratorToken r;
+ StringInfoData pathstr;
+ struct Path
+ {
+ struct Path *parent;
+ int len;
+ JsonPathStats stats;
+ Selectivity freq;
+ Selectivity sel;
+ } root,
+ *path = &root;
+ Selectivity scalarSel = 0.0;
+ Selectivity sel;
+ bool rawScalar = false;
+
+ initStringInfo(&pathstr);
+
+ appendStringInfo(&pathstr, "$");
+
+ root.parent = NULL;
+ root.len = pathstr.len;
+ root.stats = jsonStatsGetPathStatsStr(stats, pathstr.data, pathstr.len);
+ root.freq = jsonPathStatsGetFreq(root.stats, 0.0);
+ root.sel = 1.0;
+
+ if (root.freq <= 0.0)
+ return 0.0;
+
+ it = JsonbIteratorInit(&jb->root);
+
+ while ((r = JsonbIteratorNext(&it, &v, false)) != WJB_DONE)
+ {
+ switch (r)
+ {
+ case WJB_BEGIN_OBJECT:
+ {
+ struct Path *p = palloc(sizeof(*p));
+
+ p->len = pathstr.len;
+ p->parent = path;
+ p->stats = NULL;
+ p->freq = jsonPathStatsGetTypeFreq(path->stats, jbvObject, 0.0);
+ if (p->freq <= 0.0)
+ return 0.0;
+ p->sel = 1.0;
+ path = p;
+ break;
+ }
+
+ case WJB_BEGIN_ARRAY:
+ {
+ struct Path *p = palloc(sizeof(*p));
+
+ rawScalar = v.val.array.rawScalar;
+
+ appendStringInfo(&pathstr, ".#");
+ p->len = pathstr.len;
+ p->parent = path;
+ p->stats = jsonStatsGetPathStatsStr(stats, pathstr.data,
+ pathstr.len);
+ p->freq = jsonPathStatsGetFreq(p->stats, 0.0);
+ if (p->freq <= 0.0 && !rawScalar)
+ return 0.0;
+ p->sel = 1.0;
+ path = p;
+
+ break;
+ }
+
+ case WJB_END_OBJECT:
+ case WJB_END_ARRAY:
+ {
+ struct Path *p = path;
+
+ path = path->parent;
+ sel = p->sel * p->freq / path->freq;
+ pfree(p);
+ pathstr.data[pathstr.len = path->len] = '\0';
+ if (pathstr.data[pathstr.len - 1] == '#')
+ sel = 1.0 - pow(1.0 - sel,
+ jsonPathStatsGetAvgArraySize(path->stats));
+ path->sel *= sel;
+ break;
+ }
+
+ case WJB_KEY:
+ {
+ pathstr.data[pathstr.len = path->parent->len] = '\0';
+ jsonPathAppendEntryWithLen(&pathstr, v.val.string.val,
+ v.val.string.len);
+ path->len = pathstr.len;
+ break;
+ }
+
+ case WJB_VALUE:
+ case WJB_ELEM:
+ {
+ JsonPathStats pstats = r == WJB_ELEM ? path->stats :
+ jsonStatsGetPathStatsStr(stats, pathstr.data, pathstr.len);
+ Datum scalar = JsonbPGetDatum(JsonbValueToJsonb(&v));
+
+ if (path->freq <= 0.0)
+ sel = 0.0;
+ else
+ {
+ sel = jsonSelectivity(pstats, scalar, JsonbEqOperator);
+ sel /= path->freq;
+ if (pathstr.data[pathstr.len - 1] == '#')
+ sel = 1.0 - pow(1.0 - sel,
+ jsonPathStatsGetAvgArraySize(path->stats));
+ }
+
+ path->sel *= sel;
+
+ if (r == WJB_ELEM && path->parent == &root && rawScalar)
+ scalarSel = jsonSelectivity(root.stats, scalar,
+ JsonbEqOperator);
+ break;
+ }
+
+ default:
+ break;
+ }
+ }
+
+ sel = scalarSel + root.sel * root.freq;
+ CLAMP_PROBABILITY(sel);
+ return sel;
+}
+
+/*
+ * jsonSelectivityExists
+ * Estimate selectivity for JSON "exists" operator.
+ */
+static Selectivity
+jsonSelectivityExists(JsonStats stats, Datum key)
+{
+ JsonPathStats arrstats;
+ JsonbValue jbvkey;
+ Datum jbkey;
+ Selectivity keysel;
+ Selectivity scalarsel;
+ Selectivity arraysel;
+ Selectivity sel;
+
+ JsonValueInitStringWithLen(&jbvkey,
+ VARDATA_ANY(key), VARSIZE_ANY_EXHDR(key));
+
+ jbkey = JsonbPGetDatum(JsonbValueToJsonb(&jbvkey));
+
+ keysel = jsonStatsGetPathFreq(stats, &key, 1);
+
+ scalarsel = jsonSelectivity(jsonStatsGetPathStatsStr(stats, "$", 1),
+ jbkey, JsonbEqOperator);
+
+ arrstats = jsonStatsGetPathStatsStr(stats, "$.#", 3);
+ arraysel = jsonSelectivity(arrstats, jbkey, JsonbEqOperator);
+ arraysel = 1.0 - pow(1.0 - arraysel,
+ jsonPathStatsGetAvgArraySize(arrstats));
+
+ sel = keysel + scalarsel + arraysel;
+ CLAMP_PROBABILITY(sel);
+ return sel;
+}
+
+/*
+ * jsonb_sel
+ * The main procedure estimating selectivity for all JSONB operators.
+ */
+Datum
+jsonb_sel(PG_FUNCTION_ARGS)
+{
+ PlannerInfo *root = (PlannerInfo *) PG_GETARG_POINTER(0);
+ Oid operator = PG_GETARG_OID(1);
+ List *args = (List *) PG_GETARG_POINTER(2);
+ int varRelid = PG_GETARG_INT32(3);
+ double sel = DEFAULT_JSON_CONTAINS_SEL;
+ Node *other;
+ Const *cnst;
+ bool varonleft;
+ JsonStatData stats;
+ VariableStatData vardata;
+
+ if (!get_restriction_variable(root, args, varRelid,
+ &vardata, &other, &varonleft))
+ PG_RETURN_FLOAT8(sel);
+
+ if (!IsA(other, Const))
+ goto out;
+
+ cnst = (Const *) other;
+
+ if (cnst->constisnull)
+ {
+ sel = 0.0;
+ goto out;
+ }
+
+ if (!jsonStatsInit(&stats, &vardata))
+ goto out;
+
+ switch (operator)
+ {
+ case JsonbExistsOperator:
+ if (!varonleft || cnst->consttype != TEXTOID)
+ goto out;
+
+ sel = jsonSelectivityExists(&stats, cnst->constvalue);
+ break;
+
+ case JsonbExistsAnyOperator:
+ case JsonbExistsAllOperator:
+ {
+ Datum *keys;
+ bool *nulls;
+ Selectivity freq = 1.0;
+ int nkeys;
+ int i;
+ bool all = operator == JsonbExistsAllOperator;
+
+ if (!varonleft || cnst->consttype != TEXTARRAYOID)
+ goto out;
+
+ deconstruct_array(DatumGetArrayTypeP(cnst->constvalue), TEXTOID,
+ -1, false, 'i', &keys, &nulls, &nkeys);
+
+ for (i = 0; i < nkeys; i++)
+ if (!nulls[i])
+ {
+ Selectivity pathfreq = jsonSelectivityExists(&stats,
+ keys[i]);
+ freq *= all ? pathfreq : (1.0 - pathfreq);
+ }
+
+ pfree(keys);
+ pfree(nulls);
+
+ if (!all)
+ freq = 1.0 - freq;
+
+ sel = freq;
+ break;
+ }
+
+ case JsonbContainedOperator:
+ /* TODO */
+ break;
+
+ case JsonbContainsOperator:
+ {
+ if (cnst->consttype != JSONBOID)
+ goto out;
+
+ sel = jsonSelectivityContains(&stats,
+ DatumGetJsonbP(cnst->constvalue));
+ break;
+ }
+ }
+
+out:
+ jsonStatsRelease(&stats);
+ ReleaseVariableStats(vardata);
+
+ PG_RETURN_FLOAT8((float8) sel);
+}
diff --git a/src/backend/utils/adt/jsonb_typanalyze.c b/src/backend/utils/adt/jsonb_typanalyze.c
new file mode 100644
index 00000000000..d2fbfbf42db
--- /dev/null
+++ b/src/backend/utils/adt/jsonb_typanalyze.c
@@ -0,0 +1,1379 @@
+/*-------------------------------------------------------------------------
+ *
+ * jsonb_typanalyze.c
+ * Functions for gathering statistics from jsonb columns
+ *
+ * Copyright (c) 2016, PostgreSQL Global Development Group
+ *
+ * Functions in this module are used to analyze contents of JSONB columns
+ * and build optimizer statistics. In principle we extract paths from all
+ * sampled documents and calculate the usual statistics (MCV, histogram)
+ * for each path - in principle each path is treated as a column.
+ *
+ * Because we're not enforcing any JSON schema, the documents may differ
+ * a lot - the documents may contain large number of different keys, the
+ * types of values may be entirely different, etc. This makes it more
+ * challenging than building stats for regular columns. For example not
+ * only do we need to decide which values to keep in the MCV, but also
+ * which paths to keep (in case the documents are so variable we can't
+ * keep all paths).
+ *
+ * The statistics is stored in pg_statistic, in a slot with a new stakind
+ * value (STATISTIC_KIND_JSON). The statistics is serialized as an array
+ * of JSONB values, eash element storing statistics for one path.
+ *
+ * For each path, we store the following keys:
+ *
+ * - path - path this stats is for, serialized as jsonpath
+ * - freq - frequency of documents containing this path
+ * - json - the regular per-column stats (MCV, histogram, ...)
+ * - freq_null - frequency of JSON null values
+ * - freq_array - frequency of JSON array values
+ * - freq_object - frequency of JSON object values
+ * - freq_string - frequency of JSON string values
+ * - freq_numeric - frequency of JSON numeric values
+ *
+ * This is stored in the stavalues array.
+ *
+ * The per-column stats (stored in the "json" key) have additional internal
+ * structure, to allow storing multiple stakind types (histogram, mcv). See
+ * jsonAnalyzeMakeScalarStats for details.
+ *
+ *
+ * XXX There's additional stuff (prefix, length stats) stored in the first
+ * two elements, I think.
+ *
+ * XXX It's a bit weird the "regular" stats are stored in the "json" key,
+ * while the JSON stats (frequencies of different JSON types) are right
+ * at the top level.
+ *
+ *
+ * IDENTIFICATION
+ * src/backend/utils/adt/jsonb_typanalyze.c
+ *
+ *-------------------------------------------------------------------------
+ */
+
+#include "postgres.h"
+
+#include "fmgr.h"
+#include "access/hash.h"
+#include "access/detoast.h"
+#include "catalog/pg_attribute.h"
+#include "catalog/pg_operator.h"
+#include "catalog/pg_type.h"
+#include "commands/vacuum.h"
+#include "utils/builtins.h"
+#include "utils/guc.h"
+#include "utils/hsearch.h"
+#include "utils/json.h"
+#include "utils/jsonb.h"
+#include "utils/json_selfuncs.h"
+#include "utils/lsyscache.h"
+#include "utils/memutils.h"
+
+typedef struct JsonPathEntry JsonPathEntry;
+
+/*
+ * Element of a path in the JSON document (i.e. not jsonpath). Elements
+ * are linked together to build longer paths.
+ *
+ * XXX We need entry+lenth because JSON path elements may contain null
+ * bytes, I guess?
+ */
+struct JsonPathEntry
+{
+ JsonPathEntry *parent;
+ const char *entry; /* element of the path as a string */
+ int len; /* length of entry string (may be 0) */
+ uint32 hash; /* hash of the whole path (with parent) */
+};
+
+/*
+ * A path is simply a pointer to the last element (we can traverse to
+ * the top easily).
+ */
+typedef JsonPathEntry *JsonPath;
+
+/* An array containing a dynamic number of JSON values. */
+typedef struct JsonValues
+{
+ Datum *buf;
+ int count;
+ int allocated;
+} JsonValues;
+
+/*
+ * Scalar statistics built for an array of values, extracted from a JSON
+ * document (for one particular path).
+ *
+ * XXX The array can contain values of different JSON type, probably?
+ */
+typedef struct JsonScalarStats
+{
+ JsonValues values;
+ VacAttrStats stats;
+} JsonScalarStats;
+
+/*
+ * Statistics calculated for a set of values.
+ *
+ *
+ * XXX This seems rather complicated and needs simplification. We're not
+ * really using all the various JsonScalarStats bits, there's a lot of
+ * duplication (e.g. each JsonScalarStats contains it's own array, which
+ * has a copy of data from the one in "jsons"). Some of it is defined as
+ * a typedef, but booleans have inline struct.
+ */
+typedef struct JsonValueStats
+{
+ JsonScalarStats jsons; /* stats for all JSON types together */
+
+ /* XXX used only with JSON_ANALYZE_SCALARS defined */
+ JsonScalarStats strings; /* stats for JSON strings */
+ JsonScalarStats numerics; /* stats for JSON numerics */
+
+ /* stats for booleans */
+ struct
+ {
+ int ntrue;
+ int nfalse;
+ } booleans;
+
+ int nnulls; /* number of JSON null values */
+ int nobjects; /* number of JSON objects */
+ int narrays; /* number of JSON arrays */
+
+ JsonScalarStats lens; /* stats of object lengths */
+ JsonScalarStats arrlens; /* stats of array lengths */
+} JsonValueStats;
+
+/* ??? */
+typedef struct JsonPathAnlStats
+{
+ JsonPathEntry path;
+ JsonValueStats vstats;
+ Jsonb *stats;
+ char *pathstr;
+ double freq;
+ int depth;
+
+ JsonPathEntry **entries;
+ int nentries;
+} JsonPathAnlStats;
+
+/* various bits needed while analyzing JSON */
+typedef struct JsonAnalyzeContext
+{
+ VacAttrStats *stats;
+ MemoryContext mcxt;
+ AnalyzeAttrFetchFunc fetchfunc;
+ HTAB *pathshash;
+ JsonPathAnlStats *root;
+ JsonPathAnlStats **paths;
+ int npaths;
+ double totalrows;
+ double total_width;
+ int samplerows;
+ int target;
+ int null_cnt;
+ int analyzed_cnt;
+ int maxdepth;
+ bool scalarsOnly;
+} JsonAnalyzeContext;
+
+/*
+ * JsonPathMatch
+ * Determine when two JSON paths (list of JsonPathEntry) match.
+ *
+ * XXX Sould be JsonPathEntryMatch as it deals with JsonPathEntry nodes
+ * not whole paths, no?
+ *
+ * XXX Seems a bit silly to return int, when the return statement only
+ * really returns bool (because of how it compares paths). It's not really
+ * a comparator for sorting, for example.
+ */
+static int
+JsonPathMatch(const void *key1, const void *key2, Size keysize)
+{
+ const JsonPathEntry *path1 = key1;
+ const JsonPathEntry *path2 = key2;
+
+ return path1->parent != path2->parent ||
+ path1->len != path2->len ||
+ (path1->len > 0 &&
+ strncmp(path1->entry, path2->entry, path1->len));
+}
+
+/*
+ * JsonPathHash
+ * Calculate hash of the path entry.
+ *
+ * XXX Again, maybe JsonPathEntryHash would be a better name?
+ *
+ * XXX Maybe should call JsonPathHash on the parent, instead of looking
+ * at the field directly. Could easily happen we have not calculated it
+ * yet, I guess.
+ */
+static uint32
+JsonPathHash(const void *key, Size keysize)
+{
+ const JsonPathEntry *path = key;
+
+ /* XXX Call JsonPathHash instead of direct access? */
+ uint32 hash = path->parent ? path->parent->hash : 0;
+
+ hash = (hash << 1) | (hash >> 31);
+ hash ^= path->len < 0 ? 0 : DatumGetUInt32(
+ hash_any((const unsigned char *) path->entry, path->len));
+
+ return hash;
+}
+
+/*
+ * jsonAnalyzeAddPath
+ * Add an entry for a JSON path to the working list of statistics.
+ *
+ * Returns a pointer to JsonPathAnlStats (which might have already existed
+ * if the path was in earlier document), which can then be populated or
+ * updated.
+ */
+static inline JsonPathAnlStats *
+jsonAnalyzeAddPath(JsonAnalyzeContext *ctx, JsonPath path)
+{
+ JsonPathAnlStats *stats;
+ bool found;
+
+ path->hash = JsonPathHash(path, 0);
+
+ /* XXX See if we already saw this path earlier. */
+ stats = hash_search_with_hash_value(ctx->pathshash, path, path->hash,
+ HASH_ENTER, &found);
+
+ /*
+ * Nope, it's the first time we see this path, so initialize all the
+ * fields (path string, counters, ...).
+ */
+ if (!found)
+ {
+ JsonPathAnlStats *parent = (JsonPathAnlStats *) stats->path.parent;
+ const char *ppath = parent->pathstr;
+
+ path = &stats->path;
+
+ /* Is it valid path? If not, we treat it as $.# */
+ if (path->len >= 0)
+ {
+ StringInfoData si;
+ MemoryContext oldcxt = MemoryContextSwitchTo(ctx->mcxt);
+
+ initStringInfo(&si);
+
+ path->entry = pnstrdup(path->entry, path->len);
+
+ appendStringInfo(&si, "%s.", ppath);
+ escape_json(&si, path->entry);
+
+ stats->pathstr = si.data;
+
+ MemoryContextSwitchTo(oldcxt);
+ }
+ else
+ {
+ int pathstrlen = strlen(ppath) + 3;
+ stats->pathstr = MemoryContextAlloc(ctx->mcxt, pathstrlen);
+ snprintf(stats->pathstr, pathstrlen, "%s.#", ppath);
+ }
+
+ /* initialize the stats counter for this path entry */
+ memset(&stats->vstats, 0, sizeof(JsonValueStats));
+ stats->stats = NULL;
+ stats->freq = 0.0;
+ stats->depth = parent->depth + 1;
+ stats->entries = NULL;
+ stats->nentries = 0;
+
+ /* XXX Seems strange. Should we even add the path in this case? */
+ if (stats->depth > ctx->maxdepth)
+ ctx->maxdepth = stats->depth;
+ }
+
+ return stats;
+}
+
+/*
+ * JsonValuesAppend
+ * Add a JSON value to the dynamic array (enlarge it if needed).
+ *
+ * XXX This is likely one of the problems - the documents may be pretty
+ * large, with a lot of different values for each path. At that point
+ * it's problematic to keep all of that in memory at once. So maybe we
+ * need to introduce some sort of compaction (e.g. we could try
+ * deduplicating the values), limit on size of the array or something.
+ */
+static inline void
+JsonValuesAppend(JsonValues *values, Datum value, int initialSize)
+{
+ if (values->count >= values->allocated)
+ {
+ if (values->allocated)
+ {
+ values->allocated = values->allocated * 2;
+ values->buf = repalloc(values->buf,
+ sizeof(values->buf[0]) * values->allocated);
+ }
+ else
+ {
+ values->allocated = initialSize;
+ values->buf = palloc(sizeof(values->buf[0]) * values->allocated);
+ }
+ }
+
+ values->buf[values->count++] = value;
+}
+
+/*
+ * jsonAnalyzeJsonValue
+ * Process a value extracted from the document (for a given path).
+ */
+static inline void
+jsonAnalyzeJsonValue(JsonAnalyzeContext *ctx, JsonValueStats *vstats,
+ JsonbValue *jv)
+{
+ JsonScalarStats *sstats;
+ JsonbValue *jbv;
+ JsonbValue jbvtmp;
+ Datum value;
+
+ /* ??? */
+ if (ctx->scalarsOnly && jv->type == jbvBinary)
+ {
+ if (JsonContainerIsObject(jv->val.binary.data))
+ jbv = JsonValueInitObject(&jbvtmp, 0, 0);
+ else
+ {
+ Assert(JsonContainerIsArray(jv->val.binary.data));
+ jbv = JsonValueInitArray(&jbvtmp, 0, 0, false);
+ }
+ }
+ else
+ jbv = jv;
+
+ /* always add it to the "global" JSON stats, shared by all types */
+ JsonValuesAppend(&vstats->jsons.values,
+ JsonbPGetDatum(JsonbValueToJsonb(jbv)),
+ ctx->target);
+
+ /*
+ * Maybe also update the type-specific counters.
+ *
+ * XXX The mix of break/return statements in this block is really
+ * confusing.
+ */
+ switch (jv->type)
+ {
+ case jbvNull:
+ ++vstats->nnulls;
+ return;
+
+ case jbvBool:
+ ++*(jv->val.boolean ? &vstats->booleans.ntrue
+ : &vstats->booleans.nfalse);
+ return;
+
+ case jbvString:
+#ifdef JSON_ANALYZE_SCALARS
+ sstats = &vstats->strings;
+ value = PointerGetDatum(
+ cstring_to_text_with_len(jv->val.string.val,
+ jv->val.string.len));
+ break;
+#else
+ return;
+#endif
+
+ case jbvNumeric:
+#ifdef JSON_ANALYZE_SCALARS
+ sstats = &vstats->numerics;
+ value = PointerGetDatum(jv->val.numeric);
+ break;
+#else
+ return;
+#endif
+
+ case jbvBinary:
+ if (JsonContainerIsObject(jv->val.binary.data))
+ {
+ uint32 size = JsonContainerSize(jv->val.binary.data);
+ value = DatumGetInt32(size);
+ sstats = &vstats->lens;
+ vstats->nobjects++;
+ break;
+ }
+ else if (JsonContainerIsArray(jv->val.binary.data))
+ {
+ uint32 size = JsonContainerSize(jv->val.binary.data);
+ value = DatumGetInt32(size);
+ sstats = &vstats->lens;
+ vstats->narrays++;
+ JsonValuesAppend(&vstats->arrlens.values, value, ctx->target);
+ break;
+ }
+ return;
+
+ default:
+ elog(ERROR, "invalid scalar json value type %d", jv->type);
+ break;
+ }
+
+ JsonValuesAppend(&sstats->values, value, ctx->target);
+}
+
+/*
+ * jsonAnalyzeJson
+ * Parse the JSON document and build/update stats.
+ *
+ * XXX The name seems a bit weird, with the two json bits.
+ *
+ * XXX The param is either NULL, (char *) -1, or a pointer
+ */
+static void
+jsonAnalyzeJson(JsonAnalyzeContext *ctx, Jsonb *jb, void *param)
+{
+ JsonbValue jv;
+ JsonbIterator *it;
+ JsonbIteratorToken tok;
+ JsonPathAnlStats *target = (JsonPathAnlStats *) param;
+ JsonPathAnlStats *stats = ctx->root;
+ JsonPath path = &stats->path;
+ JsonPathEntry entry;
+ bool scalar = false;
+
+ if ((!target || target == stats) &&
+ !JB_ROOT_IS_SCALAR(jb))
+ jsonAnalyzeJsonValue(ctx, &stats->vstats, JsonValueInitBinary(&jv, jb));
+
+ it = JsonbIteratorInit(&jb->root);
+
+ while ((tok = JsonbIteratorNext(&it, &jv, true)) != WJB_DONE)
+ {
+ switch (tok)
+ {
+ case WJB_BEGIN_OBJECT:
+ entry.entry = NULL;
+ entry.len = -1;
+ entry.parent = path;
+ path = &entry;
+
+ break;
+
+ case WJB_END_OBJECT:
+ stats = (JsonPathAnlStats *)(path = path->parent);
+ break;
+
+ case WJB_BEGIN_ARRAY:
+ if (!(scalar = jv.val.array.rawScalar))
+ {
+ entry.entry = NULL;
+ entry.len = -1;
+ entry.parent = path;
+ path = &(stats = jsonAnalyzeAddPath(ctx, &entry))->path;
+ }
+ break;
+
+ case WJB_END_ARRAY:
+ if (!scalar)
+ stats = (JsonPathAnlStats *)(path = path->parent);
+ break;
+
+ case WJB_KEY:
+ entry.entry = jv.val.string.val;
+ entry.len = jv.val.string.len;
+ entry.parent = path->parent;
+ path = &(stats = jsonAnalyzeAddPath(ctx, &entry))->path;
+ break;
+
+ case WJB_VALUE:
+ case WJB_ELEM:
+ if (!target || target == stats)
+ jsonAnalyzeJsonValue(ctx, &stats->vstats, &jv);
+
+ /* XXX not sure why we're doing this? */
+ if (jv.type == jbvBinary)
+ {
+ /* recurse into container */
+ JsonbIterator *it2 = JsonbIteratorInit(jv.val.binary.data);
+
+ it2->parent = it;
+ it = it2;
+ }
+ break;
+
+ default:
+ break;
+ }
+ }
+}
+
+/*
+ * jsonAnalyzeJsonSubpath
+ * ???
+ */
+static void
+jsonAnalyzeJsonSubpath(JsonAnalyzeContext *ctx, JsonPathAnlStats *pstats,
+ JsonbValue *jbv, int n)
+{
+ JsonbValue scalar;
+ int i;
+
+ for (i = n; i < pstats->depth; i++)
+ {
+ JsonPathEntry *entry = pstats->entries[i];
+ JsonbContainer *jbc = jbv->val.binary.data;
+ JsonbValueType type = jbv->type;
+
+ if (i > n)
+ pfree(jbv);
+
+ if (type != jbvBinary)
+ return;
+
+ if (entry->len == -1)
+ {
+ JsonbIterator *it;
+ JsonbIteratorToken r;
+ JsonbValue elem;
+
+ if (!JsonContainerIsArray(jbc) || JsonContainerIsScalar(jbc))
+ return;
+
+ it = JsonbIteratorInit(jbc);
+
+ while ((r = JsonbIteratorNext(&it, &elem, true)) != WJB_DONE)
+ {
+ if (r == WJB_ELEM)
+ jsonAnalyzeJsonSubpath(ctx, pstats, &elem, i + 1);
+ }
+
+ return;
+ }
+ else
+ {
+ if (!JsonContainerIsObject(jbc))
+ return;
+
+ jbv = findJsonbValueFromContainerLen(jbc, JB_FOBJECT,
+ entry->entry, entry->len);
+
+ if (!jbv)
+ return;
+ }
+ }
+
+ if (i == n &&
+ jbv->type == jbvBinary &&
+ JsonbExtractScalar(jbv->val.binary.data, &scalar))
+ jbv = &scalar;
+
+ jsonAnalyzeJsonValue(ctx, &pstats->vstats, jbv);
+
+ if (i > n)
+ pfree(jbv);
+}
+
+/*
+ * jsonAnalyzeJsonPath
+ * ???
+ */
+static void
+jsonAnalyzeJsonPath(JsonAnalyzeContext *ctx, Jsonb *jb, void *param)
+{
+ JsonPathAnlStats *pstats = (JsonPathAnlStats *) param;
+ JsonbValue jbvtmp;
+ JsonbValue *jbv = JsonValueInitBinary(&jbvtmp, jb);
+ JsonPath path;
+
+ if (!pstats->entries)
+ {
+ int i;
+
+ pstats->entries = MemoryContextAlloc(ctx->mcxt,
+ sizeof(*pstats->entries) * pstats->depth);
+
+ for (path = &pstats->path, i = pstats->depth - 1;
+ path->parent && i >= 0;
+ path = path->parent, i--)
+ pstats->entries[i] = path;
+ }
+
+ jsonAnalyzeJsonSubpath(ctx, pstats, jbv, 0);
+}
+
+static Datum
+jsonAnalyzePathFetch(VacAttrStatsP stats, int rownum, bool *isnull)
+{
+ *isnull = false;
+ return stats->exprvals[rownum];
+}
+
+/*
+ * jsonAnalyzePathValues
+ * Calculate per-column statistics for values for a single path.
+ *
+ * We have already accumulated all the values for the path, so we simply
+ * call the typanalyze function for the proper data type, and then
+ * compute_stats (which points to compute_scalar_stats or so).
+ */
+static void
+jsonAnalyzePathValues(JsonAnalyzeContext *ctx, JsonScalarStats *sstats,
+ Oid typid, double freq)
+{
+ JsonValues *values = &sstats->values;
+ VacAttrStats *stats = &sstats->stats;
+ FormData_pg_attribute attr;
+ FormData_pg_type type;
+ int i;
+
+ if (!sstats->values.count)
+ return;
+
+ get_typlenbyvalalign(typid, &type.typlen, &type.typbyval, &type.typalign);
+
+ attr.attstattarget = ctx->target;
+
+ stats->attr = &attr;
+ stats->attrtypid = typid;
+ stats->attrtypmod = -1;
+ stats->attrtype = &type;
+ stats->anl_context = ctx->stats->anl_context;
+
+ stats->exprvals = values->buf;
+
+ /* XXX Do we need to initialize all slots? */
+ for (i = 0; i < STATISTIC_NUM_SLOTS; i++)
+ {
+ stats->statypid[i] = stats->attrtypid;
+ stats->statyplen[i] = stats->attrtype->typlen;
+ stats->statypbyval[i] = stats->attrtype->typbyval;
+ stats->statypalign[i] = stats->attrtype->typalign;
+ }
+
+ std_typanalyze(stats);
+
+ stats->compute_stats(stats, jsonAnalyzePathFetch,
+ values->count,
+ ctx->totalrows / ctx->samplerows * values->count);
+
+ /*
+ * We've only kept the non-null values, so compute_stats will always
+ * leave this as 1.0. But we have enough info to calculate the correct
+ * value.
+ */
+ stats->stanullfrac = (float4)(1.0 - freq);
+
+ /*
+ * Similarly, we need to correct the MCV frequencies, becuse those are
+ * also calculated only from the non-null values. All we need to do is
+ * simply multiply that with the non-NULL frequency.
+ */
+ for (i = 0; i < STATISTIC_NUM_SLOTS; i++)
+ {
+ if (stats->stakind[i] == STATISTIC_KIND_MCV)
+ {
+ int j;
+ for (j = 0; j < stats->numnumbers[i]; j++)
+ stats->stanumbers[i][j] *= freq;
+ }
+ }
+}
+
+/*
+ * jsonAnalyzeMakeScalarStats
+ * Serialize scalar stats into a JSON representation.
+ *
+ * We simply produce a JSON document with a list of predefined keys:
+ *
+ * - nullfrac
+ * - distinct
+ * - width
+ * - correlation
+ * - mcv or histogram
+ *
+ * For the mcv / histogram, we store a nested values / numbers.
+ */
+static JsonbValue *
+jsonAnalyzeMakeScalarStats(JsonbParseState **ps, const char *name,
+ const VacAttrStats *stats)
+{
+ JsonbValue val;
+ int i;
+ int j;
+
+ pushJsonbKey(ps, &val, name);
+
+ pushJsonbValue(ps, WJB_BEGIN_OBJECT, NULL);
+
+ pushJsonbKeyValueFloat(ps, &val, "nullfrac", stats->stanullfrac);
+ pushJsonbKeyValueFloat(ps, &val, "distinct", stats->stadistinct);
+ pushJsonbKeyValueInteger(ps, &val, "width", stats->stawidth);
+
+ for (i = 0; i < STATISTIC_NUM_SLOTS; i++)
+ {
+ if (!stats->stakind[i])
+ break;
+
+ switch (stats->stakind[i])
+ {
+ case STATISTIC_KIND_MCV:
+ pushJsonbKey(ps, &val, "mcv");
+ break;
+
+ case STATISTIC_KIND_HISTOGRAM:
+ pushJsonbKey(ps, &val, "histogram");
+ break;
+
+ case STATISTIC_KIND_CORRELATION:
+ pushJsonbKeyValueFloat(ps, &val, "correlation",
+ stats->stanumbers[i][0]);
+ continue;
+
+ default:
+ elog(ERROR, "unexpected stakind %d", stats->stakind[i]);
+ break;
+ }
+
+ pushJsonbValue(ps, WJB_BEGIN_OBJECT, NULL);
+
+ if (stats->numvalues[i] > 0)
+ {
+ pushJsonbKey(ps, &val, "values");
+ pushJsonbValue(ps, WJB_BEGIN_ARRAY, NULL);
+ for (j = 0; j < stats->numvalues[i]; j++)
+ {
+ Datum v = stats->stavalues[i][j];
+ if (stats->attrtypid == JSONBOID)
+ pushJsonbElemBinary(ps, &val, DatumGetJsonbP(v));
+ else if (stats->attrtypid == TEXTOID)
+ pushJsonbElemText(ps, &val, DatumGetTextP(v));
+ else if (stats->attrtypid == NUMERICOID)
+ pushJsonbElemNumeric(ps, &val, DatumGetNumeric(v));
+ else if (stats->attrtypid == INT4OID)
+ pushJsonbElemInteger(ps, &val, DatumGetInt32(v));
+ else
+ elog(ERROR, "unexpected stat value type %d",
+ stats->attrtypid);
+ }
+ pushJsonbValue(ps, WJB_END_ARRAY, NULL);
+ }
+
+ if (stats->numnumbers[i] > 0)
+ {
+ pushJsonbKey(ps, &val, "numbers");
+ pushJsonbValue(ps, WJB_BEGIN_ARRAY, NULL);
+ for (j = 0; j < stats->numnumbers[i]; j++)
+ pushJsonbElemFloat(ps, &val, stats->stanumbers[i][j]);
+ pushJsonbValue(ps, WJB_END_ARRAY, NULL);
+ }
+
+ pushJsonbValue(ps, WJB_END_OBJECT, NULL);
+ }
+
+ return pushJsonbValue(ps, WJB_END_OBJECT, NULL);
+}
+
+/*
+ * jsonAnalyzeBuildPathStats
+ * Serialize statistics for a particular json path.
+ *
+ * This includes both the per-column stats (stored in "json" key) and the
+ * JSON specific stats (like frequencies of different object types).
+ */
+static Jsonb *
+jsonAnalyzeBuildPathStats(JsonPathAnlStats *pstats)
+{
+ const JsonValueStats *vstats = &pstats->vstats;
+ float4 freq = pstats->freq;
+ bool full = !!pstats->path.parent;
+ JsonbValue val;
+ JsonbValue *jbv;
+ JsonbParseState *ps = NULL;
+
+ pushJsonbValue(&ps, WJB_BEGIN_OBJECT, NULL);
+
+ pushJsonbKeyValueString(&ps, &val, "path", pstats->pathstr);
+
+ pushJsonbKeyValueFloat(&ps, &val, "freq", freq);
+
+ pushJsonbKeyValueFloat(&ps, &val, "freq_null",
+ freq * vstats->nnulls /
+ vstats->jsons.values.count);
+
+ pushJsonbKeyValueFloat(&ps, &val, "freq_boolean",
+ freq * (vstats->booleans.nfalse +
+ vstats->booleans.ntrue) /
+ vstats->jsons.values.count);
+
+ pushJsonbKeyValueFloat(&ps, &val, "freq_string",
+ freq * vstats->strings.values.count /
+ vstats->jsons.values.count);
+
+ pushJsonbKeyValueFloat(&ps, &val, "freq_numeric",
+ freq * vstats->numerics.values.count /
+ vstats->jsons.values.count);
+
+ pushJsonbKeyValueFloat(&ps, &val, "freq_array",
+ freq * vstats->narrays /
+ vstats->jsons.values.count);
+
+ pushJsonbKeyValueFloat(&ps, &val, "freq_object",
+ freq * vstats->nobjects /
+ vstats->jsons.values.count);
+
+ /*
+ * XXX not sure why we keep length and array length stats at this level.
+ * Aren't those covered by the per-column stats? We certainly have
+ * frequencies for array elements etc.
+ */
+ if (pstats->vstats.lens.values.count)
+ jsonAnalyzeMakeScalarStats(&ps, "length", &vstats->lens.stats);
+
+ if (pstats->path.len == -1)
+ {
+ JsonPathAnlStats *parent = (JsonPathAnlStats *) pstats->path.parent;
+
+ pushJsonbKeyValueFloat(&ps, &val, "avg_array_length",
+ (float4) vstats->jsons.values.count /
+ parent->vstats.narrays);
+
+ jsonAnalyzeMakeScalarStats(&ps, "array_length",
+ &parent->vstats.arrlens.stats);
+ }
+
+ if (full)
+ {
+#ifdef JSON_ANALYZE_SCALARS
+ jsonAnalyzeMakeScalarStats(&ps, "string", &vstats->strings.stats);
+ jsonAnalyzeMakeScalarStats(&ps, "numeric", &vstats->numerics.stats);
+#endif
+ jsonAnalyzeMakeScalarStats(&ps, "json", &vstats->jsons.stats);
+ }
+
+ jbv = pushJsonbValue(&ps, WJB_END_OBJECT, NULL);
+
+ return JsonbValueToJsonb(jbv);
+}
+
+/*
+ * jsonAnalyzeCalcPathFreq
+ * Calculate path frequency, i.e. how many documents contain this path.
+ */
+static void
+jsonAnalyzeCalcPathFreq(JsonAnalyzeContext *ctx, JsonPathAnlStats *pstats)
+{
+ JsonPathAnlStats *parent = (JsonPathAnlStats *) pstats->path.parent;
+
+ if (parent)
+ {
+ pstats->freq = parent->freq *
+ (pstats->path.len == -1 ? parent->vstats.narrays
+ : pstats->vstats.jsons.values.count) /
+ parent->vstats.jsons.values.count;
+
+ CLAMP_PROBABILITY(pstats->freq);
+ }
+ else
+ pstats->freq = (double) ctx->analyzed_cnt / ctx->samplerows;
+}
+
+/*
+ * jsonAnalyzePath
+ * Build statistics for values accumulated for this path.
+ *
+ * We're done with accumulating values for this path, so calculate the
+ * statistics for the various arrays.
+ *
+ * XXX I wonder if we could introduce some simple heuristict on which
+ * paths to keep, similarly to what we do for MCV lists. For example a
+ * path that occurred just once is not very interesting, so we could
+ * decide to ignore it and not build the stats. Although that won't
+ * save much, because there'll be very few values accumulated.
+ */
+static void
+jsonAnalyzePath(JsonAnalyzeContext *ctx, JsonPathAnlStats *pstats)
+{
+ MemoryContext oldcxt;
+ JsonValueStats *vstats = &pstats->vstats;
+
+ jsonAnalyzeCalcPathFreq(ctx, pstats);
+
+ /* values combining all object types */
+ jsonAnalyzePathValues(ctx, &vstats->jsons, JSONBOID, pstats->freq);
+
+ /*
+ * lengths and array lengths
+ *
+ * XXX Not sure why we divide it by the number of json values?
+ */
+ jsonAnalyzePathValues(ctx, &vstats->lens, INT4OID,
+ pstats->freq * vstats->lens.values.count /
+ vstats->jsons.values.count);
+ jsonAnalyzePathValues(ctx, &vstats->arrlens, INT4OID,
+ pstats->freq * vstats->arrlens.values.count /
+ vstats->jsons.values.count);
+
+#ifdef JSON_ANALYZE_SCALARS
+ /* stats for values of string/numeric types only */
+ jsonAnalyzePathValues(ctx, &vstats->strings, TEXTOID, pstats->freq);
+ jsonAnalyzePathValues(ctx, &vstats->numerics, NUMERICOID, pstats->freq);
+#endif
+
+ oldcxt = MemoryContextSwitchTo(ctx->stats->anl_context);
+ pstats->stats = jsonAnalyzeBuildPathStats(pstats);
+ MemoryContextSwitchTo(oldcxt);
+}
+
+/*
+ * JsonPathStatsCompare
+ * Compare two path stats (by path string).
+ *
+ * We store the stats sorted by path string, and this is the comparator.
+ */
+static int
+JsonPathStatsCompare(const void *pv1, const void *pv2)
+{
+ return strcmp((*((const JsonPathAnlStats **) pv1))->pathstr,
+ (*((const JsonPathAnlStats **) pv2))->pathstr);
+}
+
+/*
+ * jsonAnalyzeSortPaths
+ * Reads all stats stored in the hash table and sorts them.
+ *
+ * XXX It's a bit strange we simply store the result in the context instead
+ * of just returning it.
+ */
+static void
+jsonAnalyzeSortPaths(JsonAnalyzeContext *ctx)
+{
+ HASH_SEQ_STATUS hseq;
+ JsonPathAnlStats *path;
+ int i;
+
+ ctx->npaths = hash_get_num_entries(ctx->pathshash) + 1;
+ ctx->paths = MemoryContextAlloc(ctx->mcxt,
+ sizeof(*ctx->paths) * ctx->npaths);
+
+ ctx->paths[0] = ctx->root;
+
+ hash_seq_init(&hseq, ctx->pathshash);
+
+ for (i = 1; (path = hash_seq_search(&hseq)); i++)
+ ctx->paths[i] = path;
+
+ pg_qsort(ctx->paths, ctx->npaths, sizeof(*ctx->paths),
+ JsonPathStatsCompare);
+}
+
+/*
+ * jsonAnalyzePaths
+ * Sort the paths and calculate statistics for each of them.
+ *
+ * Now that we're done with processing the documents, we sort the paths
+ * we extracted and calculate stats for each of them.
+ *
+ * XXX I wonder if we could do this in two phases, to maybe not collect
+ * (or even accumulate) values for paths that are not interesting.
+ */
+static void
+jsonAnalyzePaths(JsonAnalyzeContext *ctx)
+{
+ int i;
+
+ jsonAnalyzeSortPaths(ctx);
+
+ for (i = 0; i < ctx->npaths; i++)
+ jsonAnalyzePath(ctx, ctx->paths[i]);
+}
+
+/*
+ * jsonAnalyzeBuildPathStatsArray
+ * ???
+ */
+static Datum *
+jsonAnalyzeBuildPathStatsArray(JsonPathAnlStats **paths, int npaths, int *nvals,
+ const char *prefix, int prefixlen)
+{
+ Datum *values = palloc(sizeof(Datum) * (npaths + 1));
+ JsonbValue *jbvprefix = palloc(sizeof(JsonbValue));
+ int i;
+
+ JsonValueInitStringWithLen(jbvprefix,
+ memcpy(palloc(prefixlen), prefix, prefixlen),
+ prefixlen);
+
+ values[0] = JsonbPGetDatum(JsonbValueToJsonb(jbvprefix));
+
+ for (i = 0; i < npaths; i++)
+ values[i + 1] = JsonbPGetDatum(paths[i]->stats);
+
+ *nvals = npaths + 1;
+
+ return values;
+}
+
+/*
+ * jsonAnalyzeMakeStats
+ * ???
+ */
+static Datum *
+jsonAnalyzeMakeStats(JsonAnalyzeContext *ctx, int *numvalues)
+{
+ Datum *values;
+ MemoryContext oldcxt = MemoryContextSwitchTo(ctx->stats->anl_context);
+
+ values = jsonAnalyzeBuildPathStatsArray(ctx->paths, ctx->npaths,
+ numvalues, "$", 1);
+
+ MemoryContextSwitchTo(oldcxt);
+
+ return values;
+}
+
+/*
+ * jsonAnalyzeBuildSubPathsData
+ * ???
+ */
+bool
+jsonAnalyzeBuildSubPathsData(Datum *pathsDatums, int npaths, int index,
+ const char *path, int pathlen,
+ bool includeSubpaths, float4 nullfrac,
+ Datum *pvals, Datum *pnums)
+{
+ JsonPathAnlStats **pvalues = palloc(sizeof(*pvalues) * npaths);
+ Datum *values;
+ Datum numbers[1];
+ JsonbValue pathkey;
+ int nsubpaths = 0;
+ int nvalues;
+ int i;
+
+ JsonValueInitStringWithLen(&pathkey, "path", 4);
+
+ for (i = index; i < npaths; i++)
+ {
+ Jsonb *jb = DatumGetJsonbP(pathsDatums[i]);
+ JsonbValue *jbv = findJsonbValueFromContainer(&jb->root, JB_FOBJECT,
+ &pathkey);
+
+ if (!jbv || jbv->type != jbvString ||
+ jbv->val.string.len < pathlen ||
+ memcmp(jbv->val.string.val, path, pathlen))
+ break;
+
+ pfree(jbv);
+
+ pvalues[nsubpaths] = palloc(sizeof(**pvalues));
+ pvalues[nsubpaths]->stats = jb;
+
+ nsubpaths++;
+
+ if (!includeSubpaths)
+ break;
+ }
+
+ if (!nsubpaths)
+ {
+ pfree(pvalues);
+ return false;
+ }
+
+ values = jsonAnalyzeBuildPathStatsArray(pvalues, nsubpaths, &nvalues,
+ path, pathlen);
+ *pvals = PointerGetDatum(construct_array(values, nvalues, JSONBOID, -1,
+ false, 'i'));
+
+ pfree(pvalues);
+ pfree(values);
+
+ numbers[0] = Float4GetDatum(nullfrac);
+ *pnums = PointerGetDatum(construct_array(numbers, 1, FLOAT4OID, 4,
+ true /*FLOAT4PASSBYVAL*/, 'i'));
+
+ return true;
+}
+
+/*
+ * jsonAnalyzeInit
+ * Initialize the analyze context so that we can start adding paths.
+ */
+static void
+jsonAnalyzeInit(JsonAnalyzeContext *ctx, VacAttrStats *stats,
+ AnalyzeAttrFetchFunc fetchfunc,
+ int samplerows, double totalrows)
+{
+ HASHCTL hash_ctl;
+
+ memset(ctx, 0, sizeof(*ctx));
+
+ ctx->stats = stats;
+ ctx->fetchfunc = fetchfunc;
+ ctx->mcxt = CurrentMemoryContext;
+ ctx->samplerows = samplerows;
+ ctx->totalrows = totalrows;
+ ctx->target = stats->attr->attstattarget;
+ ctx->scalarsOnly = false;
+
+ MemSet(&hash_ctl, 0, sizeof(hash_ctl));
+ hash_ctl.keysize = sizeof(JsonPathEntry);
+ hash_ctl.entrysize = sizeof(JsonPathAnlStats);
+ hash_ctl.hash = JsonPathHash;
+ hash_ctl.match = JsonPathMatch;
+ hash_ctl.hcxt = ctx->mcxt;
+
+ ctx->pathshash = hash_create("JSON analyze path table", 100, &hash_ctl,
+ HASH_ELEM | HASH_FUNCTION | HASH_COMPARE | HASH_CONTEXT);
+
+ ctx->root = MemoryContextAllocZero(ctx->mcxt, sizeof(JsonPathAnlStats));
+ ctx->root->pathstr = "$";
+}
+
+/*
+ * jsonAnalyzePass
+ * One analysis pass over the JSON column.
+ *
+ * Performs one analysis pass on the JSON documents, and passes them to the
+ * custom analyzefunc.
+ */
+static void
+jsonAnalyzePass(JsonAnalyzeContext *ctx,
+ void (*analyzefunc)(JsonAnalyzeContext *, Jsonb *, void *),
+ void *analyzearg)
+{
+ int row_num;
+
+ MemoryContext tmpcxt = AllocSetContextCreate(CurrentMemoryContext,
+ "Json Analyze Pass Context",
+ ALLOCSET_DEFAULT_MINSIZE,
+ ALLOCSET_DEFAULT_INITSIZE,
+ ALLOCSET_DEFAULT_MAXSIZE);
+
+ MemoryContext oldcxt = MemoryContextSwitchTo(tmpcxt);
+
+ ctx->null_cnt = 0;
+ ctx->analyzed_cnt = 0;
+ ctx->total_width = 0;
+
+ /* Loop over the arrays. */
+ for (row_num = 0; row_num < ctx->samplerows; row_num++)
+ {
+ Datum value;
+ Jsonb *jb;
+ Size width;
+ bool isnull;
+
+ vacuum_delay_point();
+
+ value = ctx->fetchfunc(ctx->stats, row_num, &isnull);
+
+ if (isnull)
+ {
+ /* json is null, just count that */
+ ctx->null_cnt++;
+ continue;
+ }
+
+ width = toast_raw_datum_size(value);
+
+ ctx->total_width += VARSIZE_ANY(DatumGetPointer(value)); /* FIXME raw width? */
+
+ /* Skip too-large values. */
+#define JSON_WIDTH_THRESHOLD (100 * 1024)
+
+ if (width > JSON_WIDTH_THRESHOLD)
+ continue;
+
+ ctx->analyzed_cnt++;
+
+ jb = DatumGetJsonbP(value);
+
+ MemoryContextSwitchTo(oldcxt);
+
+ analyzefunc(ctx, jb, analyzearg);
+
+ oldcxt = MemoryContextSwitchTo(tmpcxt);
+ MemoryContextReset(tmpcxt);
+ }
+
+ MemoryContextSwitchTo(oldcxt);
+}
+
+/*
+ * compute_json_stats() -- compute statistics for a json column
+ */
+static void
+compute_json_stats(VacAttrStats *stats, AnalyzeAttrFetchFunc fetchfunc,
+ int samplerows, double totalrows)
+{
+ JsonAnalyzeContext ctx;
+
+ jsonAnalyzeInit(&ctx, stats, fetchfunc, samplerows, totalrows);
+
+ /* XXX Not sure what the first branch is doing (or supposed to)? */
+ if (false)
+ {
+ jsonAnalyzePass(&ctx, jsonAnalyzeJson, NULL);
+ jsonAnalyzePaths(&ctx);
+ }
+ else
+ {
+ int i;
+ MemoryContext oldcxt;
+ MemoryContext tmpcxt = AllocSetContextCreate(CurrentMemoryContext,
+ "Json Analyze Tmp Context",
+ ALLOCSET_DEFAULT_MINSIZE,
+ ALLOCSET_DEFAULT_INITSIZE,
+ ALLOCSET_DEFAULT_MAXSIZE);
+
+ elog(DEBUG1, "analyzing %s attribute \"%s\"",
+ stats->attrtypid == JSONBOID ? "jsonb" : "json",
+ NameStr(stats->attr->attname));
+
+ elog(DEBUG1, "collecting json paths");
+
+ oldcxt = MemoryContextSwitchTo(tmpcxt);
+
+ /*
+ * XXX It's not immediately clear why this is (-1) and not simply
+ * NULL. It crashes, so presumably it's used to tweak the behavior,
+ * but it's not clear why/how, and it affects place that is pretty
+ * far away, and so not obvious. We should use some sort of flag
+ * with a descriptive name instead.
+ *
+ * XXX If I understand correctly, we simply collect all paths first,
+ * without accumulating any Values. And then in the next step we
+ * process each path independently, probably to save memory (we
+ * don't want to accumulate all values for all paths, with a lot
+ * of duplicities).
+ */
+ jsonAnalyzePass(&ctx, jsonAnalyzeJson, (void *) -1);
+ jsonAnalyzeSortPaths(&ctx);
+
+ MemoryContextReset(tmpcxt);
+
+ for (i = 0; i < ctx.npaths; i++)
+ {
+ JsonPathAnlStats *path = ctx.paths[i];
+
+ elog(DEBUG1, "analyzing json path (%d/%d) %s",
+ i + 1, ctx.npaths, path->pathstr);
+
+ jsonAnalyzePass(&ctx, jsonAnalyzeJsonPath, path);
+ jsonAnalyzePath(&ctx, path);
+
+ MemoryContextReset(tmpcxt);
+ }
+
+ MemoryContextSwitchTo(oldcxt);
+
+ MemoryContextDelete(tmpcxt);
+ }
+
+ /* We can only compute real stats if we found some non-null values. */
+ if (ctx.null_cnt >= samplerows)
+ {
+ /* We found only nulls; assume the column is entirely null */
+ stats->stats_valid = true;
+ stats->stanullfrac = 1.0;
+ stats->stawidth = 0; /* "unknown" */
+ stats->stadistinct = 0.0; /* "unknown" */
+ }
+ else if (!ctx.analyzed_cnt)
+ {
+ int nonnull_cnt = samplerows - ctx.null_cnt;
+
+ /* We found some non-null values, but they were all too wide */
+ stats->stats_valid = true;
+ /* Do the simple null-frac and width stats */
+ stats->stanullfrac = (double) ctx.null_cnt / (double) samplerows;
+ stats->stawidth = ctx.total_width / (double) nonnull_cnt;
+ /* Assume all too-wide values are distinct, so it's a unique column */
+ stats->stadistinct = -1.0 * (1.0 - stats->stanullfrac);
+ }
+ else
+ {
+ VacAttrStats *jsstats = &ctx.root->vstats.jsons.stats;
+ int i;
+ int empty_slot = -1;
+
+ stats->stats_valid = true;
+
+ stats->stanullfrac = jsstats->stanullfrac;
+ stats->stawidth = jsstats->stawidth;
+ stats->stadistinct = jsstats->stadistinct;
+
+ /*
+ * We need to store the statistics the statistics slots. We simply
+ * store the regular stats in the first slots, and then we put the
+ * JSON stats into the first empty slot.
+ */
+ for (i = 0; i < STATISTIC_NUM_SLOTS; i++)
+ {
+ /* once we hit an empty slot, we're done */
+ if (!jsstats->staop[i])
+ {
+ empty_slot = i; /* remember the empty slot */
+ break;
+ }
+
+ stats->stakind[i] = jsstats->stakind[i];
+ stats->staop[i] = jsstats->staop[i];
+ stats->stanumbers[i] = jsstats->stanumbers[i];
+ stats->stavalues[i] = jsstats->stavalues[i];
+ stats->statypid[i] = jsstats->statypid[i];
+ stats->statyplen[i] = jsstats->statyplen[i];
+ stats->statypbyval[i] = jsstats->statypbyval[i];
+ stats->statypalign[i] = jsstats->statypalign[i];
+ stats->numnumbers[i] = jsstats->numnumbers[i];
+ stats->numvalues[i] = jsstats->numvalues[i];
+ }
+
+ Assert((empty_slot >= 0) && (empty_slot < STATISTIC_NUM_SLOTS));
+
+ stats->stakind[empty_slot] = STATISTIC_KIND_JSON;
+ stats->staop[empty_slot] = InvalidOid;
+ stats->numnumbers[empty_slot] = 1;
+ stats->stanumbers[empty_slot] = MemoryContextAlloc(stats->anl_context,
+ sizeof(float4));
+ stats->stanumbers[empty_slot][0] = 0.0; /* nullfrac */
+ stats->stavalues[empty_slot] =
+ jsonAnalyzeMakeStats(&ctx, &stats->numvalues[empty_slot]);
+
+ /* We are storing jsonb values */
+ /* XXX Could the parameters be different on other platforms? */
+ stats->statypid[empty_slot] = JSONBOID;
+ stats->statyplen[empty_slot] = -1;
+ stats->statypbyval[empty_slot] = false;
+ stats->statypalign[empty_slot] = 'i';
+ }
+}
+
+/*
+ * json_typanalyze -- typanalyze function for jsonb
+ */
+Datum
+jsonb_typanalyze(PG_FUNCTION_ARGS)
+{
+ VacAttrStats *stats = (VacAttrStats *) PG_GETARG_POINTER(0);
+ Form_pg_attribute attr = stats->attr;
+
+ /* If the attstattarget column is negative, use the default value */
+ /* NB: it is okay to scribble on stats->attr since it's a copy */
+ if (attr->attstattarget < 0)
+ attr->attstattarget = default_statistics_target;
+
+ stats->compute_stats = compute_json_stats;
+ /* see comment about the choice of minrows in commands/analyze.c */
+ stats->minrows = 300 * attr->attstattarget;
+
+ PG_RETURN_BOOL(true);
+}
diff --git a/src/backend/utils/adt/jsonpath_exec.c b/src/backend/utils/adt/jsonpath_exec.c
index 078aaef5392..16a08561c10 100644
--- a/src/backend/utils/adt/jsonpath_exec.c
+++ b/src/backend/utils/adt/jsonpath_exec.c
@@ -1723,7 +1723,7 @@ executeLikeRegex(JsonPathItem *jsp, JsonbValue *str, JsonbValue *rarg,
cxt->cflags = jspConvertRegexFlags(jsp->content.like_regex.flags);
}
- if (RE_compile_and_execute(cxt->regex, str->val.string.val,
+ if (RE_compile_and_execute(cxt->regex, unconstify(char *, str->val.string.val),
str->val.string.len,
cxt->cflags, DEFAULT_COLLATION_OID, 0, NULL))
return jpbTrue;
diff --git a/src/include/catalog/pg_operator.dat b/src/include/catalog/pg_operator.dat
index c0ff8da722c..736f4a7ec3b 100644
--- a/src/include/catalog/pg_operator.dat
+++ b/src/include/catalog/pg_operator.dat
@@ -3175,7 +3175,7 @@
{ oid => '3211', oid_symbol => 'JsonbObjectFieldOperator',
descr => 'get jsonb object field',
oprname => '->', oprleft => 'jsonb', oprright => 'text', oprresult => 'jsonb',
- oprcode => 'jsonb_object_field' },
+ oprcode => 'jsonb_object_field', oprstat => 'jsonb_stats' },
{ oid => '3477', oid_symbol => 'JsonbObjectFieldTextOperator',
descr => 'get jsonb object field as text',
oprname => '->>', oprleft => 'jsonb', oprright => 'text', oprresult => 'text',
@@ -3183,7 +3183,7 @@
{ oid => '3212', oid_symbol => 'JsonbArrayElementOperator',
descr => 'get jsonb array element',
oprname => '->', oprleft => 'jsonb', oprright => 'int4', oprresult => 'jsonb',
- oprcode => 'jsonb_array_element' },
+ oprcode => 'jsonb_array_element', oprstat => 'jsonb_stats' },
{ oid => '3481', oid_symbol => 'JsonbArrayElementTextOperator',
descr => 'get jsonb array element as text',
oprname => '->>', oprleft => 'jsonb', oprright => 'int4', oprresult => 'text',
@@ -3191,7 +3191,8 @@
{ oid => '3213', oid_symbol => 'JsonbExtractPathOperator',
descr => 'get value from jsonb with path elements',
oprname => '#>', oprleft => 'jsonb', oprright => '_text',
- oprresult => 'jsonb', oprcode => 'jsonb_extract_path' },
+ oprresult => 'jsonb', oprcode => 'jsonb_extract_path',
+ oprstat => 'jsonb_stats' },
{ oid => '3206', oid_symbol => 'JsonbExtractPathTextOperator',
descr => 'get value from jsonb as text with path elements',
oprname => '#>>', oprleft => 'jsonb', oprright => '_text',
@@ -3232,20 +3233,20 @@
oprrest => 'matchingsel', oprjoin => 'matchingjoinsel' },
{ oid => '3247', oid_symbol => 'JsonbExistsOperator', descr => 'key exists',
oprname => '?', oprleft => 'jsonb', oprright => 'text', oprresult => 'bool',
- oprcode => 'jsonb_exists', oprrest => 'matchingsel',
+ oprcode => 'jsonb_exists', oprrest => 'jsonb_sel',
oprjoin => 'matchingjoinsel' },
{ oid => '3248', oid_symbol => 'JsonbExistsAnyOperator', descr => 'any key exists',
oprname => '?|', oprleft => 'jsonb', oprright => '_text', oprresult => 'bool',
- oprcode => 'jsonb_exists_any', oprrest => 'matchingsel',
+ oprcode => 'jsonb_exists_any', oprrest => 'jsonb_sel',
oprjoin => 'matchingjoinsel' },
{ oid => '3249', oid_symbol => 'JsonbExistsAllOperator', descr => 'all keys exist',
oprname => '?&', oprleft => 'jsonb', oprright => '_text', oprresult => 'bool',
- oprcode => 'jsonb_exists_all', oprrest => 'matchingsel',
+ oprcode => 'jsonb_exists_all', oprrest => 'jsonb_sel',
oprjoin => 'matchingjoinsel' },
{ oid => '3250', oid_symbol => 'JsonbContainedOperator', descr => 'is contained by',
oprname => '<@', oprleft => 'jsonb', oprright => 'jsonb', oprresult => 'bool',
oprcom => '@>(jsonb,jsonb)', oprcode => 'jsonb_contained',
- oprrest => 'matchingsel', oprjoin => 'matchingjoinsel' },
+ oprrest => 'jsonb_sel', oprjoin => 'matchingjoinsel' },
{ oid => '3284', descr => 'concatenate',
oprname => '||', oprleft => 'jsonb', oprright => 'jsonb',
oprresult => 'jsonb', oprcode => 'jsonb_concat' },
diff --git a/src/include/catalog/pg_proc.dat b/src/include/catalog/pg_proc.dat
index 4d992dc2241..73ba2d08690 100644
--- a/src/include/catalog/pg_proc.dat
+++ b/src/include/catalog/pg_proc.dat
@@ -11715,4 +11715,15 @@
prorettype => 'bytea', proargtypes => 'pg_brin_minmax_multi_summary',
prosrc => 'brin_minmax_multi_summary_send' },
+# jsonb statistics
+{ oid => '8526', descr => 'jsonb typanalyze',
+ proname => 'jsonb_typanalyze', provolatile => 's', prorettype => 'bool',
+ proargtypes => 'internal', prosrc => 'jsonb_typanalyze' },
+{ oid => '8527', descr => 'jsonb selectivity estimation',
+ proname => 'jsonb_sel', provolatile => 's', prorettype => 'float8',
+ proargtypes => 'internal oid internal int4', prosrc => 'jsonb_sel' },
+{ oid => '8528', descr => 'jsonb statsistics estimation',
+ proname => 'jsonb_stats', provolatile => 's', prorettype => 'bool',
+ proargtypes => 'internal internal int4 internal', prosrc => 'jsonb_stats' },
+
]
diff --git a/src/include/catalog/pg_statistic.h b/src/include/catalog/pg_statistic.h
index 4f95d7ade47..85e6f9e0fb6 100644
--- a/src/include/catalog/pg_statistic.h
+++ b/src/include/catalog/pg_statistic.h
@@ -277,6 +277,8 @@ DECLARE_FOREIGN_KEY((starelid, staattnum), pg_attribute, (attrelid, attnum));
*/
#define STATISTIC_KIND_BOUNDS_HISTOGRAM 7
+#define STATISTIC_KIND_JSON 8
+
#endif /* EXPOSE_TO_CLIENT_CODE */
#endif /* PG_STATISTIC_H */
diff --git a/src/include/catalog/pg_type.dat b/src/include/catalog/pg_type.dat
index f3d94f3cf5d..1f881eef516 100644
--- a/src/include/catalog/pg_type.dat
+++ b/src/include/catalog/pg_type.dat
@@ -445,7 +445,7 @@
typname => 'jsonb', typlen => '-1', typbyval => 'f', typcategory => 'U',
typsubscript => 'jsonb_subscript_handler', typinput => 'jsonb_in',
typoutput => 'jsonb_out', typreceive => 'jsonb_recv', typsend => 'jsonb_send',
- typalign => 'i', typstorage => 'x' },
+ typanalyze => 'jsonb_typanalyze', typalign => 'i', typstorage => 'x' },
{ oid => '4072', array_type_oid => '4073', descr => 'JSON path',
typname => 'jsonpath', typlen => '-1', typbyval => 'f', typcategory => 'U',
typinput => 'jsonpath_in', typoutput => 'jsonpath_out',
diff --git a/src/include/utils/json_selfuncs.h b/src/include/utils/json_selfuncs.h
new file mode 100644
index 00000000000..c8999d105bc
--- /dev/null
+++ b/src/include/utils/json_selfuncs.h
@@ -0,0 +1,100 @@
+/*-------------------------------------------------------------------------
+ *
+ * json_selfuncs.h
+ * JSON cost estimation functions.
+ *
+ *
+ * Portions Copyright (c) 2016, PostgreSQL Global Development Group
+ *
+ * IDENTIFICATION
+ * src/include/utils/json_selfuncs.h
+ *
+ *-------------------------------------------------------------------------
+ */
+
+#ifndef JSON_SELFUNCS_H_
+#define JSON_SELFUNCS_H 1
+
+#include "postgres.h"
+#include "access/htup.h"
+#include "utils/jsonb.h"
+#include "utils/lsyscache.h"
+#include "utils/selfuncs.h"
+
+typedef struct JsonStatData
+{
+ AttStatsSlot attslot;
+ HeapTuple statsTuple;
+ RelOptInfo *rel;
+ Datum *values;
+ int nvalues;
+ float4 nullfrac;
+ const char *prefix;
+ int prefixlen;
+ bool acl_ok;
+} JsonStatData, *JsonStats;
+
+typedef enum
+{
+ JsonPathStatsValues,
+ JsonPathStatsLength,
+ JsonPathStatsArrayLength,
+} JsonPathStatsType;
+
+typedef struct JsonPathStatsData
+{
+ Datum *datum;
+ JsonStats data;
+ char *path;
+ int pathlen;
+ JsonPathStatsType type;
+} JsonPathStatsData, *JsonPathStats;
+
+typedef enum JsonStatType
+{
+ JsonStatJsonb,
+ JsonStatJsonbWithoutSubpaths,
+ JsonStatText,
+ JsonStatString,
+ JsonStatNumeric,
+ JsonStatFreq,
+} JsonStatType;
+
+extern bool jsonStatsInit(JsonStats stats, const VariableStatData *vardata);
+extern void jsonStatsRelease(JsonStats data);
+
+extern JsonPathStats jsonStatsGetPathStatsStr(JsonStats stats,
+ const char *path, int pathlen);
+
+extern JsonPathStats jsonPathStatsGetSubpath(JsonPathStats stats,
+ const char *subpath, int subpathlen);
+
+extern bool jsonPathStatsGetNextKeyStats(JsonPathStats stats,
+ JsonPathStats *keystats, bool keysOnly);
+
+extern JsonPathStats jsonPathStatsGetLengthStats(JsonPathStats pstats);
+
+extern float4 jsonPathStatsGetFreq(JsonPathStats pstats, float4 defaultfreq);
+
+extern float4 jsonPathStatsGetTypeFreq(JsonPathStats pstats,
+ JsonbValueType type, float4 defaultfreq);
+
+extern float4 jsonPathStatsGetAvgArraySize(JsonPathStats pstats);
+
+extern Selectivity jsonPathStatsGetArrayIndexSelectivity(JsonPathStats pstats,
+ int index);
+
+extern Selectivity jsonSelectivity(JsonPathStats stats, Datum scalar, Oid oper);
+
+
+extern bool jsonAnalyzeBuildSubPathsData(Datum *pathsDatums,
+ int npaths, int index,
+ const char *path, int pathlen,
+ bool includeSubpaths, float4 nullfrac,
+ Datum *pvals, Datum *pnums);
+
+extern Datum jsonb_typanalyze(PG_FUNCTION_ARGS);
+extern Datum jsonb_stats(PG_FUNCTION_ARGS);
+extern Datum jsonb_sel(PG_FUNCTION_ARGS);
+
+#endif /* JSON_SELFUNCS_H */
diff --git a/src/test/regress/expected/jsonb_stats.out b/src/test/regress/expected/jsonb_stats.out
new file mode 100644
index 00000000000..0badf8e8479
--- /dev/null
+++ b/src/test/regress/expected/jsonb_stats.out
@@ -0,0 +1,713 @@
+CREATE OR REPLACE FUNCTION explain_jsonb(sql_query text)
+RETURNS TABLE(explain_line json) AS
+$$
+BEGIN
+ RETURN QUERY EXECUTE 'EXPLAIN (ANALYZE, FORMAT json) ' || sql_query;
+END;
+$$ LANGUAGE plpgsql;
+CREATE OR REPLACE FUNCTION get_plan_and_actual_rows(sql_query text)
+RETURNS TABLE(plan integer, actual integer) AS
+$$
+ SELECT
+ (plan->>'Plan Rows')::integer plan,
+ (plan->>'Actual Rows')::integer actual
+ FROM (
+ SELECT explain_jsonb(sql_query) #> '{0,Plan,Plans,0}'
+ ) p(plan)
+$$ LANGUAGE sql;
+CREATE OR REPLACE FUNCTION check_estimate(sql_query text, accuracy real)
+RETURNS boolean AS
+$$
+ SELECT plan BETWEEN actual / (1 + accuracy) AND (actual + 1) * (1 + accuracy)
+ FROM (SELECT * FROM get_plan_and_actual_rows(sql_query)) x
+$$ LANGUAGE sql;
+CREATE OR REPLACE FUNCTION check_estimate2(sql_query text, accuracy real)
+RETURNS TABLE(min integer, max integer) AS
+$$
+ SELECT (actual * (1 - accuracy))::integer, ((actual + 1) * (1 + accuracy))::integer
+ FROM (SELECT * FROM get_plan_and_actual_rows(sql_query)) x
+$$ LANGUAGE sql;
+CREATE TABLE jsonb_stats_test(js jsonb);
+INSERT INTO jsonb_stats_test SELECT NULL FROM generate_series(1, 1000);
+INSERT INTO jsonb_stats_test SELECT 'null' FROM generate_series(1, 200);
+INSERT INTO jsonb_stats_test SELECT 'true' FROM generate_series(1, 300);
+INSERT INTO jsonb_stats_test SELECT 'false' FROM generate_series(1, 500);
+INSERT INTO jsonb_stats_test SELECT '12345' FROM generate_series(1, 100);
+INSERT INTO jsonb_stats_test SELECT (1000 * (i % 10))::text::jsonb FROM generate_series(1, 400) i;
+INSERT INTO jsonb_stats_test SELECT i::text::jsonb FROM generate_series(1, 500) i;
+INSERT INTO jsonb_stats_test SELECT '"foo"' FROM generate_series(1, 100);
+INSERT INTO jsonb_stats_test SELECT format('"bar%s"', i % 10)::jsonb FROM generate_series(1, 400) i;
+INSERT INTO jsonb_stats_test SELECT format('"baz%s"', i)::jsonb FROM generate_series(1, 500) i;
+INSERT INTO jsonb_stats_test SELECT '{}' FROM generate_series(1, 100);
+INSERT INTO jsonb_stats_test SELECT jsonb_build_object('foo', 'bar') FROM generate_series(1, 100);
+INSERT INTO jsonb_stats_test SELECT jsonb_build_object('foo', 'baz' || (i % 10)) FROM generate_series(1, 300) i;
+INSERT INTO jsonb_stats_test SELECT jsonb_build_object('foo', i % 10) FROM generate_series(1, 200) i;
+INSERT INTO jsonb_stats_test SELECT jsonb_build_object('"foo \"bar"', i % 10) FROM generate_series(1, 200) i;
+INSERT INTO jsonb_stats_test SELECT '[]' FROM generate_series(1, 100);
+INSERT INTO jsonb_stats_test SELECT '["foo"]' FROM generate_series(1, 200);
+INSERT INTO jsonb_stats_test SELECT '[12345]' FROM generate_series(1, 300);
+INSERT INTO jsonb_stats_test SELECT '[["foo"]]' FROM generate_series(1, 200);
+INSERT INTO jsonb_stats_test SELECT '[{"key": "foo"}]' FROM generate_series(1, 200);
+INSERT INTO jsonb_stats_test SELECT '[null, "foo"]' FROM generate_series(1, 200);
+INSERT INTO jsonb_stats_test SELECT '[null, 12345]' FROM generate_series(1, 300);
+INSERT INTO jsonb_stats_test SELECT '[null, ["foo"]]' FROM generate_series(1, 200);
+INSERT INTO jsonb_stats_test SELECT '[null, {"key": "foo"}]' FROM generate_series(1, 200);
+-- Build random variable-length integer arrays
+SELECT setseed(0.0);
+ setseed
+---------
+
+(1 row)
+
+INSERT INTO jsonb_stats_test
+SELECT jsonb_build_object('array',
+ jsonb_build_array())
+FROM generate_series(1, 1000);
+INSERT INTO jsonb_stats_test
+SELECT jsonb_build_object('array',
+ jsonb_build_array(
+ floor(random() * 10)::int))
+FROM generate_series(1, 4000);
+INSERT INTO jsonb_stats_test
+SELECT jsonb_build_object('array',
+ jsonb_build_array(
+ floor(random() * 10)::int,
+ floor(random() * 10)::int))
+FROM generate_series(1, 3000);
+INSERT INTO jsonb_stats_test
+SELECT jsonb_build_object('array',
+ jsonb_build_array(
+ floor(random() * 10)::int,
+ floor(random() * 10)::int,
+ floor(random() * 10)::int))
+FROM generate_series(1, 2000);
+ANALYZE jsonb_stats_test;
+CREATE OR REPLACE FUNCTION check_jsonb_stats_test_estimate(sql_condition text, accuracy real)
+RETURNS boolean AS
+$$
+ SELECT check_estimate('SELECT count(*) FROM jsonb_stats_test WHERE ' || sql_condition, accuracy)
+$$ LANGUAGE sql;
+DROP FUNCTION IF EXISTS check_jsonb_stats_test_estimate2(text, real);
+NOTICE: function check_jsonb_stats_test_estimate2(text,pg_catalog.float4) does not exist, skipping
+CREATE OR REPLACE FUNCTION check_jsonb_stats_test_estimate2(sql_condition text, accuracy real)
+RETURNS TABLE(plan integer, actual integer) AS
+$$
+ SELECT get_plan_and_actual_rows('SELECT count(*) FROM jsonb_stats_test WHERE ' || sql_condition)
+$$ LANGUAGE sql;
+-- Check NULL estimate
+SELECT check_jsonb_stats_test_estimate($$js IS NULL$$, 0.03);
+ check_jsonb_stats_test_estimate
+---------------------------------
+ t
+(1 row)
+
+SELECT check_jsonb_stats_test_estimate($$js -> 'bad_key' IS NULL$$, 0.01);
+ check_jsonb_stats_test_estimate
+---------------------------------
+ t
+(1 row)
+
+SELECT check_jsonb_stats_test_estimate($$js #> '{bad_key}' IS NULL$$, 0.01);
+ check_jsonb_stats_test_estimate
+---------------------------------
+ t
+(1 row)
+
+SELECT check_jsonb_stats_test_estimate($$js -> 1000000 IS NULL$$, 0.01);
+ check_jsonb_stats_test_estimate
+---------------------------------
+ t
+(1 row)
+
+SELECT check_jsonb_stats_test_estimate($$js #> '{1000000}' IS NULL$$, 0.01);
+ check_jsonb_stats_test_estimate
+---------------------------------
+ t
+(1 row)
+
+SELECT check_jsonb_stats_test_estimate($$js -> 'bad_key1' -> 'bad_key2' IS NULL$$, 0.01);
+ check_jsonb_stats_test_estimate
+---------------------------------
+ t
+(1 row)
+
+SELECT check_jsonb_stats_test_estimate($$js #> '{bad_key1,bad_key2}' IS NULL$$, 0.01);
+ check_jsonb_stats_test_estimate
+---------------------------------
+ t
+(1 row)
+
+SELECT check_jsonb_stats_test_estimate($$js -> 'bad_key1' -> 1 IS NULL$$, 0.01);
+ check_jsonb_stats_test_estimate
+---------------------------------
+ t
+(1 row)
+
+SELECT check_jsonb_stats_test_estimate($$js #> '{bad_key1,1}' IS NULL$$, 0.01);
+ check_jsonb_stats_test_estimate
+---------------------------------
+ t
+(1 row)
+
+SELECT check_jsonb_stats_test_estimate($$js -> 1000000 -> 'foo' IS NULL$$, 0.01);
+ check_jsonb_stats_test_estimate
+---------------------------------
+ t
+(1 row)
+
+SELECT check_jsonb_stats_test_estimate($$js #> '{1000000,foo}' IS NULL$$, 0.01);
+ check_jsonb_stats_test_estimate
+---------------------------------
+ t
+(1 row)
+
+SELECT check_jsonb_stats_test_estimate($$js -> 'bad_key' = '123'$$, 0.01);
+ check_jsonb_stats_test_estimate
+---------------------------------
+ t
+(1 row)
+
+SELECT check_jsonb_stats_test_estimate($$js -> 1000000 = '123'$$, 0.01);
+ check_jsonb_stats_test_estimate
+---------------------------------
+ t
+(1 row)
+
+-- Check null eq estimate
+SELECT check_jsonb_stats_test_estimate($$js = 'null'$$, 0.1);
+ check_jsonb_stats_test_estimate
+---------------------------------
+ t
+(1 row)
+
+SELECT check_jsonb_stats_test_estimate($$js @> 'null'$$, 0.1);
+ check_jsonb_stats_test_estimate
+---------------------------------
+ t
+(1 row)
+
+-- Check boolean eq estimate
+SELECT check_jsonb_stats_test_estimate($$js = 'true'$$, 0.1);
+ check_jsonb_stats_test_estimate
+---------------------------------
+ t
+(1 row)
+
+SELECT check_jsonb_stats_test_estimate($$js @> 'true'$$, 0.1);
+ check_jsonb_stats_test_estimate
+---------------------------------
+ t
+(1 row)
+
+SELECT check_jsonb_stats_test_estimate($$js = 'false'$$, 0.1);
+ check_jsonb_stats_test_estimate
+---------------------------------
+ t
+(1 row)
+
+SELECT check_jsonb_stats_test_estimate($$js @> 'false'$$, 0.1);
+ check_jsonb_stats_test_estimate
+---------------------------------
+ t
+(1 row)
+
+-- Check numeric eq estimate
+SELECT check_jsonb_stats_test_estimate($$js = '12345'$$, 0.1);
+ check_jsonb_stats_test_estimate
+---------------------------------
+ t
+(1 row)
+
+SELECT check_jsonb_stats_test_estimate($$js#>'{}' = '12345'$$, 0.1);
+ check_jsonb_stats_test_estimate
+---------------------------------
+ t
+(1 row)
+
+SELECT check_jsonb_stats_test_estimate($$js = '3000'$$, 0.3);
+ check_jsonb_stats_test_estimate
+---------------------------------
+ t
+(1 row)
+
+SELECT check_jsonb_stats_test_estimate($$js = '1234'$$, 1);
+ check_jsonb_stats_test_estimate
+---------------------------------
+ t
+(1 row)
+
+SELECT check_jsonb_stats_test_estimate($$js @> '6000'$$, 0.2);
+ check_jsonb_stats_test_estimate
+---------------------------------
+ t
+(1 row)
+
+-- Check numeric range estimate
+SELECT check_jsonb_stats_test_estimate($$js < '0'$$, 0.1);
+ check_jsonb_stats_test_estimate
+---------------------------------
+ t
+(1 row)
+
+SELECT check_jsonb_stats_test_estimate($$js < '100'$$, 0.1);
+ check_jsonb_stats_test_estimate
+---------------------------------
+ t
+(1 row)
+
+SELECT check_jsonb_stats_test_estimate($$js < '1000'$$, 0.1);
+ check_jsonb_stats_test_estimate
+---------------------------------
+ t
+(1 row)
+
+SELECT check_jsonb_stats_test_estimate($$js < '3456'$$, 0.1);
+ check_jsonb_stats_test_estimate
+---------------------------------
+ t
+(1 row)
+
+SELECT check_jsonb_stats_test_estimate($$js < '10000'$$, 0.1);
+ check_jsonb_stats_test_estimate
+---------------------------------
+ t
+(1 row)
+
+SELECT check_jsonb_stats_test_estimate($$js < '100000'$$, 0.1);
+ check_jsonb_stats_test_estimate
+---------------------------------
+ t
+(1 row)
+
+SELECT check_jsonb_stats_test_estimate($$js > '100' AND js < '600'$$, 0.5);
+ check_jsonb_stats_test_estimate
+---------------------------------
+ t
+(1 row)
+
+SELECT check_jsonb_stats_test_estimate($$js > '6800' AND js < '12000'$$, 0.1);
+ check_jsonb_stats_test_estimate
+---------------------------------
+ t
+(1 row)
+
+-- Check string eq estimate
+SELECT check_jsonb_stats_test_estimate($$js = '"foo"'$$, 0.1);
+ check_jsonb_stats_test_estimate
+---------------------------------
+ t
+(1 row)
+
+SELECT check_jsonb_stats_test_estimate($$js = '"bar7"'$$, 0.2);
+ check_jsonb_stats_test_estimate
+---------------------------------
+ t
+(1 row)
+
+SELECT check_jsonb_stats_test_estimate($$js = '"baz1234"'$$, 10);
+ check_jsonb_stats_test_estimate
+---------------------------------
+ t
+(1 row)
+
+SELECT check_jsonb_stats_test_estimate($$js @> '"bar4"'$$, 0.3);
+ check_jsonb_stats_test_estimate
+---------------------------------
+ t
+(1 row)
+
+-- Check string range estimate
+SELECT check_jsonb_stats_test_estimate($$js > '"foo"'$$, 0.01);
+ check_jsonb_stats_test_estimate
+---------------------------------
+ t
+(1 row)
+
+SELECT check_jsonb_stats_test_estimate($$js > '"bar"'$$, 0.01);
+ check_jsonb_stats_test_estimate
+---------------------------------
+ t
+(1 row)
+
+SELECT check_jsonb_stats_test_estimate($$js > '"baz"'$$, 0.01);
+ check_jsonb_stats_test_estimate
+---------------------------------
+ t
+(1 row)
+
+-- Check object eq estimate
+SELECT check_jsonb_stats_test_estimate($$js = '{}'$$, 0.1);
+ check_jsonb_stats_test_estimate
+---------------------------------
+ t
+(1 row)
+
+SELECT check_jsonb_stats_test_estimate($$js > '{}'$$, 0.1);
+ check_jsonb_stats_test_estimate
+---------------------------------
+ t
+(1 row)
+
+-- Check object key eq estimate
+SELECT check_jsonb_stats_test_estimate($$js -> 'foo' = '"bar"'$$, 0.2);
+ check_jsonb_stats_test_estimate
+---------------------------------
+ t
+(1 row)
+
+SELECT check_jsonb_stats_test_estimate($$js -> 'foo' = '"baz"'$$, 0.1);
+ check_jsonb_stats_test_estimate
+---------------------------------
+ t
+(1 row)
+
+SELECT check_jsonb_stats_test_estimate($$js -> 'foo' = '"baz5"'$$, 0.3);
+ check_jsonb_stats_test_estimate
+---------------------------------
+ t
+(1 row)
+
+SELECT check_jsonb_stats_test_estimate($$js #> '{foo}' = '"bar"'$$, 0.2);
+ check_jsonb_stats_test_estimate
+---------------------------------
+ t
+(1 row)
+
+-- Check object key range estimate
+SELECT check_jsonb_stats_test_estimate($$js -> 'foo' >= '"baz2"'$$, 0.1);
+ check_jsonb_stats_test_estimate
+---------------------------------
+ t
+(1 row)
+
+SELECT check_jsonb_stats_test_estimate($$js -> 'foo' < '"baz9"'$$, 0.1);
+ check_jsonb_stats_test_estimate
+---------------------------------
+ t
+(1 row)
+
+SELECT check_jsonb_stats_test_estimate($$js -> 'foo' >= '"baz2"' AND js -> 'foo' < '"baz9"'$$, 0.1);
+ check_jsonb_stats_test_estimate
+---------------------------------
+ t
+(1 row)
+
+-- Check array eq estimate
+SELECT check_jsonb_stats_test_estimate($$js = '[]'$$, 0.1);
+ check_jsonb_stats_test_estimate
+---------------------------------
+ t
+(1 row)
+
+SELECT check_jsonb_stats_test_estimate($$js >= '[]' AND js < '{}'$$, 0.1);
+ check_jsonb_stats_test_estimate
+---------------------------------
+ t
+(1 row)
+
+-- Check variable-length array element eq estimate
+SELECT check_jsonb_stats_test_estimate($$js -> 'array' -> 0 = '1'$$, 0.2);
+ check_jsonb_stats_test_estimate
+---------------------------------
+ t
+(1 row)
+
+SELECT check_jsonb_stats_test_estimate($$js -> 'array' -> 1 = '6'$$, 0.2);
+ check_jsonb_stats_test_estimate
+---------------------------------
+ t
+(1 row)
+
+SELECT check_jsonb_stats_test_estimate($$js -> 'array' -> 2 = '8'$$, 0.2);
+ check_jsonb_stats_test_estimate
+---------------------------------
+ t
+(1 row)
+
+SELECT check_jsonb_stats_test_estimate($$js -> 'array' -> 3 = '1'$$, 0.2);
+ check_jsonb_stats_test_estimate
+---------------------------------
+ t
+(1 row)
+
+-- Check variable-length array element range estimate
+SELECT check_jsonb_stats_test_estimate($$js -> 'array' -> 0 < '7'$$, 0.1);
+ check_jsonb_stats_test_estimate
+---------------------------------
+ t
+(1 row)
+
+SELECT check_jsonb_stats_test_estimate($$js -> 'array' -> 1 < '7'$$, 0.1);
+ check_jsonb_stats_test_estimate
+---------------------------------
+ t
+(1 row)
+
+SELECT check_jsonb_stats_test_estimate($$js -> 'array' -> 2 < '7'$$, 0.1);
+ check_jsonb_stats_test_estimate
+---------------------------------
+ t
+(1 row)
+
+SELECT check_jsonb_stats_test_estimate($$js -> 'array' -> 3 < '7'$$, 0.1);
+ check_jsonb_stats_test_estimate
+---------------------------------
+ t
+(1 row)
+
+-- Check variable-length array containment estimate
+SELECT check_jsonb_stats_test_estimate($$js -> 'array' @> '[]'$$, 0.2);
+ check_jsonb_stats_test_estimate
+---------------------------------
+ t
+(1 row)
+
+SELECT check_jsonb_stats_test_estimate($$js -> 'array' @> '[1]'$$, 0.2);
+ check_jsonb_stats_test_estimate
+---------------------------------
+ t
+(1 row)
+
+SELECT check_jsonb_stats_test_estimate($$js -> 'array' @> '[100]'$$, 0.2);
+ check_jsonb_stats_test_estimate
+---------------------------------
+ t
+(1 row)
+
+SELECT check_jsonb_stats_test_estimate($$js -> 'array' @> '[1, 2]'$$, 1);
+ check_jsonb_stats_test_estimate
+---------------------------------
+ t
+(1 row)
+
+SELECT check_jsonb_stats_test_estimate($$js -> 'array' @> '[1, 100]'$$, 1);
+ check_jsonb_stats_test_estimate
+---------------------------------
+ t
+(1 row)
+
+SELECT check_jsonb_stats_test_estimate($$js -> 'array' @> '[1, 2, 100]'$$, 1);
+ check_jsonb_stats_test_estimate
+---------------------------------
+ t
+(1 row)
+
+SELECT check_jsonb_stats_test_estimate($$js -> 'array' @> '[1, 2, 3]'$$, 5);
+ check_jsonb_stats_test_estimate
+---------------------------------
+ t
+(1 row)
+
+SELECT check_jsonb_stats_test_estimate($$js -> 'array' @> '1'$$, 0.3);
+ check_jsonb_stats_test_estimate
+---------------------------------
+ t
+(1 row)
+
+SELECT check_jsonb_stats_test_estimate($$js -> 'array' @> '100'$$, 10);
+ check_jsonb_stats_test_estimate
+---------------------------------
+ t
+(1 row)
+
+SELECT check_jsonb_stats_test_estimate($$js -> 'array' -> 0 @> '1'$$, 0.3);
+ check_jsonb_stats_test_estimate
+---------------------------------
+ t
+(1 row)
+
+SELECT check_jsonb_stats_test_estimate($$js -> 'array' -> 1 @> '1'$$, 0.3);
+ check_jsonb_stats_test_estimate
+---------------------------------
+ t
+(1 row)
+
+SELECT check_jsonb_stats_test_estimate($$js -> 'array' -> 2 @> '1'$$, 0.3);
+ check_jsonb_stats_test_estimate
+---------------------------------
+ t
+(1 row)
+
+SELECT check_jsonb_stats_test_estimate($$js -> 'array' -> 3 @> '1'$$, 0.3);
+ check_jsonb_stats_test_estimate
+---------------------------------
+ t
+(1 row)
+
+SELECT check_jsonb_stats_test_estimate($$js -> 'array' -> 0 @> '[1]'$$, 0.3);
+ check_jsonb_stats_test_estimate
+---------------------------------
+ t
+(1 row)
+
+SELECT check_jsonb_stats_test_estimate($$js @> '{"array": []}'$$, 0.1);
+ check_jsonb_stats_test_estimate
+---------------------------------
+ t
+(1 row)
+
+SELECT check_jsonb_stats_test_estimate($$js @> '{"array": [1]}'$$, 0.3);
+ check_jsonb_stats_test_estimate
+---------------------------------
+ t
+(1 row)
+
+SELECT check_jsonb_stats_test_estimate($$js @> '{"array": [100]}'$$, 0.3);
+ check_jsonb_stats_test_estimate
+---------------------------------
+ t
+(1 row)
+
+SELECT check_jsonb_stats_test_estimate($$js @> '{"array": [1, 2]}'$$, 1);
+ check_jsonb_stats_test_estimate
+---------------------------------
+ t
+(1 row)
+
+SELECT check_jsonb_stats_test_estimate($$js @> '{"array": [1, 100]}'$$, 1);
+ check_jsonb_stats_test_estimate
+---------------------------------
+ t
+(1 row)
+
+SELECT check_jsonb_stats_test_estimate($$js @> '{"array": [1, 2, 100]}'$$, 1);
+ check_jsonb_stats_test_estimate
+---------------------------------
+ t
+(1 row)
+
+SELECT check_jsonb_stats_test_estimate($$js @> '{"array": [1, 2, 3]}'$$, 100);
+ check_jsonb_stats_test_estimate
+---------------------------------
+ t
+(1 row)
+
+-- check misc containment
+SELECT check_jsonb_stats_test_estimate($$js @> '"foo"'$$, 0.1);
+ check_jsonb_stats_test_estimate
+---------------------------------
+ t
+(1 row)
+
+SELECT check_jsonb_stats_test_estimate($$js @> '12345'$$, 0.1);
+ check_jsonb_stats_test_estimate
+---------------------------------
+ t
+(1 row)
+
+SELECT check_jsonb_stats_test_estimate($$js @> '[]'$$, 0.1);
+ check_jsonb_stats_test_estimate
+---------------------------------
+ t
+(1 row)
+
+SELECT check_jsonb_stats_test_estimate($$js @> '[12345]'$$, 0.1);
+ check_jsonb_stats_test_estimate
+---------------------------------
+ t
+(1 row)
+
+SELECT check_jsonb_stats_test_estimate($$js @> '["foo"]'$$, 0.1);
+ check_jsonb_stats_test_estimate
+---------------------------------
+ t
+(1 row)
+
+SELECT check_jsonb_stats_test_estimate($$js @> '[["foo", "bar"]]'$$, 0.1);
+ check_jsonb_stats_test_estimate
+---------------------------------
+ t
+(1 row)
+
+SELECT check_jsonb_stats_test_estimate($$js @> '[["foo"]]'$$, 0.2);
+ check_jsonb_stats_test_estimate
+---------------------------------
+ t
+(1 row)
+
+SELECT check_jsonb_stats_test_estimate($$js @> '[{"key": "foo"}]'$$, 0.1);
+ check_jsonb_stats_test_estimate
+---------------------------------
+ t
+(1 row)
+
+SELECT check_jsonb_stats_test_estimate($$js @> '[null]'$$, 0.3);
+ check_jsonb_stats_test_estimate
+---------------------------------
+ t
+(1 row)
+
+-- Check object key null estimate
+SELECT check_jsonb_stats_test_estimate($$js -> 'foo' IS NULL$$, 0.1);
+ check_jsonb_stats_test_estimate
+---------------------------------
+ t
+(1 row)
+
+SELECT check_jsonb_stats_test_estimate($$js -> 'foo' IS NOT NULL$$, 0.1);
+ check_jsonb_stats_test_estimate
+---------------------------------
+ t
+(1 row)
+
+SELECT check_jsonb_stats_test_estimate($$js -> '"foo \"bar"' IS NOT NULL$$, 0.1);
+ check_jsonb_stats_test_estimate
+---------------------------------
+ t
+(1 row)
+
+SELECT check_jsonb_stats_test_estimate($$js -> 'bad_key' IS NULL$$, 0.01);
+ check_jsonb_stats_test_estimate
+---------------------------------
+ t
+(1 row)
+
+SELECT check_jsonb_stats_test_estimate($$js -> 'bad_key' IS NOT NULL$$, 0.01);
+ check_jsonb_stats_test_estimate
+---------------------------------
+ t
+(1 row)
+
+-- Check object key existence
+SELECT check_jsonb_stats_test_estimate($$js ? 'bad_key'$$, 10);
+ check_jsonb_stats_test_estimate
+---------------------------------
+ t
+(1 row)
+
+SELECT check_jsonb_stats_test_estimate($$js ? 'foo'$$, 0.1);
+ check_jsonb_stats_test_estimate
+---------------------------------
+ t
+(1 row)
+
+SELECT check_jsonb_stats_test_estimate($$js ? 'array'$$, 0.1);
+ check_jsonb_stats_test_estimate
+---------------------------------
+ t
+(1 row)
+
+SELECT check_jsonb_stats_test_estimate($$js ?| '{foo,bad_key}'$$, 0.1);
+ check_jsonb_stats_test_estimate
+---------------------------------
+ t
+(1 row)
+
+SELECT check_jsonb_stats_test_estimate($$js ?| '{foo,array}'$$, 0.1);
+ check_jsonb_stats_test_estimate
+---------------------------------
+ t
+(1 row)
+
+SELECT check_jsonb_stats_test_estimate($$js ?& '{foo,bad_key}'$$, 0.1);
+ check_jsonb_stats_test_estimate
+---------------------------------
+ t
+(1 row)
+
+SELECT check_jsonb_stats_test_estimate($$js ?& '{foo,bar}'$$, 0.1);
+ check_jsonb_stats_test_estimate
+---------------------------------
+ t
+(1 row)
+
diff --git a/src/test/regress/expected/rules.out b/src/test/regress/expected/rules.out
index b58b062b10d..ffbce87296e 100644
--- a/src/test/regress/expected/rules.out
+++ b/src/test/regress/expected/rules.out
@@ -2535,6 +2535,38 @@ pg_stats_ext_exprs| SELECT cn.nspname AS schemaname,
LEFT JOIN pg_namespace sn ON ((sn.oid = s.stxnamespace)))
JOIN LATERAL ( SELECT unnest(pg_get_statisticsobjdef_expressions(s.oid)) AS expr,
unnest(sd.stxdexpr) AS a) stat ON ((stat.expr IS NOT NULL)));
+pg_stats_json| SELECT n.nspname AS schemaname,
+ c.relname AS tablename,
+ a.attname,
+ (paths.path ->> 'path'::text) AS json_path,
+ s.stainherit AS inherited,
+ (((paths.path -> 'json'::text) ->> 'nullfrac'::text))::real AS null_frac,
+ (((paths.path -> 'json'::text) ->> 'width'::text))::real AS avg_width,
+ (((paths.path -> 'json'::text) ->> 'distinct'::text))::real AS n_distinct,
+ ARRAY( SELECT val.value AS val
+ FROM jsonb_array_elements((((paths.path -> 'json'::text) -> 'mcv'::text) -> 'values'::text)) val(value)) AS most_common_vals,
+ ARRAY( SELECT ((num.value)::text)::real AS num
+ FROM jsonb_array_elements((((paths.path -> 'json'::text) -> 'mcv'::text) -> 'numbers'::text)) num(value)) AS most_common_freqs,
+ ARRAY( SELECT val.value AS val
+ FROM jsonb_array_elements((((paths.path -> 'json'::text) -> 'histogram'::text) -> 'values'::text)) val(value)) AS histogram_bounds,
+ ARRAY( SELECT ((val.value)::text)::integer AS val
+ FROM jsonb_array_elements((((paths.path -> 'array_length'::text) -> 'mcv'::text) -> 'values'::text)) val(value)) AS most_common_array_lengths,
+ ARRAY( SELECT ((num.value)::text)::real AS num
+ FROM jsonb_array_elements((((paths.path -> 'array_length'::text) -> 'mcv'::text) -> 'numbers'::text)) num(value)) AS most_common_array_length_freqs,
+ (((paths.path -> 'json'::text) ->> 'correlation'::text))::real AS correlation
+ FROM (((pg_statistic s
+ JOIN pg_class c ON ((c.oid = s.starelid)))
+ JOIN pg_attribute a ON (((c.oid = a.attrelid) AND (a.attnum = s.staattnum))))
+ LEFT JOIN pg_namespace n ON ((n.oid = c.relnamespace))),
+ LATERAL ( SELECT unnest((((
+ CASE
+ WHEN (s.stakind1 = 8) THEN s.stavalues1
+ WHEN (s.stakind2 = 8) THEN s.stavalues2
+ WHEN (s.stakind3 = 8) THEN s.stavalues3
+ WHEN (s.stakind4 = 8) THEN s.stavalues4
+ WHEN (s.stakind5 = 8) THEN s.stavalues5
+ ELSE NULL::anyarray
+ END)::text)::jsonb[])[2:]) AS path) paths;
pg_tables| SELECT n.nspname AS schemaname,
c.relname AS tablename,
pg_get_userbyid(c.relowner) AS tableowner,
diff --git a/src/test/regress/parallel_schedule b/src/test/regress/parallel_schedule
index 5b0c73d7e37..d108cc62107 100644
--- a/src/test/regress/parallel_schedule
+++ b/src/test/regress/parallel_schedule
@@ -112,7 +112,7 @@ test: select_views portals_p2 foreign_key cluster dependency guc bitmapops combo
# ----------
# Another group of parallel tests (JSON related)
# ----------
-test: json jsonb json_encoding jsonpath jsonpath_encoding jsonb_jsonpath
+test: json jsonb json_encoding jsonpath jsonpath_encoding jsonb_jsonpath jsonb_stats
# ----------
# Another group of parallel tests
diff --git a/src/test/regress/sql/jsonb_stats.sql b/src/test/regress/sql/jsonb_stats.sql
new file mode 100644
index 00000000000..37c83aa8e32
--- /dev/null
+++ b/src/test/regress/sql/jsonb_stats.sql
@@ -0,0 +1,249 @@
+CREATE OR REPLACE FUNCTION explain_jsonb(sql_query text)
+RETURNS TABLE(explain_line json) AS
+$$
+BEGIN
+ RETURN QUERY EXECUTE 'EXPLAIN (ANALYZE, FORMAT json) ' || sql_query;
+END;
+$$ LANGUAGE plpgsql;
+
+CREATE OR REPLACE FUNCTION get_plan_and_actual_rows(sql_query text)
+RETURNS TABLE(plan integer, actual integer) AS
+$$
+ SELECT
+ (plan->>'Plan Rows')::integer plan,
+ (plan->>'Actual Rows')::integer actual
+ FROM (
+ SELECT explain_jsonb(sql_query) #> '{0,Plan,Plans,0}'
+ ) p(plan)
+$$ LANGUAGE sql;
+
+CREATE OR REPLACE FUNCTION check_estimate(sql_query text, accuracy real)
+RETURNS boolean AS
+$$
+ SELECT plan BETWEEN actual / (1 + accuracy) AND (actual + 1) * (1 + accuracy)
+ FROM (SELECT * FROM get_plan_and_actual_rows(sql_query)) x
+$$ LANGUAGE sql;
+
+CREATE OR REPLACE FUNCTION check_estimate2(sql_query text, accuracy real)
+RETURNS TABLE(min integer, max integer) AS
+$$
+ SELECT (actual * (1 - accuracy))::integer, ((actual + 1) * (1 + accuracy))::integer
+ FROM (SELECT * FROM get_plan_and_actual_rows(sql_query)) x
+$$ LANGUAGE sql;
+
+CREATE TABLE jsonb_stats_test(js jsonb);
+
+INSERT INTO jsonb_stats_test SELECT NULL FROM generate_series(1, 1000);
+
+INSERT INTO jsonb_stats_test SELECT 'null' FROM generate_series(1, 200);
+INSERT INTO jsonb_stats_test SELECT 'true' FROM generate_series(1, 300);
+INSERT INTO jsonb_stats_test SELECT 'false' FROM generate_series(1, 500);
+
+INSERT INTO jsonb_stats_test SELECT '12345' FROM generate_series(1, 100);
+INSERT INTO jsonb_stats_test SELECT (1000 * (i % 10))::text::jsonb FROM generate_series(1, 400) i;
+INSERT INTO jsonb_stats_test SELECT i::text::jsonb FROM generate_series(1, 500) i;
+
+INSERT INTO jsonb_stats_test SELECT '"foo"' FROM generate_series(1, 100);
+INSERT INTO jsonb_stats_test SELECT format('"bar%s"', i % 10)::jsonb FROM generate_series(1, 400) i;
+INSERT INTO jsonb_stats_test SELECT format('"baz%s"', i)::jsonb FROM generate_series(1, 500) i;
+
+INSERT INTO jsonb_stats_test SELECT '{}' FROM generate_series(1, 100);
+INSERT INTO jsonb_stats_test SELECT jsonb_build_object('foo', 'bar') FROM generate_series(1, 100);
+INSERT INTO jsonb_stats_test SELECT jsonb_build_object('foo', 'baz' || (i % 10)) FROM generate_series(1, 300) i;
+INSERT INTO jsonb_stats_test SELECT jsonb_build_object('foo', i % 10) FROM generate_series(1, 200) i;
+INSERT INTO jsonb_stats_test SELECT jsonb_build_object('"foo \"bar"', i % 10) FROM generate_series(1, 200) i;
+
+INSERT INTO jsonb_stats_test SELECT '[]' FROM generate_series(1, 100);
+INSERT INTO jsonb_stats_test SELECT '["foo"]' FROM generate_series(1, 200);
+INSERT INTO jsonb_stats_test SELECT '[12345]' FROM generate_series(1, 300);
+INSERT INTO jsonb_stats_test SELECT '[["foo"]]' FROM generate_series(1, 200);
+INSERT INTO jsonb_stats_test SELECT '[{"key": "foo"}]' FROM generate_series(1, 200);
+INSERT INTO jsonb_stats_test SELECT '[null, "foo"]' FROM generate_series(1, 200);
+INSERT INTO jsonb_stats_test SELECT '[null, 12345]' FROM generate_series(1, 300);
+INSERT INTO jsonb_stats_test SELECT '[null, ["foo"]]' FROM generate_series(1, 200);
+INSERT INTO jsonb_stats_test SELECT '[null, {"key": "foo"}]' FROM generate_series(1, 200);
+
+-- Build random variable-length integer arrays
+SELECT setseed(0.0);
+
+INSERT INTO jsonb_stats_test
+SELECT jsonb_build_object('array',
+ jsonb_build_array())
+FROM generate_series(1, 1000);
+
+INSERT INTO jsonb_stats_test
+SELECT jsonb_build_object('array',
+ jsonb_build_array(
+ floor(random() * 10)::int))
+FROM generate_series(1, 4000);
+
+INSERT INTO jsonb_stats_test
+SELECT jsonb_build_object('array',
+ jsonb_build_array(
+ floor(random() * 10)::int,
+ floor(random() * 10)::int))
+FROM generate_series(1, 3000);
+
+INSERT INTO jsonb_stats_test
+SELECT jsonb_build_object('array',
+ jsonb_build_array(
+ floor(random() * 10)::int,
+ floor(random() * 10)::int,
+ floor(random() * 10)::int))
+FROM generate_series(1, 2000);
+
+
+ANALYZE jsonb_stats_test;
+
+CREATE OR REPLACE FUNCTION check_jsonb_stats_test_estimate(sql_condition text, accuracy real)
+RETURNS boolean AS
+$$
+ SELECT check_estimate('SELECT count(*) FROM jsonb_stats_test WHERE ' || sql_condition, accuracy)
+$$ LANGUAGE sql;
+
+DROP FUNCTION IF EXISTS check_jsonb_stats_test_estimate2(text, real);
+
+CREATE OR REPLACE FUNCTION check_jsonb_stats_test_estimate2(sql_condition text, accuracy real)
+RETURNS TABLE(plan integer, actual integer) AS
+$$
+ SELECT get_plan_and_actual_rows('SELECT count(*) FROM jsonb_stats_test WHERE ' || sql_condition)
+$$ LANGUAGE sql;
+
+-- Check NULL estimate
+SELECT check_jsonb_stats_test_estimate($$js IS NULL$$, 0.03);
+SELECT check_jsonb_stats_test_estimate($$js -> 'bad_key' IS NULL$$, 0.01);
+SELECT check_jsonb_stats_test_estimate($$js #> '{bad_key}' IS NULL$$, 0.01);
+SELECT check_jsonb_stats_test_estimate($$js -> 1000000 IS NULL$$, 0.01);
+SELECT check_jsonb_stats_test_estimate($$js #> '{1000000}' IS NULL$$, 0.01);
+SELECT check_jsonb_stats_test_estimate($$js -> 'bad_key1' -> 'bad_key2' IS NULL$$, 0.01);
+SELECT check_jsonb_stats_test_estimate($$js #> '{bad_key1,bad_key2}' IS NULL$$, 0.01);
+SELECT check_jsonb_stats_test_estimate($$js -> 'bad_key1' -> 1 IS NULL$$, 0.01);
+SELECT check_jsonb_stats_test_estimate($$js #> '{bad_key1,1}' IS NULL$$, 0.01);
+SELECT check_jsonb_stats_test_estimate($$js -> 1000000 -> 'foo' IS NULL$$, 0.01);
+SELECT check_jsonb_stats_test_estimate($$js #> '{1000000,foo}' IS NULL$$, 0.01);
+
+SELECT check_jsonb_stats_test_estimate($$js -> 'bad_key' = '123'$$, 0.01);
+SELECT check_jsonb_stats_test_estimate($$js -> 1000000 = '123'$$, 0.01);
+
+-- Check null eq estimate
+SELECT check_jsonb_stats_test_estimate($$js = 'null'$$, 0.1);
+SELECT check_jsonb_stats_test_estimate($$js @> 'null'$$, 0.1);
+
+-- Check boolean eq estimate
+SELECT check_jsonb_stats_test_estimate($$js = 'true'$$, 0.1);
+SELECT check_jsonb_stats_test_estimate($$js @> 'true'$$, 0.1);
+SELECT check_jsonb_stats_test_estimate($$js = 'false'$$, 0.1);
+SELECT check_jsonb_stats_test_estimate($$js @> 'false'$$, 0.1);
+
+-- Check numeric eq estimate
+SELECT check_jsonb_stats_test_estimate($$js = '12345'$$, 0.1);
+SELECT check_jsonb_stats_test_estimate($$js#>'{}' = '12345'$$, 0.1);
+SELECT check_jsonb_stats_test_estimate($$js = '3000'$$, 0.3);
+SELECT check_jsonb_stats_test_estimate($$js = '1234'$$, 1);
+SELECT check_jsonb_stats_test_estimate($$js @> '6000'$$, 0.2);
+
+-- Check numeric range estimate
+SELECT check_jsonb_stats_test_estimate($$js < '0'$$, 0.1);
+SELECT check_jsonb_stats_test_estimate($$js < '100'$$, 0.1);
+SELECT check_jsonb_stats_test_estimate($$js < '1000'$$, 0.1);
+SELECT check_jsonb_stats_test_estimate($$js < '3456'$$, 0.1);
+SELECT check_jsonb_stats_test_estimate($$js < '10000'$$, 0.1);
+SELECT check_jsonb_stats_test_estimate($$js < '100000'$$, 0.1);
+SELECT check_jsonb_stats_test_estimate($$js > '100' AND js < '600'$$, 0.5);
+SELECT check_jsonb_stats_test_estimate($$js > '6800' AND js < '12000'$$, 0.1);
+
+-- Check string eq estimate
+SELECT check_jsonb_stats_test_estimate($$js = '"foo"'$$, 0.1);
+SELECT check_jsonb_stats_test_estimate($$js = '"bar7"'$$, 0.2);
+SELECT check_jsonb_stats_test_estimate($$js = '"baz1234"'$$, 10);
+SELECT check_jsonb_stats_test_estimate($$js @> '"bar4"'$$, 0.3);
+
+-- Check string range estimate
+SELECT check_jsonb_stats_test_estimate($$js > '"foo"'$$, 0.01);
+SELECT check_jsonb_stats_test_estimate($$js > '"bar"'$$, 0.01);
+SELECT check_jsonb_stats_test_estimate($$js > '"baz"'$$, 0.01);
+
+-- Check object eq estimate
+SELECT check_jsonb_stats_test_estimate($$js = '{}'$$, 0.1);
+SELECT check_jsonb_stats_test_estimate($$js > '{}'$$, 0.1);
+
+-- Check object key eq estimate
+SELECT check_jsonb_stats_test_estimate($$js -> 'foo' = '"bar"'$$, 0.2);
+SELECT check_jsonb_stats_test_estimate($$js -> 'foo' = '"baz"'$$, 0.1);
+SELECT check_jsonb_stats_test_estimate($$js -> 'foo' = '"baz5"'$$, 0.3);
+SELECT check_jsonb_stats_test_estimate($$js #> '{foo}' = '"bar"'$$, 0.2);
+
+-- Check object key range estimate
+SELECT check_jsonb_stats_test_estimate($$js -> 'foo' >= '"baz2"'$$, 0.1);
+SELECT check_jsonb_stats_test_estimate($$js -> 'foo' < '"baz9"'$$, 0.1);
+SELECT check_jsonb_stats_test_estimate($$js -> 'foo' >= '"baz2"' AND js -> 'foo' < '"baz9"'$$, 0.1);
+
+-- Check array eq estimate
+SELECT check_jsonb_stats_test_estimate($$js = '[]'$$, 0.1);
+SELECT check_jsonb_stats_test_estimate($$js >= '[]' AND js < '{}'$$, 0.1);
+
+-- Check variable-length array element eq estimate
+SELECT check_jsonb_stats_test_estimate($$js -> 'array' -> 0 = '1'$$, 0.2);
+SELECT check_jsonb_stats_test_estimate($$js -> 'array' -> 1 = '6'$$, 0.2);
+SELECT check_jsonb_stats_test_estimate($$js -> 'array' -> 2 = '8'$$, 0.2);
+SELECT check_jsonb_stats_test_estimate($$js -> 'array' -> 3 = '1'$$, 0.2);
+
+-- Check variable-length array element range estimate
+SELECT check_jsonb_stats_test_estimate($$js -> 'array' -> 0 < '7'$$, 0.1);
+SELECT check_jsonb_stats_test_estimate($$js -> 'array' -> 1 < '7'$$, 0.1);
+SELECT check_jsonb_stats_test_estimate($$js -> 'array' -> 2 < '7'$$, 0.1);
+SELECT check_jsonb_stats_test_estimate($$js -> 'array' -> 3 < '7'$$, 0.1);
+
+-- Check variable-length array containment estimate
+SELECT check_jsonb_stats_test_estimate($$js -> 'array' @> '[]'$$, 0.2);
+SELECT check_jsonb_stats_test_estimate($$js -> 'array' @> '[1]'$$, 0.2);
+SELECT check_jsonb_stats_test_estimate($$js -> 'array' @> '[100]'$$, 0.2);
+SELECT check_jsonb_stats_test_estimate($$js -> 'array' @> '[1, 2]'$$, 1);
+SELECT check_jsonb_stats_test_estimate($$js -> 'array' @> '[1, 100]'$$, 1);
+SELECT check_jsonb_stats_test_estimate($$js -> 'array' @> '[1, 2, 100]'$$, 1);
+SELECT check_jsonb_stats_test_estimate($$js -> 'array' @> '[1, 2, 3]'$$, 5);
+
+SELECT check_jsonb_stats_test_estimate($$js -> 'array' @> '1'$$, 0.3);
+SELECT check_jsonb_stats_test_estimate($$js -> 'array' @> '100'$$, 10);
+
+SELECT check_jsonb_stats_test_estimate($$js -> 'array' -> 0 @> '1'$$, 0.3);
+SELECT check_jsonb_stats_test_estimate($$js -> 'array' -> 1 @> '1'$$, 0.3);
+SELECT check_jsonb_stats_test_estimate($$js -> 'array' -> 2 @> '1'$$, 0.3);
+SELECT check_jsonb_stats_test_estimate($$js -> 'array' -> 3 @> '1'$$, 0.3);
+SELECT check_jsonb_stats_test_estimate($$js -> 'array' -> 0 @> '[1]'$$, 0.3);
+
+SELECT check_jsonb_stats_test_estimate($$js @> '{"array": []}'$$, 0.1);
+SELECT check_jsonb_stats_test_estimate($$js @> '{"array": [1]}'$$, 0.3);
+SELECT check_jsonb_stats_test_estimate($$js @> '{"array": [100]}'$$, 0.3);
+SELECT check_jsonb_stats_test_estimate($$js @> '{"array": [1, 2]}'$$, 1);
+SELECT check_jsonb_stats_test_estimate($$js @> '{"array": [1, 100]}'$$, 1);
+SELECT check_jsonb_stats_test_estimate($$js @> '{"array": [1, 2, 100]}'$$, 1);
+SELECT check_jsonb_stats_test_estimate($$js @> '{"array": [1, 2, 3]}'$$, 100);
+
+-- check misc containment
+SELECT check_jsonb_stats_test_estimate($$js @> '"foo"'$$, 0.1);
+SELECT check_jsonb_stats_test_estimate($$js @> '12345'$$, 0.1);
+SELECT check_jsonb_stats_test_estimate($$js @> '[]'$$, 0.1);
+SELECT check_jsonb_stats_test_estimate($$js @> '[12345]'$$, 0.1);
+SELECT check_jsonb_stats_test_estimate($$js @> '["foo"]'$$, 0.1);
+SELECT check_jsonb_stats_test_estimate($$js @> '[["foo", "bar"]]'$$, 0.1);
+SELECT check_jsonb_stats_test_estimate($$js @> '[["foo"]]'$$, 0.2);
+SELECT check_jsonb_stats_test_estimate($$js @> '[{"key": "foo"}]'$$, 0.1);
+SELECT check_jsonb_stats_test_estimate($$js @> '[null]'$$, 0.3);
+
+-- Check object key null estimate
+SELECT check_jsonb_stats_test_estimate($$js -> 'foo' IS NULL$$, 0.1);
+SELECT check_jsonb_stats_test_estimate($$js -> 'foo' IS NOT NULL$$, 0.1);
+SELECT check_jsonb_stats_test_estimate($$js -> '"foo \"bar"' IS NOT NULL$$, 0.1);
+SELECT check_jsonb_stats_test_estimate($$js -> 'bad_key' IS NULL$$, 0.01);
+SELECT check_jsonb_stats_test_estimate($$js -> 'bad_key' IS NOT NULL$$, 0.01);
+
+-- Check object key existence
+SELECT check_jsonb_stats_test_estimate($$js ? 'bad_key'$$, 10);
+SELECT check_jsonb_stats_test_estimate($$js ? 'foo'$$, 0.1);
+SELECT check_jsonb_stats_test_estimate($$js ? 'array'$$, 0.1);
+
+SELECT check_jsonb_stats_test_estimate($$js ?| '{foo,bad_key}'$$, 0.1);
+SELECT check_jsonb_stats_test_estimate($$js ?| '{foo,array}'$$, 0.1);
+SELECT check_jsonb_stats_test_estimate($$js ?& '{foo,bad_key}'$$, 0.1);
+SELECT check_jsonb_stats_test_estimate($$js ?& '{foo,bar}'$$, 0.1);
--
2.31.1
view thread (3+ messages)
reply
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: [email protected]
Cc: [email protected], [email protected], [email protected], [email protected], [email protected], [email protected]
Subject: Re: Collecting statistics about contents of JSONB columns
In-Reply-To: <[email protected]>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by agora; see mirroring instructions
for how to clone and mirror all data and code used for this inbox