From: | CS_DBA <cs_dba(at)consistentstate(dot)com> |
---|---|
To: | PostgreSQL General <pgsql-general(at)postgresql(dot)org> |
Subject: | JSON Indexes |
Date: | 2014-06-24 20:15:56 |
Message-ID: | 53A9DC7C.4080602@consistentstate.com |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-general |
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...
From | Date | Subject | |
---|---|---|---|
Next Message | Adrian Klaver | 2014-06-24 20:30:45 | Re: Error When Trying to Use Npgsql to COPY into a PostgreSQL Database |
Previous Message | Heikki Linnakangas | 2014-06-24 17:49:05 | Re: Extended Prefetching using Asynchronous IO - proposal and patch |