Unable to search data fast in postgres table

Question

I am using Postgres v 9.5.

I have a table with a column of type JSON. Trying to search data in that JSON column by using the following query:

select * from mytable where  data->'application_form_attributes'->>'5705' = 'ABCD';

To speedup the query I have tried to create an index :

CREATE INDEX myindex1 ON mytable USING btree (base_service_id,(data :: jsonb->'application_form_attributes'));

But I am getting the error:-

ERROR:  index row size 2744 exceeds maximum 2712 for index "myindex1"
HINT:  Values larger than 1/3 of a buffer page cannot be indexed.
Consider a function index of an MD5 hash of the value, or use full text indexing.
SQL state: 54000

mytable structure is:

  department_id integer,
  org_name character varying(250),
  base_service_id integer,
  service_id integer,
  service_name character varying(250),
  application_id integer NOT NULL,
  appl_ref_no character varying(50),
  submission_date timestamp without time zone,
  submission_mode_id integer,
  submission_mode character varying(150),
  submission_location character varying(250),
  data json,
  execution_data json,
  pending_task_json json,
  max_current_process_id integer,
  appl_status character(1),
  due_date timestamp without time zone,
  payment_mode_name character varying(200),
  version_no integer,
  appl_amount numeric(18,2),
  coverage_location_id integer,
  location_id integer,
  location_type_id integer,
  payment_mode_id integer,
  applied_by character varying,
  kiosk_registration_no character varying(50),
  last_task_at character varying(100),
  current_task_pending character varying(500),

  CONSTRAINT pk_table1_json PRIMARY KEY (application_id)

Need solution so that the search could be made fast.

Update :

My index that created successfully:

CREATE INDEX concurrently appl_processingjson_data_idx2 ON application_processing_json (CAST(md5(initiated_data :: text) AS uuid));

EXPLAIN output:

select * from application_processing_json where base_service_id = 721 and initiated_data->'application_form_attributes'->>'5734' = '9898989822';

"Seq Scan on application_processing_json (cost=0.00..109162.32 rows=2342 width=1998)"
" Filter: ((base_service_id = 721) AND (((initiated_data -> 'application_form_attributes'::text) ->> '5734'::text) = '9898989822'::text))"

Now problem is that this index is not being used in searching. Rather a sequential scan is being used as described in EXPLAIN.

Answer 1

You can rewrite your query to use the @> operator which can make use of a GIN index:

select * 
from mytable 
where data->'application_form_attributes' @> '{"5705", "ABCD"}';

That should make use of the following index:

create index on mytable using gin ((data->'application_form_attributes'));

If you always include the base_service_id column in the WHERE clause, you can create a combined index:

create index on mytable 
    using gin (base_service_id , (data->'application_form_attributes'));

The above requires the btree-gin extension.

Answer 2

You get that error because data->'application_form_attributes' is too large to fit in an index entry.

You'd have to index the whole left side of the expression:

CREATE INDEX ON mytable (data->'application_form_attributes'->>'5705');

Of course, if '5705' is not a constant, that won't get you far.

With your index, you could change the query to:

... WHERE data->'application_form_attributes' ? '5705'

but since you cannot create the index, that won't help you.

I don't think that there is an easy way out. Perhaps it would be best to "normalize" the schema, pull those values you want to search for out of the JSON and model it as several tables with foreign key relationship. Then your query will be simpler and perform better, and indexing won't be a problem.