Обсуждение: JSON Indexes

Поиск
Список
Период
Сортировка

JSON Indexes

От
CS_DBA
Дата:
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...

Re: JSON Indexes

От
Merlin Moncure
Дата:
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

Re: JSON Indexes

От
Vik Fearing
Дата:
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