Peter Geoghegan <[email protected]> writes:
> Now, I'm not all that worried about this, because this is surely an
> odd-ball use case, particularly for jsonb_hash_ops where no keys are
> separately indexed (separately from *primitive* elements/values).
> However, it is worth noting in the documentation in my view. I attach
> a doc patch that adds this.
Agreed, we'd better mention that somewhere.
I'm not sure whether we have consensus to rename jsonb_hash_ops to
jsonb_path_ops, but since time is so short I went ahead and made a draft
patch to do so (attached). Probably the most interesting part of this is
the new text in json.sgml explaining the difference between the two
opclasses. I also added a paragraph about the empty-query hazard that
Peter mentions. Do people think this discussion is correct and useful?
regards, tom lane
diff --git a/doc/src/sgml/gin.sgml b/doc/src/sgml/gin.sgml
index 0b3d6ee..1cbc73c 100644
*** a/doc/src/sgml/gin.sgml
--- b/doc/src/sgml/gin.sgml
***************
*** 395,401 ****
</entry>
</row>
<row>
! <entry><literal>jsonb_hash_ops</></entry>
<entry><type>jsonb</></entry>
<entry>
<literal>@></>
--- 395,401 ----
</entry>
</row>
<row>
! <entry><literal>jsonb_path_ops</></entry>
<entry><type>jsonb</></entry>
<entry>
<literal>@></>
***************
*** 415,421 ****
<para>
Of the two operator classes for type <type>jsonb</>, <literal>jsonb_ops</>
! is the default. <literal>jsonb_hash_ops</> supports fewer operators but
offers better performance for those operators.
See <xref linkend="json-indexing"> for details.
</para>
--- 415,421 ----
<para>
Of the two operator classes for type <type>jsonb</>, <literal>jsonb_ops</>
! is the default. <literal>jsonb_path_ops</> supports fewer operators but
offers better performance for those operators.
See <xref linkend="json-indexing"> for details.
</para>
diff --git a/doc/src/sgml/json.sgml b/doc/src/sgml/json.sgml
index 518fe63..52ad882 100644
*** a/doc/src/sgml/json.sgml
--- b/doc/src/sgml/json.sgml
***************
*** 156,162 ****
</table>
<sect2 id="json-keys-elements">
! <title><type>jsonb</> Input and Output Syntax</title>
<para>
The input/output syntax for the JSON data types is as specified in
<acronym>RFC</> 7159.
--- 156,162 ----
</table>
<sect2 id="json-keys-elements">
! <title>JSON Input and Output Syntax</title>
<para>
The input/output syntax for the JSON data types is as specified in
<acronym>RFC</> 7159.
*************** SELECT '"foo"'::jsonb ? 'foo';
*** 366,376 ****
<programlisting>
CREATE INDEX idxgin ON api USING gin (jdoc);
</programlisting>
! The non-default GIN operator class <literal>jsonb_hash_ops</>
supports indexing the <literal>@></> operator only.
An example of creating an index with this operator class is:
<programlisting>
! CREATE INDEX idxginh ON api USING gin (jdoc jsonb_hash_ops);
</programlisting>
</para>
--- 366,376 ----
<programlisting>
CREATE INDEX idxgin ON api USING gin (jdoc);
</programlisting>
! The non-default GIN operator class <literal>jsonb_path_ops</>
supports indexing the <literal>@></> operator only.
An example of creating an index with this operator class is:
<programlisting>
! CREATE INDEX idxginh ON api USING gin (jdoc jsonb_path_ops);
</programlisting>
</para>
*************** SELECT jdoc->'guid', jdoc->'name'
*** 444,453 ****
</para>
<para>
! Although the <literal>jsonb_hash_ops</literal> operator class supports
only queries with the <literal>@></> operator, it has notable
performance advantages over the default operator
! class <literal>jsonb_ops</literal>. A <literal>jsonb_hash_ops</literal>
index is usually much smaller than a <literal>jsonb_ops</literal>
index over the same data, and the specificity of searches is better,
particularly when queries contain keys that appear frequently in the
--- 444,453 ----
</para>
<para>
! Although the <literal>jsonb_path_ops</literal> operator class supports
only queries with the <literal>@></> operator, it has notable
performance advantages over the default operator
! class <literal>jsonb_ops</literal>. A <literal>jsonb_path_ops</literal>
index is usually much smaller than a <literal>jsonb_ops</literal>
index over the same data, and the specificity of searches is better,
particularly when queries contain keys that appear frequently in the
*************** SELECT jdoc->'guid', jdoc->'name'
*** 456,461 ****
--- 456,493 ----
</para>
<para>
+ The technical difference between a <literal>jsonb_ops</literal>
+ and a <literal>jsonb_path_ops</literal> GIN index is that the former
+ creates independent index items for each key and value in the data,
+ while the latter creates index items only for each value in the data.
+ But in <literal>jsonb_path_ops</literal>, each index item is a hash
+ of both the value and the key(s) leading to it; for example to index
+ <literal>{"foo": {"bar": "baz"}}</literal>, a single index item would
+ be created incorporating all three of <literal>foo</>, <literal>bar</>,
+ and <literal>baz</> into the hash value. Thus a containment query
+ looking for this structure would result in an extremely specific index
+ search; but there is no way at all to find out whether <literal>foo</>
+ appears as a key. On the other hand, a <literal>jsonb_ops</literal>
+ index would create three index items representing <literal>foo</>,
+ <literal>bar</>, and <literal>baz</> separately; then to do the
+ containment query, it would look for rows containing all three of
+ these keys. While GIN indexes can perform such an AND search fairly
+ efficiently, it will still be less specific and slower than the
+ equivalent <literal>jsonb_path_ops</literal> search, especially if
+ there are a very large number of rows containing any single one of the
+ three keys.
+ </para>
+
+ <para>
+ A disadvantage of the <literal>jsonb_path_ops</literal> approach is
+ that it produces no index entries for JSON structures not containing
+ any values, such as <literal>{"a": {}}</literal>. If a search for
+ documents containing such a structure is requested, it will require a
+ full-index scan, which is quite slow. <literal>jsonb_path_ops</> is
+ therefore ill-suited for applications that perform such searches.
+ </para>
+
+ <para>
<type>jsonb</> also supports <literal>btree</> and <literal>hash</>
indexes. These are usually useful only if it's important to check
equality of complete JSON documents.
diff --git a/src/backend/utils/adt/jsonb_gin.c b/src/backend/utils/adt/jsonb_gin.c
index 57a0b2c..069ee03 100644
*** a/src/backend/utils/adt/jsonb_gin.c
--- b/src/backend/utils/adt/jsonb_gin.c
*************** gin_triconsistent_jsonb(PG_FUNCTION_ARGS
*** 315,323 ****
/*
*
! * jsonb_hash_ops GIN opclass support functions
*
! * In a jsonb_hash_ops index, the GIN keys are uint32 hashes, one per JSON
* value; but the JSON key(s) leading to each value are also included in its
* hash computation. This means we can only support containment queries,
* but the index can distinguish, for example, {"foo": 42} from {"bar": 42}
--- 315,323 ----
/*
*
! * jsonb_path_ops GIN opclass support functions
*
! * In a jsonb_path_ops index, the GIN keys are uint32 hashes, one per JSON
* value; but the JSON key(s) leading to each value are also included in its
* hash computation. This means we can only support containment queries,
* but the index can distinguish, for example, {"foo": 42} from {"bar": 42}
*************** gin_triconsistent_jsonb(PG_FUNCTION_ARGS
*** 326,332 ****
*/
Datum
! gin_extract_jsonb_hash(PG_FUNCTION_ARGS)
{
Jsonb *jb = PG_GETARG_JSONB(0);
int32 *nentries = (int32 *) PG_GETARG_POINTER(1);
--- 326,332 ----
*/
Datum
! gin_extract_jsonb_path(PG_FUNCTION_ARGS)
{
Jsonb *jb = PG_GETARG_JSONB(0);
int32 *nentries = (int32 *) PG_GETARG_POINTER(1);
*************** gin_extract_jsonb_hash(PG_FUNCTION_ARGS)
*** 349,355 ****
/* Otherwise, use 2 * root count as initial estimate of result size */
entries = (Datum *) palloc(sizeof(Datum) * total);
! /* We keep a stack of hashes corresponding to parent key levels */
tail.parent = NULL;
tail.hash = 0;
stack = &tail;
--- 349,355 ----
/* Otherwise, use 2 * root count as initial estimate of result size */
entries = (Datum *) palloc(sizeof(Datum) * total);
! /* We keep a stack of partial hashes corresponding to parent key levels */
tail.parent = NULL;
tail.hash = 0;
stack = &tail;
*************** gin_extract_jsonb_hash(PG_FUNCTION_ARGS)
*** 439,445 ****
}
Datum
! gin_extract_jsonb_query_hash(PG_FUNCTION_ARGS)
{
int32 *nentries = (int32 *) PG_GETARG_POINTER(1);
StrategyNumber strategy = PG_GETARG_UINT16(2);
--- 439,445 ----
}
Datum
! gin_extract_jsonb_query_path(PG_FUNCTION_ARGS)
{
int32 *nentries = (int32 *) PG_GETARG_POINTER(1);
StrategyNumber strategy = PG_GETARG_UINT16(2);
*************** gin_extract_jsonb_query_hash(PG_FUNCTION
*** 449,457 ****
if (strategy != JsonbContainsStrategyNumber)
elog(ERROR, "unrecognized strategy number: %d", strategy);
! /* Query is a jsonb, so just apply gin_extract_jsonb_hash ... */
entries = (Datum *)
! DatumGetPointer(DirectFunctionCall2(gin_extract_jsonb_hash,
PG_GETARG_DATUM(0),
PointerGetDatum(nentries)));
--- 449,457 ----
if (strategy != JsonbContainsStrategyNumber)
elog(ERROR, "unrecognized strategy number: %d", strategy);
! /* Query is a jsonb, so just apply gin_extract_jsonb_path ... */
entries = (Datum *)
! DatumGetPointer(DirectFunctionCall2(gin_extract_jsonb_path,
PG_GETARG_DATUM(0),
PointerGetDatum(nentries)));
*************** gin_extract_jsonb_query_hash(PG_FUNCTION
*** 463,469 ****
}
Datum
! gin_consistent_jsonb_hash(PG_FUNCTION_ARGS)
{
bool *check = (bool *) PG_GETARG_POINTER(0);
StrategyNumber strategy = PG_GETARG_UINT16(1);
--- 463,469 ----
}
Datum
! gin_consistent_jsonb_path(PG_FUNCTION_ARGS)
{
bool *check = (bool *) PG_GETARG_POINTER(0);
StrategyNumber strategy = PG_GETARG_UINT16(1);
*************** gin_consistent_jsonb_hash(PG_FUNCTION_AR
*** 480,492 ****
elog(ERROR, "unrecognized strategy number: %d", strategy);
/*
! * jsonb_hash_ops is necessarily lossy, not only because of hash
* collisions but also because it doesn't preserve complete information
* about the structure of the JSON object. Besides, there are some
! * special rules around the containment of raw scalar arrays and regular
! * arrays that are not handled here. So we must always recheck a match.
! * However, if not all of the keys are present, the tuple certainly
! * doesn't match.
*/
*recheck = true;
for (i = 0; i < nkeys; i++)
--- 480,491 ----
elog(ERROR, "unrecognized strategy number: %d", strategy);
/*
! * jsonb_path_ops is necessarily lossy, not only because of hash
* collisions but also because it doesn't preserve complete information
* about the structure of the JSON object. Besides, there are some
! * special rules around the containment of raw scalars in arrays that are
! * not handled here. So we must always recheck a match. However, if not
! * all of the keys are present, the tuple certainly doesn't match.
*/
*recheck = true;
for (i = 0; i < nkeys; i++)
*************** gin_consistent_jsonb_hash(PG_FUNCTION_AR
*** 502,508 ****
}
Datum
! gin_triconsistent_jsonb_hash(PG_FUNCTION_ARGS)
{
GinTernaryValue *check = (GinTernaryValue *) PG_GETARG_POINTER(0);
StrategyNumber strategy = PG_GETARG_UINT16(1);
--- 501,507 ----
}
Datum
! gin_triconsistent_jsonb_path(PG_FUNCTION_ARGS)
{
GinTernaryValue *check = (GinTernaryValue *) PG_GETARG_POINTER(0);
StrategyNumber strategy = PG_GETARG_UINT16(1);
diff --git a/src/include/catalog/pg_amop.h b/src/include/catalog/pg_amop.h
index 8efd3be..264059f 100644
*** a/src/include/catalog/pg_amop.h
--- b/src/include/catalog/pg_amop.h
*************** DATA(insert ( 4033 3802 3802 4 s 3245
*** 787,798 ****
DATA(insert ( 4033 3802 3802 5 s 3243 403 0 ));
/*
! * hash jsonb ops
*/
DATA(insert ( 4034 3802 3802 1 s 3240 405 0 ));
/*
! * GIN jsonb ops
*/
DATA(insert ( 4036 3802 3802 7 s 3246 2742 0 ));
DATA(insert ( 4036 3802 25 9 s 3247 2742 0 ));
--- 787,798 ----
DATA(insert ( 4033 3802 3802 5 s 3243 403 0 ));
/*
! * hash jsonb_ops
*/
DATA(insert ( 4034 3802 3802 1 s 3240 405 0 ));
/*
! * GIN jsonb_ops
*/
DATA(insert ( 4036 3802 3802 7 s 3246 2742 0 ));
DATA(insert ( 4036 3802 25 9 s 3247 2742 0 ));
*************** DATA(insert ( 4036 3802 1009 10 s 3248
*** 800,806 ****
DATA(insert ( 4036 3802 1009 11 s 3249 2742 0 ));
/*
! * GIN jsonb hash ops
*/
DATA(insert ( 4037 3802 3802 7 s 3246 2742 0 ));
--- 800,806 ----
DATA(insert ( 4036 3802 1009 11 s 3249 2742 0 ));
/*
! * GIN jsonb_path_ops
*/
DATA(insert ( 4037 3802 3802 7 s 3246 2742 0 ));
diff --git a/src/include/catalog/pg_opclass.h b/src/include/catalog/pg_opclass.h
index ecf7063..3698886 100644
*** a/src/include/catalog/pg_opclass.h
--- b/src/include/catalog/pg_opclass.h
*************** DATA(insert ( 4000 text_ops PGNSP PGUI
*** 232,237 ****
DATA(insert ( 403 jsonb_ops PGNSP PGUID 4033 3802 t 0 ));
DATA(insert ( 405 jsonb_ops PGNSP PGUID 4034 3802 t 0 ));
DATA(insert ( 2742 jsonb_ops PGNSP PGUID 4036 3802 t 25 ));
! DATA(insert ( 2742 jsonb_hash_ops PGNSP PGUID 4037 3802 f 23 ));
#endif /* PG_OPCLASS_H */
--- 232,237 ----
DATA(insert ( 403 jsonb_ops PGNSP PGUID 4033 3802 t 0 ));
DATA(insert ( 405 jsonb_ops PGNSP PGUID 4034 3802 t 0 ));
DATA(insert ( 2742 jsonb_ops PGNSP PGUID 4036 3802 t 25 ));
! DATA(insert ( 2742 jsonb_path_ops PGNSP PGUID 4037 3802 f 23 ));
#endif /* PG_OPCLASS_H */
diff --git a/src/include/catalog/pg_opfamily.h b/src/include/catalog/pg_opfamily.h
index 9e8f4ac..c83ac8c 100644
*** a/src/include/catalog/pg_opfamily.h
--- b/src/include/catalog/pg_opfamily.h
*************** DATA(insert OID = 3474 ( 4000 range_ops
*** 148,158 ****
DATA(insert OID = 4015 ( 4000 quad_point_ops PGNSP PGUID ));
DATA(insert OID = 4016 ( 4000 kd_point_ops PGNSP PGUID ));
DATA(insert OID = 4017 ( 4000 text_ops PGNSP PGUID ));
DATA(insert OID = 4033 ( 403 jsonb_ops PGNSP PGUID ));
DATA(insert OID = 4034 ( 405 jsonb_ops PGNSP PGUID ));
DATA(insert OID = 4035 ( 783 jsonb_ops PGNSP PGUID ));
DATA(insert OID = 4036 ( 2742 jsonb_ops PGNSP PGUID ));
! DATA(insert OID = 4037 ( 2742 jsonb_hash_ops PGNSP PGUID ));
! #define TEXT_SPGIST_FAM_OID 4017
#endif /* PG_OPFAMILY_H */
--- 148,158 ----
DATA(insert OID = 4015 ( 4000 quad_point_ops PGNSP PGUID ));
DATA(insert OID = 4016 ( 4000 kd_point_ops PGNSP PGUID ));
DATA(insert OID = 4017 ( 4000 text_ops PGNSP PGUID ));
+ #define TEXT_SPGIST_FAM_OID 4017
DATA(insert OID = 4033 ( 403 jsonb_ops PGNSP PGUID ));
DATA(insert OID = 4034 ( 405 jsonb_ops PGNSP PGUID ));
DATA(insert OID = 4035 ( 783 jsonb_ops PGNSP PGUID ));
DATA(insert OID = 4036 ( 2742 jsonb_ops PGNSP PGUID ));
! DATA(insert OID = 4037 ( 2742 jsonb_path_ops PGNSP PGUID ));
#endif /* PG_OPFAMILY_H */
diff --git a/src/include/catalog/pg_proc.h b/src/include/catalog/pg_proc.h
index e601ccd..72170af 100644
*** a/src/include/catalog/pg_proc.h
--- b/src/include/catalog/pg_proc.h
*************** DATA(insert OID = 3484 ( gin_consistent
*** 4645,4657 ****
DESCR("GIN support");
DATA(insert OID = 3488 ( gin_triconsistent_jsonb PGNSP PGUID 12 1 0 0 0 f f f f t f i 7 0 16 "2281 21 2277 23 2281 2281 2281" _null_ _null_ _null_ _null_ gin_triconsistent_jsonb _null_ _null_ _null_ ));
DESCR("GIN support");
! DATA(insert OID = 3485 ( gin_extract_jsonb_hash PGNSP PGUID 12 1 0 0 0 f f f f t f i 3 0 2281 "2281 2281 2281" _null_ _null_ _null_ _null_ gin_extract_jsonb_hash _null_ _null_ _null_ ));
DESCR("GIN support");
! DATA(insert OID = 3486 ( gin_extract_jsonb_query_hash PGNSP PGUID 12 1 0 0 0 f f f f t f i 7 0 2281 "2277 2281 21 2281 2281 2281 2281" _null_ _null_ _null_ _null_ gin_extract_jsonb_query_hash _null_ _null_ _null_ ));
DESCR("GIN support");
! DATA(insert OID = 3487 ( gin_consistent_jsonb_hash PGNSP PGUID 12 1 0 0 0 f f f f t f i 8 0 16 "2281 21 2277 23 2281 2281 2281 2281" _null_ _null_ _null_ _null_ gin_consistent_jsonb_hash _null_ _null_ _null_ ));
DESCR("GIN support");
! DATA(insert OID = 3489 ( gin_triconsistent_jsonb_hash PGNSP PGUID 12 1 0 0 0 f f f f t f i 7 0 16 "2281 21 2277 23 2281 2281 2281" _null_ _null_ _null_ _null_ gin_triconsistent_jsonb_hash _null_ _null_ _null_ ));
DESCR("GIN support");
/* txid */
--- 4645,4657 ----
DESCR("GIN support");
DATA(insert OID = 3488 ( gin_triconsistent_jsonb PGNSP PGUID 12 1 0 0 0 f f f f t f i 7 0 16 "2281 21 2277 23 2281 2281 2281" _null_ _null_ _null_ _null_ gin_triconsistent_jsonb _null_ _null_ _null_ ));
DESCR("GIN support");
! DATA(insert OID = 3485 ( gin_extract_jsonb_path PGNSP PGUID 12 1 0 0 0 f f f f t f i 3 0 2281 "2281 2281 2281" _null_ _null_ _null_ _null_ gin_extract_jsonb_path _null_ _null_ _null_ ));
DESCR("GIN support");
! DATA(insert OID = 3486 ( gin_extract_jsonb_query_path PGNSP PGUID 12 1 0 0 0 f f f f t f i 7 0 2281 "2277 2281 21 2281 2281 2281 2281" _null_ _null_ _null_ _null_ gin_extract_jsonb_query_path _null_ _null_ _null_ ));
DESCR("GIN support");
! DATA(insert OID = 3487 ( gin_consistent_jsonb_path PGNSP PGUID 12 1 0 0 0 f f f f t f i 8 0 16 "2281 21 2277 23 2281 2281 2281 2281" _null_ _null_ _null_ _null_ gin_consistent_jsonb_path _null_ _null_ _null_ ));
DESCR("GIN support");
! DATA(insert OID = 3489 ( gin_triconsistent_jsonb_path PGNSP PGUID 12 1 0 0 0 f f f f t f i 7 0 16 "2281 21 2277 23 2281 2281 2281" _null_ _null_ _null_ _null_ gin_triconsistent_jsonb_path _null_ _null_ _null_ ));
DESCR("GIN support");
/* txid */
diff --git a/src/include/utils/jsonb.h b/src/include/utils/jsonb.h
index bb8c380..add7628 100644
*** a/src/include/utils/jsonb.h
--- b/src/include/utils/jsonb.h
*************** extern Datum jsonb_eq(PG_FUNCTION_ARGS);
*** 332,349 ****
extern Datum jsonb_cmp(PG_FUNCTION_ARGS);
extern Datum jsonb_hash(PG_FUNCTION_ARGS);
! /* GIN support functions */
extern Datum gin_compare_jsonb(PG_FUNCTION_ARGS);
extern Datum gin_extract_jsonb(PG_FUNCTION_ARGS);
extern Datum gin_extract_jsonb_query(PG_FUNCTION_ARGS);
extern Datum gin_consistent_jsonb(PG_FUNCTION_ARGS);
extern Datum gin_triconsistent_jsonb(PG_FUNCTION_ARGS);
! /* GIN hash opclass functions */
! extern Datum gin_extract_jsonb_hash(PG_FUNCTION_ARGS);
! extern Datum gin_extract_jsonb_query_hash(PG_FUNCTION_ARGS);
! extern Datum gin_consistent_jsonb_hash(PG_FUNCTION_ARGS);
! extern Datum gin_triconsistent_jsonb_hash(PG_FUNCTION_ARGS);
/* Support functions */
extern int compareJsonbContainers(JsonbContainer *a, JsonbContainer *b);
--- 332,349 ----
extern Datum jsonb_cmp(PG_FUNCTION_ARGS);
extern Datum jsonb_hash(PG_FUNCTION_ARGS);
! /* GIN support functions for jsonb_ops */
extern Datum gin_compare_jsonb(PG_FUNCTION_ARGS);
extern Datum gin_extract_jsonb(PG_FUNCTION_ARGS);
extern Datum gin_extract_jsonb_query(PG_FUNCTION_ARGS);
extern Datum gin_consistent_jsonb(PG_FUNCTION_ARGS);
extern Datum gin_triconsistent_jsonb(PG_FUNCTION_ARGS);
! /* GIN support functions for jsonb_path_ops */
! extern Datum gin_extract_jsonb_path(PG_FUNCTION_ARGS);
! extern Datum gin_extract_jsonb_query_path(PG_FUNCTION_ARGS);
! extern Datum gin_consistent_jsonb_path(PG_FUNCTION_ARGS);
! extern Datum gin_triconsistent_jsonb_path(PG_FUNCTION_ARGS);
/* Support functions */
extern int compareJsonbContainers(JsonbContainer *a, JsonbContainer *b);
diff --git a/src/test/regress/expected/jsonb.out b/src/test/regress/expected/jsonb.out
index c5a7d64..ae7c506 100644
*** a/src/test/regress/expected/jsonb.out
--- b/src/test/regress/expected/jsonb.out
*************** SELECT count(*) FROM testjsonb WHERE j =
*** 1685,1693 ****
1
(1 row)
! --gin hash
DROP INDEX jidx;
! CREATE INDEX jidx ON testjsonb USING gin (j jsonb_hash_ops);
SET enable_seqscan = off;
SELECT count(*) FROM testjsonb WHERE j @> '{"wait":null}';
count
--- 1685,1693 ----
1
(1 row)
! --gin path opclass
DROP INDEX jidx;
! CREATE INDEX jidx ON testjsonb USING gin (j jsonb_path_ops);
SET enable_seqscan = off;
SELECT count(*) FROM testjsonb WHERE j @> '{"wait":null}';
count
diff --git a/src/test/regress/expected/jsonb_1.out b/src/test/regress/expected/jsonb_1.out
index 0e3ebd1..38a95b4 100644
*** a/src/test/regress/expected/jsonb_1.out
--- b/src/test/regress/expected/jsonb_1.out
*************** SELECT count(*) FROM testjsonb WHERE j =
*** 1685,1693 ****
1
(1 row)
! --gin hash
DROP INDEX jidx;
! CREATE INDEX jidx ON testjsonb USING gin (j jsonb_hash_ops);
SET enable_seqscan = off;
SELECT count(*) FROM testjsonb WHERE j @> '{"wait":null}';
count
--- 1685,1693 ----
1
(1 row)
! --gin path opclass
DROP INDEX jidx;
! CREATE INDEX jidx ON testjsonb USING gin (j jsonb_path_ops);
SET enable_seqscan = off;
SELECT count(*) FROM testjsonb WHERE j @> '{"wait":null}';
count
diff --git a/src/test/regress/sql/jsonb.sql b/src/test/regress/sql/jsonb.sql
index 3e90489..7527925 100644
*** a/src/test/regress/sql/jsonb.sql
--- b/src/test/regress/sql/jsonb.sql
*************** SET enable_seqscan = off;
*** 391,399 ****
SELECT count(*) FROM testjsonb WHERE j > '{"p":1}';
SELECT count(*) FROM testjsonb WHERE j = '{"pos":98, "line":371, "node":"CBA", "indexed":true}';
! --gin hash
DROP INDEX jidx;
! CREATE INDEX jidx ON testjsonb USING gin (j jsonb_hash_ops);
SET enable_seqscan = off;
SELECT count(*) FROM testjsonb WHERE j @> '{"wait":null}';
--- 391,399 ----
SELECT count(*) FROM testjsonb WHERE j > '{"p":1}';
SELECT count(*) FROM testjsonb WHERE j = '{"pos":98, "line":371, "node":"CBA", "indexed":true}';
! --gin path opclass
DROP INDEX jidx;
! CREATE INDEX jidx ON testjsonb USING gin (j jsonb_path_ops);
SET enable_seqscan = off;
SELECT count(*) FROM testjsonb WHERE j @> '{"wait":null}';
--
Sent via pgsql-hackers mailing list ([email protected])
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers
