From: | Merlin Moncure <mmoncure(at)gmail(dot)com> |
---|---|
To: | CS_DBA <cs_dba(at)consistentstate(dot)com> |
Cc: | PostgreSQL General <pgsql-general(at)postgresql(dot)org> |
Subject: | Re: JSON Indexes |
Date: | 2014-06-24 21:16:35 |
Message-ID: | CAHyXU0w7Y7a1FSrrgbZWecomynsCsieQAX7U=he-+hK9o+jjNg@mail.gmail.com |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-general |
On Tue, Jun 24, 2014 at 3:15 PM, CS_DBA <cs_dba(at)consistentstate(dot)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
From | Date | Subject | |
---|---|---|---|
Next Message | Vik Fearing | 2014-06-24 21:16:59 | Re: JSON Indexes |
Previous Message | Adrian Klaver | 2014-06-24 21:05:29 | Re: Re: Error When Trying to Use Npgsql to COPY into a PostgreSQL Database |