JSON Indexes

CS_DBA <cs_dba@xxxxxxxxxxxxxxxxxxx> · Tue, 24 Jun 2014 14:15:56 -0600

Hi All;

We're struggling to get the planner to use a json index.

I have this table :

                              Table "public.mytest"

 Column |       Type        | Modifiers

--------+-------------------+-----------------------------------------------------

 id     | bigint            | not null default 
nextval('events_id_seq'::regclass)

 task  | json              |

I added a PK constraint on the id column and created this json index:

create index mytest_json_col_idx on mytest ((task->'name'));

However the planner never uses the index...

EXPLAIN SELECT (mytest.task->>'name') as name,

COUNT((mytest.task->>'name')) AS task_count

FROM mytest

GROUP BY (mytest.task->>'name')

ORDER BY 2  DESC;

                               QUERY PLAN

-----------------------------------------------------------------------------

 Sort  (cost=155097.84..155098.34 rows=200 width=32)

   Sort Key: (count(((task ->> 'name'::text))))

   ->  HashAggregate  (cost=155087.70..155090.20 rows=200 width=32)

         ->  Seq Scan on mytab  (cost=0.00..149796.94 rows=705435 width=32)

Am I missing something?

Thanks in advance...