[GENERAL] JSON Indexes

2014-06-24 Thread 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...







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

Re: [GENERAL] JSON Indexes

2014-06-24 Thread Merlin Moncure 
On Tue, Jun 24, 2014 at 3:15 PM, CS_DBA cs_...@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

Re: [GENERAL] JSON Indexes

2014-06-24 Thread 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


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