|
查询tbl_user_json表的一条测试数据，如下：
- mydb=> SELECT * FROM tbl_user_json LIMIT 1;
- id | user_info
- ---------+------------------------------------------------------------------------------------
- 2000001 | {"id":1,"user_id":1182883,"user_name":"1_francs","create_time":"2017-08-03T20:59:27.42741+08:00"}
- (1 row)
3、JSON与JSONB表读性能测试
对于json、jsonb读性能测试我们选择基于json、jsonb键值查询的场景，例如，根据user_info字段的user_name键的值查询，如下所示：
- mydb=> EXPLAIN ANALYZE SELECT * FROM tbl_user_jsonb WHERE user_info->>'user_name'='1_francs';
- QUERY PLAN
- -------------------------------------------------------------------------------------
- Seq Scan on tbl_user_jsonb (cost=0.00..72859.90 rows=10042 width=143) (actual time=0.023..524.843 rows=1 loops=1)
- Filter: ((user_info ->> 'user_name'::text) = '1_francs'::text)
- Rows Removed by Filter: 1999999
- Planning time: 0.091 ms
- Execution time: 524.876 ms
- (5 rows)
上述SQL执行时间为524毫秒左右，基于user_info字段的user_name键值创建btree索引如下：
- mydb=> CREATE INDEX idx_jsonb ON tbl_user_jsonb USING btree ((user_info->>'user_name'));
再次执行上述查询，如下所示：
- Bitmap Heap Scan on tbl_user_jsonb (cost=155.93..14113.93 rows=10000 width=143) (actual time=0.027..0.027 rows=1 loops=1)
- Recheck Cond: ((user_info ->> 'user_name'::text) = '1_francs'::text)
- Heap Blocks: exact=1
- -> Bitmap Index Scan on idx_jsonb (cost=0.00..153.43 rows=10000 width=0) (actual time=0.021..0.021 rows=1 loops=1)
- Index Cond: ((user_info ->> 'user_name'::text) = '1_francs'::text)
- Planning time: 0.091 ms
- Execution time: 0.060 ms
- (7 rows)
根据上述执行计划看出走了索引，并且SQL时间下降到0.060ms。为更好地对比tbl_user_json、tbl_user_jsonb表基于键值查询的效率，计划根据user_info字段id键进行范围扫描对比性能，创建索引如下：
- mydb=> CREATE INDEX idx_gin_user_info_id ON tbl_user_json USING btree
- (((user_info ->> 'id')::integer));
- CREATE INDEX
- mydb=> CREATE INDEX idx_gin_user_infob_id ON tbl_user_jsonb USING btree
索引创建后，查询tbl_user_json表如下：
- mydb=> EXPLAIN ANALYZE SELECT id,user_info->'id',user_info->'user_name'
- FROM tbl_user_json
- WHERE (user_info->>'id')::int4>1 AND (user_info->>'id')::int4<10000;
- Bitmap Heap Scan on tbl_user_json (cost=166.30..14178.17 rows=10329 width=68) (actual time=1.167..26.534 rows=9998 loops=1)
- Recheck Cond: ((((user_info ->> 'id'::text))::integer > 1) AND (((user_info ->> 'id'::text))::integer < 10000))
- Heap Blocks: exact=338
- -> Bitmap Index Scan on idx_gin_user_info_id (cost=0.00..163.72 rows=10329 width=0) (actual time=1.110..1.110 rows=19996 loops= 1)
- Index Cond: ((((user_info ->> 'id'::text))::integer > 1) AND (((user_info ->> 'id'::text))::integer < 10000))
- Planning time: 0.094 ms
- Execution time: 27.092 ms
- (7 rows)
（编辑：好传媒网）
【声明】本站内容均来自网络，其相关言论仅代表作者个人观点，不代表本站立场。若无意侵犯到您的权利，请及时与联系站长删除相关内容!
|
