I have a table that has a jsonb column which has an array of objects.

Looks like this for every row.

    [{grade: 'A', subject: 'MATH'}, {grade: 'B', subject: 'PHY'}...]

Now querying over it thanks to this post http://stackoverflow.com/a/30592076/2405689 .

Problem comes it takes at least 2.4 secs to finish querying a count of all students with `grade` IN (A, B, C).

I would like to get some help on indexing this because, the indexes I have tried didn't do anything.

    DROP INDEX idx_subjects_subject;
    DROP INDEX idx_subjects_grade;
    CREATE INDEX idx_subjects_subject ON results USING GIN((subjects-> 'subject'));
    CREATE INDEX idx_subjects_grade ON results USING GIN((subjects-> 'grade'));

Also did (separately): 

    DROP INDEX idx_subjects_standard;
    CREATE INDEX idx_subjects_standard ON results USING GIN(subjects);


I'm querying them like this.

    SELECT COUNT(*)
    FROM results
    WHERE EXISTS 
    	(
    		SELECT 1 
    		FROM jsonb_array_elements(subjects) AS j(data) 
    		WHERE (data #>> '{subject}') LIKE '%MATH%' 
    		AND 
    		(data #>> '{grade}') IN ('A', 'B', 'C')
    	)
    AND
    	"examYear" = '2010'
    AND
    	"examType" = 'CSEE'
    ;


I also tried querying it like this :

    SELECT COUNT(*)
    FROM results
    WHERE EXISTS 
    	(
    		SELECT 1 
    		FROM jsonb_array_elements(subjects) AS j(data) 
    		WHERE (data @> '{"subject": "B/MATH"}') 
    		AND data @> '{"grade": "A"}'
    		OR data @> '{"grade": "B"}'
    		OR data @> '{"grade": "C"}'
    	)
    AND
    	"examYear" = '2010'
    AND
    	"examType" = 'CSEE'
    ;

But this gave me a reversed effect (3 secs query) and non true results (my ORs are to blame).


Here is my explain analyze block.

    "Aggregate  (cost=1888617.19..1888617.20 rows=1 width=8) (actual time=2517.116..2517.117 rows=1 loops=1)"
    "  ->  Bitmap Heap Scan on results  (cost=470767.18..1888021.96 rows=238090 width=0) (actual time=680.456..2514.633 rows=24002 loops=1)"
    "        Recheck Cond: ("examYear" = '2010'::text)"
    "        Rows Removed by Index Recheck: 557945"
    "        Filter: (("examType" = 'CSEE'::text) AND (SubPlan 1))"
    "        Rows Removed by Filter: 500054"
    "        Heap Blocks: exact=40156 lossy=53452"
    "        ->  Bitmap Index Scan on idx_results_subjects  (cost=0.00..470707.66 rows=528405 width=0) (actual time=672.375..672.375 rows=524056 loops=1)"
    "              Index Cond: ("examYear" = '2010'::text)"
    "        SubPlan 1"
    "          ->  Function Scan on jsonb_array_elements j  (cost=0.00..2.13 rows=1 width=0) (actual time=0.003..0.003 rows=0 loops=458487)"
    "                Filter: (((data #>> '{subject}'::text[]) ~~ '%MATH%'::text) AND ((data #>> '{grade}'::text[]) = ANY ('{A,B,C}'::text[])))"
    "                Rows Removed by Filter: 8"
    "Planning time: 0.126 ms"
    "Execution time: 2517.145 ms"


I'm on postgresql: 9.6.1