public inbox for [email protected]
help / color / mirror / Atom feedFrom: Tomas Vondra <[email protected]>
To: PostgreSQL Hackers <[email protected]>
Cc: Nikita Glukhov <[email protected]>
Cc: Mahendra Thalor <[email protected]>
Cc: Oleg Bartunov <[email protected]>
Subject: Collecting statistics about contents of JSONB columns
Date: Fri, 31 Dec 2021 23:06:42 +0100
Message-ID: <[email protected]> (raw)
Hi,
One of the complaints I sometimes hear from users and customers using
Postgres to store JSON documents (as JSONB type, of course) is that the
selectivity estimates are often pretty poor.
Currently we only really have MCV and histograms with whole documents,
and we can deduce some stats from that. But that is somewhat bogus
because there's only ~100 documents in either MCV/histogram (with the
default statistics target). And moreover we discard all "oversized"
values (over 1kB) before even calculating those stats, which makes it
even less representative.
A couple weeks ago I started playing with this, and I experimented with
improving extended statistics in this direction. After a while I noticed
a forgotten development branch from 2016 which tried to do this by
adding a custom typanalyze function, which seemed like a more natural
idea (because it's really a statistics for a single column).
But then I went to pgconf NYC in early December, and I spoke to Oleg
about various JSON-related things, and he mentioned they've been working
on this topic some time ago too, but did not have time to pursue it. So
he pointed me to a branch [1] developed by Nikita Glukhov.
I like Nikita's branch because it solved a couple architectural issues
that I've been aware of, but only solved them in a rather hackish way.
I had a discussion with Nikita about his approach what can we do to move
it forward. He's focusing on other JSON stuff, but he's OK with me
taking over and moving it forward. So here we go ...
Nikita rebased his branch recently, I've kept improving it in various
(mostly a lot of comments and docs, and some minor fixes and tweaks).
I've pushed my version with a couple extra commits in [2], but you can
ignore that except if you want to see what I added/changed.
Attached is a couple patches adding adding the main part of the feature.
There's a couple more commits in the github repositories, adding more
advanced features - I'll briefly explain those later, but I'm not
including them here because those are optional features and it'd be
distracting to include them here.
There are 6 patches in the series, but the magic mostly happens in parts
0001 and 0006. The other parts are mostly just adding infrastructure,
which may be a sizeable amount of code, but the changes are fairly
simple and obvious. So let's focus on 0001 and 0006.
To add JSON statistics we need to do two basic things - we need to build
the statistics and then we need to allow using them while estimating
conditions.
1) building stats
Let's talk about building the stats first. The patch does one of the
things I experimented with - 0006 adds a jsonb_typanalyze function, and
it associates it with the data type. The function extracts paths and
values from the JSONB document, builds the statistics, and then stores
the result in pg_statistic as a new stakind.
I've been planning to store the stats in pg_statistic too, but I've been
considering to use a custom data type. The patch does something far more
elegant - it simply uses stavalues to store an array of JSONB documents,
each describing stats for one path extracted from the sampled documents.
One (very simple) element of the array might look like this:
{"freq": 1,
"json": {
"mcv": {
"values": [0, 1, 2, 3, 4, 5, 6, 7, 8, 9],
"numbers": [0.1, 0.1, 0.1, 0.1, 0.1, 0.1, 0.1, 0.1, 0.1, 0.1]},
"width": 19,
"distinct": 10,
"nullfrac": 0,
"correlation": 0.10449},
"path": "$.\"a\"",
"freq_null": 0, "freq_array": 0, "freq_object": 0,
"freq_string": 0, "freq_boolean": 0, "freq_numeric": 0}
In this case there's only a MCV list (represented by two arrays, just
like in pg_statistic), but there might be another part with a histogram.
There's also the other columns we'd expect to see in pg_statistic.
In principle, we need pg_statistic for each path we extract from the
JSON documents and decide it's interesting enough for estimation. There
are probably other ways to serialize/represent this, but I find using
JSONB for this pretty convenient because we need to deal with a mix of
data types (for the same path), and other JSON specific stuff. Storing
that in Postgres arrays would be problematic.
I'm sure there's plenty open questions - for example I think we'll need
some logic to decide which paths to keep, otherwise the statistics can
get quite big, if we're dealing with large / variable documents. We're
already doing something similar for MCV lists.
One of Nikita's patches not included in this thread allow "selective"
statistics, where you can define in advance a "filter" restricting which
parts are considered interesting by ANALYZE. That's interesting, but I
think we need some simple MCV-like heuristics first anyway.
Another open question is how deep the stats should be. Imagine documents
like this:
{"a" : {"b" : {"c" : {"d" : ...}}}}
The current patch build stats for all possible paths:
"a"
"a.b"
"a.b.c"
"a.b.c.d"
and of course many of the paths will often have JSONB documents as
values, not simple scalar values. I wonder if we should limit the depth
somehow, and maybe build stats only for scalar values.
2) applying the statistics
One of the problems is how to actually use the statistics. For @>
operator it's simple enough, because it's (jsonb @> jsonb) so we have
direct access to the stats. But often the conditions look like this:
jsonb_column ->> 'key' = 'value'
so the condition is actually on an expression, not on the JSONB column
directly. My solutions were pretty ugly hacks, but Nikita had a neat
idea - we can define a custom procedure for each operator, which is
responsible for "calculating" the statistics for the expression.
So in this case "->>" will have such "oprstat" procedure, which fetches
stats for the JSONB column, extracts stats for the "key" path. And then
we can use that for estimation of the (text = text) condition.
This is what 0001 does, pretty much. We simply look for expression stats
provided by an index, extended statistics, and then - if oprstat is
defined for the operator - we try to derive the stats.
This opens other interesting opportunities for the future - one of the
parts adds oprstat for basic arithmetic operators, which allows deducing
statistics for expressions like (a+10) from statistics on column (a).
Which seems like a neat feature on it's own, but it also interacts with
the extended statistics in somewhat non-obvious ways (especially when
estimating GROUP BY cardinalities).
Of course, there's a limit of what we can reasonably estimate - for
example, there may be statistical dependencies between paths, and this
patch does not even attempt to deal with that. In a way, this is similar
to correlation between columns, except that here we have a dynamic set
of columns, which makes it much much harder. We'd need something like
extended stats on steroids, pretty much.
I'm sure I've forgotten various important bits - many of them are
mentioned or explained in comments, but I'm sure others are not. And I'd
bet there are things I forgot about entirely or got wrong. So feel free
to ask.
In any case, I think this seems like a good first step to improve our
estimates for JSOB columns.
regards
[1] https://github.com/postgrespro/postgres/tree/jsonb_stats
[2] https://github.com/tvondra/postgres/tree/jsonb_stats_rework
--
Tomas Vondra
EnterpriseDB: http://www.enterprisedb.com
The Enterprise PostgreSQL Company
Attachments:
[text/x-patch] 0001-Add-pg_operator.oprstat-for-derived-operato-20211230.patch (12.3K, ../[email protected]/2-0001-Add-pg_operator.oprstat-for-derived-operato-20211230.patch)
download | inline diff:
From 7852524c50239904c7267e149b7fbe728778692b Mon Sep 17 00:00:00 2001
From: Nikita Glukhov <[email protected]>
Date: Sat, 12 Nov 2016 18:59:43 +0300
Subject: [PATCH 01/10] 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-20211230.patch (3.9K, ../[email protected]/3-0002-Add-stats_form_tuple-20211230.patch)
download | inline diff:
From 70ec4b49544c62ab1c701a90f4c60ce2e3c717c2 Mon Sep 17 00:00:00 2001
From: Nikita Glukhov <[email protected]>
Date: Wed, 7 Dec 2016 16:12:55 +0300
Subject: [PATCH 02/10] 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-20211230.patch (7.1K, ../[email protected]/4-0003-Add-symbolic-names-for-some-jsonb-operators-20211230.patch)
download | inline diff:
From 487adb560118d413eb4a9a8ec24a2c3f7ebcfcc1 Mon Sep 17 00:00:00 2001
From: Nikita Glukhov <[email protected]>
Date: Sat, 12 Nov 2016 18:59:43 +0300
Subject: [PATCH 03/10] 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-20211230.patch (9.7K, ../[email protected]/5-0004-Add-helper-jsonb-functions-and-macros-20211230.patch)
download | inline diff:
From 32297b9ad2736a52e32cc22f6c729f89f095983a Mon Sep 17 00:00:00 2001
From: Nikita Glukhov <[email protected]>
Date: Fri, 16 Dec 2016 17:16:47 +0300
Subject: [PATCH 04/10] 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-20211230.patch (1.5K, ../[email protected]/6-0005-Export-scalarineqsel-20211230.patch)
download | inline diff:
From c74ae4213a710e6abbcb332ef333e158cb1196c3 Mon Sep 17 00:00:00 2001
From: Nikita Glukhov <[email protected]>
Date: Sat, 12 Nov 2016 19:01:43 +0300
Subject: [PATCH 05/10] 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-20211230.patch (121.4K, ../[email protected]/7-0006-Add-jsonb-statistics-20211230.patch)
download | inline diff:
From 4c77b47d8b4dbed22ed24d00528162b4025de81d Mon Sep 17 00:00:00 2001
From: Nikita Glukhov <[email protected]>
Date: Sat, 12 Nov 2016 19:19:33 +0300
Subject: [PATCH 06/10] 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..51f132637a5 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..39f23506cb2
--- /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..e3849ec3076
--- /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..3c66c8c0ed8
--- /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 (4+ messages) latest in thread
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]
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