Drop Down MenusCSS Drop Down MenuPure CSS Dropdown Menu

HOW TO CREATE INDEX ON JSON TABLE AND HOW TO CHECK THE INDEX WHETHER PROPERLY WORKING OR NOT

  • index is fast fetching data without going full table scan.
Here  i will explain how to create index on json data type table and how to  work index on json data type.
--check the sales table for already having any index or not.
postgres=# \d sales
       Table "public.sales"
   Column   |  Type   | Modifiers 
------------+---------+-----------
 id         | integer | not null
 sales_info | json    | not null
Indexes:
    "sales_pkey" PRIMARY KEY, btree (id)


postgres=# \di sales
No matching relations found.  
--already we have sales table now let us go to create indicies on any (even nested) JSON field:
create unique index ind_name
on sales ((sales_info ->'PRODUCTS'->>'total_item'));
--Now check the table index is properly created or not using \di,\dt,pg_indexes.
postgres=# \di ind_name
              List of relations
 Schema |   Name   | Type  |  Owner   | Table 
--------+----------+-------+----------+-------
 public | ind_name | index | postgres | sales
(1 row)



Deleting json objectspostgres=# \d sales
       Table "public.sales"
   Column   |  Type   | Modifiers 
------------+---------+-----------
 id         | integer | not null
 sales_info | json    | not null
Indexes:
    "sales_pkey" PRIMARY KEY, btree (id)
    "ind_name" UNIQUE, btree (((sales_info -> 'PRODUCTS'::text) ->> 'total_item'::text))



postgres=# select tablename,indexname,indexdef from pg_indexes where tablename='sales';
 tablename | indexname  |                                                    indexdef                       
                              
-----------+------------+-----------------------------------------------------------------------------------
------------------------------
 sales     | sales_pkey | CREATE UNIQUE INDEX sales_pkey ON sales USING btree (id)
 sales     | ind_name   | CREATE UNIQUE INDEX ind_name ON sales USING btree ((((sales_info -> 'PRODUCTS'::te
xt) ->> 'total_item'::text)))
(2 rows)
 From below example index is  properly working becouse we already created unique index on on json column of "total_item" unique index will not accept duplicate value if you try to insert duplicate values it will throw errors,i hope you understood it from below example.
postgres=# select * from sales;
 id |                                    sales_info                                    
----+----------------------------------------------------------------------------------
  2 | { "customer": "ABU", "PRODUCTS": {"product_name": "badam","total_item": 5}}
  3 | { "customer": "UMAR", "PRODUCTS": {"product_name": "mobile","total_item": 1}}
  5 | { "customer": "NIJAM", "PRODUCTS": {"product_name": "choclate","total_item": 9}}
  1 | { "customer": "ABU", "PRODUCTS": {"product_name": "badam","total_item": 6}}
  4 | { "customer": "UMAR", "PRODUCTS": {"product_name": "mobile","total_item": 8}}
(5 rows)


postgres=# INSERT INTO SALES VALUES
 (9,'{ "customer": "UMAR", "PRODUCTS": {"product_name": "mobile","total_item": 8}}'
 );
ERROR:  duplicate key value violates unique constraint "ind_name"
DETAIL:  Key (((sales_info -> 'PRODUCTS'::text) ->> 'total_item'::text))=(8) already exists. 
another example for json index is properly working or not
HERE LET US consider  sales table having above 20000 json records for making practical json index , This way we can begin to see some performance implications when dealing with JSON data in Postgres, as well as how to solve them.

SELECT count(*) FROM sales WHERE sales_info ->'PRODUCTS'->>'total_item'= '1';
count
-------
4937 
(1 row) 
--taking explain plan for without index
postgres=# explain analyze SELECT sales_info ->>'customer' as customer_name from sales 
where sales_info ->'PRODUCTS'->>'total_item'= '1';
Aggregate (cost=335.12..335.13 rows=1 width=0) (actual time=4.421..4.421 rows=1 loops=1) -> Seq Scan on cards (cost=0.00..335.00 rows=50 width=0) (actual time=0.016..3.961 rows=4938 loops=1) 
    Filter: (((sales_info -> 'PRODUCTS'::text) ->> 'total_item'::text) = '1'::text) 
    Rows Removed by Filter: 5062 
Total runtime: 4.475 ms$cd /db_data/data/pg_log
$tail -f  postgresql-Tue.log  
Now, that wasn’t that slow of a query at 4.5ms, but let’s see if we can improve it.
CREATE INDEX ind_name1 ON sales ((sales_info ->'PRODUCTS'->>'total_item')); 
If we run the same query which now has an index, we end up cutting the time in half.
SELECT count(*) FROM sales WHERE sales_info ->'PRODUCTS'->>'total_item'= '1';
count
-------
4937 
(1 row)

postgres=# explain analyze SELECT sales_info ->>'customer' as customer_name from sales 
where sales_info ->'PRODUCTS'->>'total_item'= '1';
Aggregate (cost=118.97..118.98 rows=1 width=0) (actual time=2.122..2.122 rows=1 loops=1) -> Bitmap Heap Scan on cards (cost=4.68..118.84 rows=50 width=0) (actual time=0.711..1.664 rows=4938 loops=1) 
    Recheck Cond: (((sales_info -> 'PRODUCTS'::text) ->> 'total_item'::text) = '1'::text)  
    Heap Blocks: exact=185 
    -> Bitmap Index Scan on idxfinished (cost=0.00..4.66 rows=50 width=0) (actual time=0.671..0.671 rows=4938 loops=1) 
    Index Cond:(((sales_info -> 'PRODUCTS'::text) ->> 'total_item'::text) = '1'::text) 
Total runtime: 2.175 ms 
Our query is now taking advantage of the ind_name1 index we created, and the query time has been approximately cut in half.

Comments

Popular posts from this blog

PostgreSQL Pgbadger Installation On Linux

PostgreSQL pgBadger

Migrating From Oracle to PostgreSQL using ora2pg open source tools

How To Schedule PostgreSQL Jobs using pgAgent on Linux plateform

How to configure Replication Manager (repmgr) ?