How to create index for postgresql jsonb field (array data) and text field

Question

Please let me know how to create index for below query.

SELECT * FROM customers
WHERE identifiers @>
      '[{"systemName": "SAP", "systemReference": "33557"}]'
  AND country_code = 'IN';

identifiers is jsonb type and data is as below.

[{"systemName": "ERP", "systemReference": "TEST"}, {"systemName": "FEED", "systemReference": "2733"}, {"systemName": "SAP", "systemReference": "33557"}]

country_code is varchar type.

Jim Jones · Accepted Answer · 2021-08-03 09:49:42Z

1

Either create a GIN index on identifiers ..

CREATE INDEX customers_identifiers_idx ON customers 
USING GIN(identifiers);

.. or a composite index with identifiers and country_code.

CREATE INDEX customers_country_code_identifiers_idx ON customers 
USING GIN(identifiers,country_code gin_trgm_ops);

The second option will depend on the values distribution of country_code.

Demo: db<>fiddle

edited Aug 3, 2021 at 9:49

answered Aug 3, 2021 at 9:41

Jim Jones

19.9k3 gold badges44 silver badges51 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

JessePinkman · Accepted Answer · 2021-08-03 09:17:48Z

0

You can create gin index for jsonb typed columns in Postgresql. gin index has built-in operator classes to handle jsonb operators. Learn more about gin index here https://www.postgresql.org/docs/12/gin-intro.html

For varchar types, btree index is good enough.

answered Aug 3, 2021 at 9:17

JessePinkman

6911 gold badge8 silver badges16 bronze badges

Collectives™ on Stack Overflow

How to create index for postgresql jsonb field (array data) and text field

2 Answers 2

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related