Received: from malur.postgresql.org ([217.196.149.56]) by arkaria.postgresql.org with esmtps (TLS1.2:ECDHE_RSA_AES_256_CBC_SHA1:256) (Exim 4.89) (envelope-from ) id 1iktPq-0001S4-JD for pgsql-bugs@arkaria.postgresql.org; Fri, 27 Dec 2019 17:28:46 +0000 Received: from localhost ([127.0.0.1] helo=malur.postgresql.org) by malur.postgresql.org with esmtp (Exim 4.89) (envelope-from ) id 1iktPo-0001Rg-Sx for pgsql-bugs@arkaria.postgresql.org; Fri, 27 Dec 2019 17:28:44 +0000 Received: from makus.postgresql.org ([2001:4800:3e1:1::229]) by malur.postgresql.org with esmtps (TLS1.2:ECDHE_RSA_AES_256_CBC_SHA1:256) (Exim 4.89) (envelope-from ) id 1iktPo-0001RZ-Ai for pgsql-bugs@lists.postgresql.org; Fri, 27 Dec 2019 17:28:44 +0000 Received: from mail-qk1-x742.google.com ([2607:f8b0:4864:20::742]) by makus.postgresql.org with esmtps (TLS1.3:ECDHE_RSA_AES_128_GCM_SHA256:128) (Exim 4.92) (envelope-from ) id 1iktPk-0004Oh-Vu for pgsql-bugs@lists.postgresql.org; Fri, 27 Dec 2019 17:28:43 +0000 Received: by mail-qk1-x742.google.com with SMTP id r14so21937989qke.13 for ; Fri, 27 Dec 2019 09:28:40 -0800 (PST) DKIM-Signature: v=1; a=rsa-sha256; c=relaxed/relaxed; d=gmail.com; s=20161025; h=from:to:cc:references:in-reply-to:subject:date:message-id :mime-version:thread-index:content-language; bh=VVfCc8wZaWHvlIrBJY/kZLHh0UVfs5CSHRin9S05diM=; b=KLUfGpRQoYJ8qhEfZYymRy3QnW9cWjsJgifHjjmf7F4eBS1HU4wVTu2/F6PJlecx81 AZdElnM1Y2OxLiW1hLG8EOFvNE7amKZhSA/9oNnzz3tr1HIFd2UY69n1EgdqtCEn9RIo mz+D5HXkOQ1RMsa2j+EbC7txf0UaSoBNqWhhBubyJY2DQ3jCVB4K0kfyY+/zDFYMP1hR sXGJoM/dxPT4gxA0rL4jkWrUv3LctDlwJ7QH6+2BUwu2wyUXF9s1nBRtU9y21kWorzDu JBLxKeaD3Z6bjDo7X1ALVP4pbPYB9H+gTSnPbUlYIlLT9v9DsaWNvM0NFBg2m799kolA EkJQ== X-Google-DKIM-Signature: v=1; a=rsa-sha256; c=relaxed/relaxed; d=1e100.net; s=20161025; h=x-gm-message-state:from:to:cc:references:in-reply-to:subject:date :message-id:mime-version:thread-index:content-language; bh=VVfCc8wZaWHvlIrBJY/kZLHh0UVfs5CSHRin9S05diM=; b=cxlQ/vFChcizYJQ2j+fpJ+tMsQYv26TdxMqFsU8zYfA95RQeJGF43afFprnirMmE1A fMbnuFZvaALvKUCYUiMO06sq4etIFCaSYEBMit5LfQcAyEbfU0khcaRvENFZu0t8kyHb vBmPiZdczQLO+2vMTd5glI6YzNLRc2IdDwTJ0bI+18Af5Wewoj575UQhFdtsf6y6AKAk KWjcvqpRG3XPpdQWUpelyebE+j6xMe2akSuzkLSAgRyrhJoeuQNYuqzzDi89Z29jPq0C vLuSd/owkQKnhuje+jt7zYnEYl6DZlaqTA2lcyjqba86nbVcn1T27agoeM8R3OLZe7sv hteA== X-Gm-Message-State: APjAAAWpLdhzbsi6RsdmGF6VCaebOLEPNANeF5taJg7PIlXpOcxZuxqI VCaLLox9rl3nDUMf6IqyQ3XseCLn X-Google-Smtp-Source: APXvYqxfKduK+EXEg2BL5g+6kX82qhTeUJftH5e4+Re4eP4kqmwLub+tkgZmnvTnfkONfCCzN5Kh/A== X-Received: by 2002:a37:a30a:: with SMTP id m10mr42444226qke.56.1577467719457; Fri, 27 Dec 2019 09:28:39 -0800 (PST) Received: from zhangzdt (static-71-246-230-26.washdc.fios.verizon.net. [71.246.230.26]) by smtp.gmail.com with ESMTPSA id f23sm9853952qke.104.2019.12.27.09.28.38 (version=TLS1_2 cipher=ECDHE-RSA-AES128-GCM-SHA256 bits=128/128); Fri, 27 Dec 2019 09:28:38 -0800 (PST) From: "Zhihong Zhang" To: "'Tom Lane'" Cc: "'Tomas Vondra'" , References: <0cf001d5b77f$bfe47e10$3fad7a30$@gmail.com> <20191220223005.f6w4ff4kdbgtanpz@development> <20191221114003.yo56lv3o6thdvxtn@development> <21855.1576943356@sss.pgh.pa.us> <0AA828F3-5BA5-48CD-B229-8351ACB308D3@gmail.com> In-Reply-To: <0AA828F3-5BA5-48CD-B229-8351ACB308D3@gmail.com> Subject: RE: Indexing on JSONB field not working Date: Fri, 27 Dec 2019 12:28:38 -0500 Message-ID: <167501d5bcdb$13854b20$3a8fe160$@gmail.com> MIME-Version: 1.0 Content-Type: multipart/alternative; boundary="----=_NextPart_000_1676_01D5BCB1.2AB0C9C0" X-Mailer: Microsoft Outlook 14.0 Thread-Index: AQKpbNAKJKpsVQNC6dODYgLE8P3s1gNQJ/W8AZzB/E0COWweBAIg7RxgAPwU7B2l1Lx04A== Content-Language: en-us List-Id: List-Help: List-Subscribe: List-Post: List-Owner: List-Archive: Precedence: bulk This is a multipart message in MIME format. ------=_NextPart_000_1676_01D5BCB1.2AB0C9C0 Content-Type: text/plain; charset="utf-8" Content-Transfer-Encoding: quoted-printable I forgot to emphasize that this problem only happens with JSONB index. = The index always works if the same field is copied to a column. = That=E2=80=99s how we have been coping with this issue, simply moving = the field to a column but now we got too many columns to deal with. To prove this, I just created a new column 'floatValue' and copied the = value from JSONB column (_doc). alter table assets add column floatValue double precision; update assets set floatValue =3D (_doc #> = '{floatValue}'::text[])::double precision; create index floatValue_idx on assets (floatValue); analyze; Now the query on the column automatically uses the index, explain analyze select id, _doc->>'floatValue' from assets where = floatValue < 3.0 limit 100; QUERY PLAN -------------------------------------------------------------------------= ------------------------------------------------------- Limit (cost=3D0.43..28.55 rows=3D6 width=3D53) (actual = time=3D0.571..19.557 rows=3D7 loops=3D1) -> Index Scan using floatvalue_idx on assets (cost=3D0.43..28.55 = rows=3D6 width=3D53) (actual time=3D0.569..19.551 rows=3D7 loops=3D1) Index Cond: (floatvalue < '3'::double precision) Planning Time: 0.322 ms Execution Time: 19.583 ms (5 rows) Since I can=E2=80=99t get stats on the index, wonder if the stats on the = column is of any help. select * from pg_stats where tablename=3D'assets' and = attname=3D'floatvalue' ; -[ RECORD 1 = ]----------+-------------------------------------------------------------= -------------------------------------------------------------------------= -------------------------------------------------------------------------= -------------------------------------------------------------------------= -------------------------------------------------------------------------= -------------------------------------------------------------------------= -------------------------------------------------------------------------= -------------------------------------------------------------------------= -------------------------------------------------------------------------= -------------------------------------------------------------------------= -------------------------------------------------------------------------= -------------------------------------------------------------------------= -------------------------------------------------------------------------= -------------------------------------------------------------------------= -------------------------------------------------------------------------= -------------------------------------------------------------------------= -------------------------------------------------------------------------= -------------------------------------------------------------------------= -------------------------------------------------------------------------= -------------------------------------------------------------------------= -------------------------------------------------------------------------= -------------------------------------------------------------------------= -------------------------------------------------------------------------= ---------------------------------------- schemaname | public tablename | assets attname | floatvalue inherited | f null_frac | 3.33333e-05 avg_width | 8 n_distinct | -0.999967 most_common_vals | most_common_freqs | histogram_bounds | = {29.3762423098087,11544.743552804,22327.7248442173,31746.9625733793,42020= .0857333839,53353.4693531692,62746.0754476488,71412.5884696841,82250.3692= 470491,92105.7304367423,101821.479853243,111723.829060793,122510.32004132= 9,132316.885981709,142764.132469893,153306.225780398,162952.687591314,174= 045.353196561,184753.799811006,195106.332655996,205185.326747596,215598.9= 05924708,225703.200791031,235017.722006887,244950.931984931,255437.318701= 297,265914.741437882,275904.242414981,286097.948905081,296642.523724586,3= 07137.258350849,317328.266333789,328417.54052788,337816.804181784,348164.= 874594659,358840.349595994,368590.213824064,378130.563534796,387764.14375= 7522,398006.183560938,407868.383917958,417695.778422058,427624.609787017,= 437821.539118886,447861.317079514,457633.044105023,467341.55761078,477082= .30279386,486012.877896428,496032.587718219,507104.239426553,517111.51329= 8005,526737.637352198,536167.487502098,545932.489912957,555894.987657666,= 565314.109902829,574438.83176893,584489.227738231,593775.179702789,603509= .965352714,613848.30949828,624749.070033431,635505.4769665,644715.9447707= 24,654815.05356729,664933.282416314,673889.273777604,683789.047412574,693= 903.816863894,703375.991433859,713953.300379217,724327.767267823,733478.9= 33107108,744359.858334064,755353.817716241,764262.578450143,773850.545287= 132,783374.56099689,793155.808001757,803004.199638963,812858.935445547,82= 2967.322077602,832813.539542258,843135.526403785,853274.697437882,862586.= 844246835,872572.991997004,882436.727173626,891319.798305631,900905.37583= 4554,910936.662461609,921490.86529389,930878.716986626,940496.629569679,9= 49594.719801098,959338.55464682,969633.623026311,980396.131519228,989476.= 628601551,999952.361918986} correlation | -0.00371463 most_common_elems | most_common_elem_freqs | elem_count_histogram | Thanks! Zhihong ------=_NextPart_000_1676_01D5BCB1.2AB0C9C0 Content-Type: text/html; charset="utf-8" Content-Transfer-Encoding: quoted-printable RE: Indexing on JSONB field not working

I forgot to = emphasize that this problem only happens with JSONB index. The index always works if the = same field is copied to a column. That=E2=80=99s how we have been = coping with this issue, simply moving the field to a column but now we got too many columns = to deal with.

To prove this, I just created a new column 'floatValue' and copied = the value from JSONB column = (_doc).

alter table assets add column = floatValue double precision;

update assets set floatValue =3D = (_doc #> '{floatValue}'::text[])::double = precision;

create index floatValue_idx on = assets (floatValue);

analyze;

Now the query on the column automatically uses the = index,

explain analyze select id, = _doc->>'floatValue' from assets where floatValue < 3.0 limit = 100;

           &n= bsp;           &nb= sp;           &nbs= p;            = ;           QUERY = PLAN

--------------------------------------------------------------------= ------------------------------------------------------------

 Limit  (cost=3D0.43..28.55 rows=3D6 = width=3D53) (actual time=3D0.571..19.557 rows=3D7 = loops=3D1)

   ->  Index Scan using = floatvalue_idx on assets  (cost=3D0.43..28.55 rows=3D6 width=3D53) = (actual time=3D0.569..19.551 rows=3D7 loops=3D1)

         = Index Cond: (floatvalue < '3'::double precision)

 Planning Time: 0.322 ms

 Execution Time: 19.583 ms

(5 rows)

Since I can=E2=80=99t get stats on the index, = wonder if the stats on the column is of any help.

select * from pg_stats where = tablename=3D'assets' and attname=3D'floatvalue'

;

-[ RECORD 1 = ]----------+-------------------------------------------------------------= -------------------------------------------------------------------------= -------------------------------------------------------------------------= -------------------------------------------------------------------------= -------------------------------------------------------------------------= -------------------------------------------------------------------------= -------------------------------------------------------------------------= -------------------------------------------------------------------------= -------------------------------------------------------------------------= -------------------------------------------------------------------------= -------------------------------------------------------------------------= -------------------------------------------------------------------------= -------------------------------------------------------------------------= -------------------------------------------------------------------------= -------------------------------------------------------------------------= -------------------------------------------------------------------------= -------------------------------------------------------------------------= -------------------------------------------------------------------------= -------------------------------------------------------------------------= -------------------------------------------------------------------------= -------------------------------------------------------------------------= -------------------------------------------------------------------------= -------------------------------------------------------------------------= ----------------------------------------

schemaname         &nbs= p;   | public

tablename          = ;    | assets

attname          &= nbsp;     | floatvalue

inherited          = ;    | f

null_frac          = ;    | 3.33333e-05

avg_width          = ;    | 8

n_distinct         &nbs= p;   | -0.999967

most_common_vals       = |

most_common_freqs      = |

histogram_bounds       | = {29.3762423098087,11544.743552804,22327.7248442173,31746.9625733793,42020= .0857333839,53353.4693531692,62746.0754476488,71412.5884696841,82250.3692= 470491,92105.7304367423,101821.479853243,111723.829060793,122510.32004132= 9,132316.885981709,142764.132469893,153306.225780398,162952.687591314,174= 045.353196561,184753.799811006,195106.332655996,205185.326747596,215598.9= 05924708,225703.200791031,235017.722006887,244950.931984931,255437.318701= 297,265914.741437882,275904.242414981,286097.948905081,296642.523724586,3= 07137.258350849,317328.266333789,328417.54052788,337816.804181784,348164.= 874594659,358840.349595994,368590.213824064,378130.563534796,387764.14375= 7522,398006.183560938,407868.383917958,417695.778422058,427624.609787017,= 437821.539118886,447861.317079514,457633.044105023,467341.55761078,477082= .30279386,486012.877896428,496032.587718219,507104.239426553,517111.51329= 8005,526737.637352198,536167.487502098,545932.489912957,555894.987657666,= 565314.109902829,574438.83176893,584489.227738231,593775.179702789,603509= .965352714,613848.30949828,624749.070033431,635505.4769665,644715.9447707= 24,654815.05356729,664933.282416314,673889.273777604,683789.047412574,693= 903.816863894,703375.991433859,713953.300379217,724327.767267823,733478.9= 33107108,744359.858334064,755353.817716241,764262.578450143,773850.545287= 132,783374.56099689,793155.808001757,803004.199638963,812858.935445547,82= 2967.322077602,832813.539542258,843135.526403785,853274.697437882,862586.= 844246835,872572.991997004,882436.727173626,891319.798305631,900905.37583= 4554,910936.662461609,921490.86529389,930878.716986626,940496.629569679,9= 49594.719801098,959338.55464682,969633.623026311,980396.131519228,989476.= 628601551,999952.361918986}

correlation         &nb= sp;  | -0.00371463

most_common_elems      = |

most_common_elem_freqs |

elem_count_histogram   = |

Thanks!

Zhihong

------=_NextPart_000_1676_01D5BCB1.2AB0C9C0--