Received: from malur.postgresql.org ([217.196.149.56]) by arkaria.postgresql.org with esmtps (TLS1.3:ECDHE_RSA_AES_256_GCM_SHA384:256) (Exim 4.92) (envelope-from ) id 1nSin8-0003xr-PJ for pgsql-hackers@arkaria.postgresql.org; Fri, 11 Mar 2022 17:11:03 +0000 Received: from localhost ([127.0.0.1] helo=malur.postgresql.org) by malur.postgresql.org with esmtp (Exim 4.92) (envelope-from ) id 1nSin7-0002GS-4Y for pgsql-hackers@arkaria.postgresql.org; Fri, 11 Mar 2022 17:11:01 +0000 Received: from makus.postgresql.org ([2001:4800:3e1:1::229]) by malur.postgresql.org with esmtps (TLS1.3:ECDHE_RSA_AES_256_GCM_SHA384:256) (Exim 4.92) (envelope-from ) id 1nSin6-0002GJ-CH for pgsql-hackers@lists.postgresql.org; Fri, 11 Mar 2022 17:11:00 +0000 Received: from mail-ed1-x536.google.com ([2a00:1450:4864:20::536]) by makus.postgresql.org with esmtps (TLS1.3:ECDHE_RSA_AES_128_GCM_SHA256:128) (Exim 4.92) (envelope-from ) id 1nSin3-0005OH-Hg for pgsql-hackers@lists.postgresql.org; Fri, 11 Mar 2022 17:10:59 +0000 Received: by mail-ed1-x536.google.com with SMTP id y8so6613997edl.9 for ; Fri, 11 Mar 2022 09:10:57 -0800 (PST) DKIM-Signature: v=1; a=rsa-sha256; c=relaxed/relaxed; d=gmail.com; s=20210112; h=mime-version:references:in-reply-to:from:date:message-id:subject:to :cc; bh=DbgA4WESsnzpGj7iwehFKFOaH1iVbjhkPnRbYKV8hHs=; b=aoymKi7p3x3PTxqdxhj2vxa/XEzywPb6KxMg9fMN/zVq8L6O2BtXdyHnk6ibcj+3Nh 9mN/xILIb4sTBHqQbYAPwhIZcjNmrcm3tvTiLMDCnj3v9mTG6s6cgScjWghwSJz2x8Wg gecelCu0m3960iJWeZjPBh6lsve99OtHcqMPR/UlV50Cf5fNhL8FkjpbFt8/0Rf3aT7z f+WFEGmiCk69HEoU5ATkhhbXInn/DVA5BEnvJxpP2Xz9FPKXBf3snx0P8FUU/+CZ1ima SiE3TIzmJ3s40PqDz3V+cqmxvr06xwwwpq2omvNn1GdbbtBemOTNTnj6KgbDG5fQGJ3g 770g== X-Google-DKIM-Signature: v=1; a=rsa-sha256; c=relaxed/relaxed; d=1e100.net; s=20210112; h=x-gm-message-state:mime-version:references:in-reply-to:from:date :message-id:subject:to:cc; bh=DbgA4WESsnzpGj7iwehFKFOaH1iVbjhkPnRbYKV8hHs=; b=yfCKHC3WUIz1k5Hg1uqzKFYzdLJnqmKMdjnNLvtVTi24N3Jhj0AW53+5QFIOLVJJok 6cYrDBmBYLBurIr8KWD1LshNILMmhojxhRZR/Jt1evGhVAV+xKbBHjDOAaLPHNNIdAI/ j6kNdntEmo2/LTamyBH3re/sobz/mqCzZv9nn13bimFw/9+9n/QqN0zk/VnEGaR9iTLU FJ9valQLn7rOoVIPlLOMNfMSjEmrZyoOcVykwKOkWVAwgm4KDLyK/eOilj06cdyE4tCu i0nHZ+LsYMikdxamuEoU9Gg6L10gHyuVEkKM086+ajntJcvWHORePo3vWiqOX6tBZxpC TXfg== X-Gm-Message-State: AOAM530PtNEmM++8R9uWfq81lwkBJrVt7BgfgItIoBo6OCMMCJPa+O/8 JwUvBifJp2bmlnHVs5CNI+XOGZTAeKNhvi4CcWg= X-Google-Smtp-Source: ABdhPJz5CBwXLjyYfnIiNB8Lt7JeYmbnwhuA/B3Ufp0/U2X6BF7o3dWCwQRPlSY/eDx7CJgb7ooXfSDF9z85vzbOhfM= X-Received: by 2002:a05:6402:290f:b0:416:537b:7a2d with SMTP id ee15-20020a056402290f00b00416537b7a2dmr9525236edb.381.1647018655623; Fri, 11 Mar 2022 09:10:55 -0800 (PST) MIME-Version: 1.0 References: <15d637bb-900e-e67b-361b-d70a79c324d7@enterprisedb.com> <58afb242-183b-226b-d8ba-f83374fd2bb5@enterprisedb.com> In-Reply-To: <58afb242-183b-226b-d8ba-f83374fd2bb5@enterprisedb.com> From: Mahendra Singh Thalor Date: Fri, 11 Mar 2022 22:40:43 +0530 Message-ID: Subject: Re: Collecting statistics about contents of JSONB columns To: Tomas Vondra Cc: Nikita Glukhov , PostgreSQL Hackers , Mahendra Thalor , Oleg Bartunov , Thomas Munro Content-Type: multipart/alternative; boundary="000000000000e82e6e05d9f469f4" List-Id: List-Help: List-Subscribe: List-Post: List-Owner: List-Archive: Archived-At: Precedence: bulk --000000000000e82e6e05d9f469f4 Content-Type: text/plain; charset="UTF-8" Content-Transfer-Encoding: quoted-printable On Fri, 4 Feb 2022 at 08:30, Tomas Vondra wrote: > > > > On 2/4/22 03:47, Tomas Vondra wrote: > > ./json-generate.py 30000 2 8 1000 6 1000 > > Sorry, this should be (different order of parameters): > > ./json-generate.py 30000 2 1000 8 6 1000 > Thanks, Tomas for this test case. Hi Hackers, For the last few days, I was doing testing on the top of these JSON optimizers patches and was taking help fro Tomas Vondra to understand patches and testing results. Thanks, Tomas for your feedback and suggestions. Below is the summary: *Point 1)* analyse is taking very much time for large documents: For large JSON documents, analyze took very large time as compared to the current head. For reference, I am attaching test file (./json-generate.py 30000 2 1000 8 6 1000) Head: analyze test ; Time: 120.864 ms With patch: analyze test ; Time: more than 2 hours analyze is taking a very large time because with these patches, firstly we iterate over all sample rows (in above case 30000), and we store all the paths (here around 850k paths). In another pass, we took 1 path at a time and collects stats for the particular path by analyzing all the sample rows and we continue this process for all 850k paths or we can say that we do 850k loops, and in each loop we extract values for a single path. *Point 2)* memory consummation increases rapidly for large documents: In the above test case, there are total 851k paths and to keep stats for one path, we allocate 1120 bytes. Total paths : 852689 ~ 852k Memory for 1 path to keep stats: 1120 ~ 1 KB (sizeof(JsonValueStats) =3D 1120 from =E2=80=9CAnalyze Column=E2=80=9D) Total memory for all paths: 852689 * 1120 =3D 955011680 ~ 955 MB Extra memory for each path will be more. I mean, while analyzing each path, we allocate some more memory based on frequency and others To keep all entries(851k paths) in the hash, we use around 1GB memory for hash so this is also very large. *Point 3*) Review comment noticed by Tomas Vondra: + oldcxt =3D MemoryContextSwitchTo(ctx->stats->anl_context); + pstats->stats =3D jsonAnalyzeBuildPathStats(pstats); + MemoryContextSwitchTo(oldcxt); Above should be: + oldcxt =3D MemoryContextSwitchTo(ctx->mcxt); + pstats->stats =3D jsonAnalyzeBuildPathStats(pstats); + MemoryContextSwitchTo(oldcxt); *Response from Tomas Vondra:* The problem is "anl_context" is actually "Analyze", i.e. the context for the whole ANALYZE command, for all the columns. But we only want to keep those path stats while processing a particular column. At the end, after processing all paths from a column, we need to "build" the final stats in the column, and this result needs to go into "Analyze" context. But all the partial results need to go into "Analyze Column" context. *Point 4)* +/* + * jsonAnalyzeCollectPath + * Extract a single path from JSON documents and collect its values. + */ +static void +jsonAnalyzeCollectPath(JsonAnalyzeContext *ctx, Jsonb *jb, void *param) +{ + JsonPathAnlStats *pstats =3D (JsonPathAnlStats *) param; + JsonbValue jbvtmp; + JsonbValue *jbv =3D JsonValueInitBinary(&jbvtmp, jb); + JsonPathEntry *path; + JsonPathEntry **entries; + int i; + + entries =3D palloc(sizeof(*entries) * pstats->depth); + + /* Build entry array in direct order */ + for (path =3D &pstats->path, i =3D pstats->depth - 1; + path->parent && i >=3D 0; + path =3D path->parent, i--) + entries[i] =3D path; + + jsonAnalyzeCollectSubpath(ctx, pstats, jbv, entries, 0); + + pfree(entries); ----many times, we are trying to palloc with zero size and entries is pointing to invalid memory (because pstats->depth=3D0) so I think, we shoul= d not try to palloc with 0?? *Fix:* + If (pstats->depth) + entries =3D palloc(sizeof(*entries) * pstats->depth); From these points, we can say that we should rethink our design to collect stats for all paths. We can set limits(like MCV) for paths or we can give an explicit path to collect stats for a particular path only or we can pass a subset of the JSON values. In the above case, there are total 851k paths, but we can collect stats for only 1000 paths that are most common so this way we can minimize time and memory also and we might even keep at least frequencies for the non-analyzed paths. Next, I will take the latest patches from Nikita's last email and I will do more tests. Thanks and Regards Mahendra Singh Thalor EnterpriseDB: http://www.enterprisedb.com --000000000000e82e6e05d9f469f4 Content-Type: text/html; charset="UTF-8" Content-Transfer-Encoding: quoted-printable
On Fri, 4 Feb 2022 at 08:30, Tomas Vondra <tomas.vondra@enterprisedb.com> = wrote:
>
>
>
> On 2/4/22 03:47, Tomas Vondra wrote:=
> > ./json-generate.py 30000 2 8 1000 6 1000
>
> Sorr= y, this should be (different order of parameters):
>
> ./json-g= enerate.py 30000 2 1000 8 6 1000
>

Thanks, Tomas for this test= case.

Hi Hackers,

For the last few days, I was doing te= sting on the top of these JSON optimizers patches and was taking help fro T= omas Vondra to understand patches and testing results.
Thanks, Tomas f= or your feedback and suggestions.

Below is the summary:
Point = 1) analyse is taking very much time for large documents:
For large J= SON documents, analyze took very large time as compared to the current head= . For reference, I am attaching test file (./json-generate.py 30000 2 1000 = 8 6 1000)

Head: analyze test ; Time: 120.864 ms
With patch: analy= ze test ; Time: more than 2 hours

analyze is taking a very large tim= e because with these patches, firstly we iterate over all sample rows (in a= bove case 30000), and we store all the paths (here around 850k paths).
<= div>In another pass, we took 1 path at a time and collects stats for the pa= rticular path by analyzing all the sample rows and we continue this process= for all 850k paths or we can say that we do 850k loops, and in each loop w= e extract values for a single path.

Point 2) memory consumm= ation increases rapidly for large documents:
In the above test case, the= re are total 851k paths and to keep stats for one path, we allocate 1120 by= tes.

Total paths : 852689 ~ 852k

Memory for 1 path= to keep stats: 1120 ~ 1 KB

(sizeof(JsonValueStats) =3D 1120 from = =E2=80=9CAnalyze Column=E2=80=9D)

Total memory for all paths: 852689= * 1120 =3D 955011680 ~ 955 MB

Extra memory for each path will be mo= re. I mean, while analyzing each path, we allocate some more memory based o= n frequency and others

To keep all entries(851k paths) in the hash, = we use around 1GB memory for hash so this is also very large.

Poi= nt 3) Review comment noticed by Tomas Vondra:

+ =C2=A0 =C2=A0 = =C2=A0 oldcxt =3D MemoryContextSwitchTo(ctx->stats->anl_context);
= + =C2=A0 =C2=A0 =C2=A0 pstats->stats =3D jsonAnalyzeBuildPathStats(pstat= s);
+ =C2=A0 =C2=A0 =C2=A0 MemoryContextSwitchTo(oldcxt);

Above s= hould be:
+ =C2=A0 =C2=A0 =C2=A0 oldcxt =3D MemoryContextSwitchTo(ctx-&g= t;mcxt);
+ =C2=A0 =C2=A0 =C2=A0 pstats->stats =3D jsonAnalyzeBuildPat= hStats(pstats);
+ =C2=A0 =C2=A0 =C2=A0 MemoryContextSwitchTo(oldcxt);

Response from Tomas Vondra:
The problem is &quo= t;anl_context" is actually "Analyze", i.e. the context for t= he whole ANALYZE command, for all the columns. But we only want to keep those path st= ats while processing a particular column. At the end, after processing all paths from= a column, we need to "build" the final stats in the column, and = this result needs to go into "Analyze" context. But all the partial results= need to go into "Analyze Column" context.

Point 4)

+/*

+ * jsonAnalyzeCollectPath

+ * = =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0=C2=A0=C2=A0 Extract a sing= le path from JSON documents and collect its values.

+ */=

+static void

+jsonAnalyzeCollectPath(JsonAnalyzeContext *ctx,= Jsonb *jb, void *param)

+{

+ =C2=A0 =C2=A0=C2= =A0=C2=A0 JsonPathAnlStats *pstats =3D (JsonPathAnlStats *) param;

+ =C2=A0 =C2=A0=C2=A0=C2=A0 JsonbValue=C2=A0 = = =C2=A0=C2=A0=C2=A0 jbvtmp;

+ =C2=A0 =C2=A0=C2=A0=C2= =A0 JsonbValue *jbv =3D JsonValueInitBinary(&jbvtmp, jb);=

+ =C2=A0 =C2=A0=C2=A0=C2=A0 JsonPathEntry *path;

+ =C2=A0 =C2=A0=C2=A0=C2=A0 JsonPathEntry **entries;=

+ =C2=A0 =C2=A0=C2=A0=C2=A0 int =C2=A0 =C2=A0 =C2=A0 =C2= =A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0=C2=A0=C2=A0 i;<= /p>

+

+ =C2=A0 =C2=A0=C2=A0=C2=A0 entries =3D palloc= (sizeof(*entries) * pstats->depth);

+

+ =C2=A0 =C2=A0=C2=A0=C2=A0 /* Build entry array in direct order */<= /p>

+ =C2=A0 =C2=A0=C2=A0=C2=A0 for (path =3D &pstats->= path, i =3D pstats->depth - 1;

+=C2=A0 =C2=A0 =C2=A0 =C2=A0 = =C2=A0 =C2=A0 =C2=A0=C2=A0=C2=A0 path->parent && i= >=3D 0;

+=C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2= =A0=C2=A0=C2=A0 path =3D path->parent, i--)

+ =C2=A0 = =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0=C2=A0=C2=A0 entries[i] =3D= path;

+

+ =C2=A0 =C2=A0=C2=A0=C2=A0 json= AnalyzeCollectSubpath(ctx, pstats, jbv, entries, 0);

+

+ =C2=A0 =C2=A0=C2=A0=C2=A0 pfree(entries);

----ma= ny times, we are trying to palloc with zero size and entries is pointing to= invalid memory (because pstats->depth=3D0) so I think, we should not tr= y to palloc with 0??


Fix:

+ =C2=A0 =C2=A0 =C2=A0 If (pstats-= >depth)

=C2=A0=C2=A0+=C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0= entries =3D palloc(sizeof(*entries) * pstats->depth);


From these points, we can say that we should= rethink our design to collect stats for all paths.

We can set limit= s(like MCV) for paths or we can give an explicit path to collect stats for = a particular path only or we can pass a subset of the JSON values.

I= n the above case, there are total 851k paths, but we can collect stats for = only 1000 paths that are most common so this way we can minimize time and m= emory also and we might even keep at
least frequencies for the non-analyzed paths.

Next, = I will take the latest patches from Nikita's last email and I will do m= ore tests.

Thanks and Regards
Mahendra Singh Thalor<= br>EnterpriseDB: http://www.enterpr= isedb.com

--000000000000e82e6e05d9f469f4--