Postgresql – Using GIN to index a JSON column

jsonpostgresqlpostgresql-9.4

I need to add index on a JSON column in my table as per documentation I am executing a query similar to the one below.

CREATE INDEX idxgin ON api USING gin (jdoc);

I am getting following error:

ERROR: data type json has no default operator class for access method "gin"
HINT: You must specify an operator class for the index or define a default operator class for the data type.

Here is the link for the documentation.

Best Answer

GiN(Generalized Inverted Index) is inverted index, a structure that has only one single index entry per a key and store the mapping information(posting list) of all key row to have the same value in the index entry. In postgreSQL, the key is stored in the index entry and mapping information for the key is stored in the posting tree. To search the index entry and posting tree is using a B-Tree. Therefore, GIN is useful when an index must map many values to on row, such as indexing array, documents.

I suppose to that before creating GIN index , you are doing some mistake. as you see here postgresql GIN pg_trgm default operator class

To Creates a GIN (Generalized Inverted Index) the syntax should be like that

CREATE INDEX name ON table USING gin(column);

Creates a GIN (Generalized Inverted Index)-based index. The column must be of tsvector type.

Default operator class jsonb_ops supports existence operators( ?, ?&, ?| ) and containment operator( @> ). And jsonb_path_ops supports containment operator only. Therefore GIN index is only possible to search for that have a particular key or key-values.

When we use ->> operator of JSONB, PostgreSQL can use B-tree or Hash index for processing the operations. ->> oerator returns the value of the specified attribute in text format. PostgreSQL can use indexes for the text results as compare operands. GIN index can be used by GIN jsonb operator class.

To see the json operator Here

For further ref Here and Here

Related Solutions

PostgreSQL Text Pattern Ops Index – Why Index text_pattern_ops on a Text Column?

The documentation often gives you an answer to such questions. Like in this case, too:

The operator classes text_pattern_ops, varchar_pattern_ops, and bpchar_pattern_ops support B-tree indexes on the types text, varchar, and char respectively. The difference from the default operator classes is that the values are compared strictly character by character rather than according to the locale-specific collation rules. This makes these operator classes suitable for use by queries involving pattern matching expressions (LIKE or POSIX regular expressions) when the database does not use the standard "C" locale. As an example, you might index a varchar column like this:
CREATE INDEX test_index ON test_table (col varchar_pattern_ops);
Note that you should also create an index with the default operator class if you want queries involving ordinary <, <=, >, or >= comparisons to use an index. Such queries cannot use the xxx_pattern_ops operator classes. (Ordinary equality comparisons can use these operator classes, however.) It is possible to create multiple indexes on the same column with different operator classes.

The documentation goes on to say:

If you do use the C locale, you do not need the xxx_pattern_ops operator classes, because an index with the default operator class is usable for pattern-matching queries in the C locale.

You can check your locale as follows (it is likely to be UTF8 rather than "C"):

postgres=> show lc_collate;
 lc_collate
-------------
 en_GB.UTF-8

PostgreSQL GIN pg_trgm Default Operator Class Overview

you can update the pg_opclass table and set the default.

update pg_opclass set opcdefault = true where opcname='gin_trgm_ops'

pg_trgm doesn't do this by default as you might have/want a different GIN default.

select * from pg_opclass where opcname = 'gin_trgm_ops';

than you can create an index:

create index ON table USING gin (field); -- it will use the new default

Best Answer

Related Solutions

PostgreSQL Text Pattern Ops Index – Why Index text_pattern_ops on a Text Column?

PostgreSQL GIN pg_trgm Default Operator Class Overview

Related Question