JSON Indexes

On Tue, Jun 24, 2014 at 3:15 PM, CS_DBA <cs_dba@consistentstate.com> wrote:

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?

yes.

first of all, your create index doesn't work for me: I get:

postgres=# create index mytest_json_col_idx on mytest ((task->'name'));
ERROR: data type json has no default operator class for access method "btree"

now, if you change it to:
create index mytest_json_col_idx on mytest ((task->>'name'));

it works. Next, try disabling seq_scan if you want to force an index
scan. It is not a given that a full table count/group by is better
done via an index.

merlin

--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

On 06/24/2014 10:15 PM, CS_DBA wrote:

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;

Am I missing something?

Yes, you're querying task->>'name' but the index is on task->'name'.
--
Vik

--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general