请问如何优化我这个pg的查询
我有一个表
CREATE TABLE tbl_article (
uid TEXT PRIMARY KEY,
...
tags JSONB
)
CREATE INDEX idxgin ON tbl_article USING gin (tags);
tags
就是一个json格式的,uid是我程序里生成的id,类似mongodb的ObjectID。
如下查询:
SELECT * FROM tbl_article
WHERE tags @> '{"Category":0}'::jsonb
ORDER BY uid DESC LIMIT 20 OFFSET 10000
Explain如下:
Limit (cost=971.77..971.77 rows=1 width=1047) (actual time=121.811..121.811 rows=0 loops=1)
-> Sort (cost=971.46..971.77 rows=125 width=1047) (actual time=110.653..121.371 rows=8215 loops=1)
Sort Key: uid
Sort Method: external merge Disk: 8736kB
-> Bitmap Heap Scan on tbl_article (cost=496.97..967.11 rows=125 width=1047) (actual time=5.292..14.504 rows=8215 loops=1)
Recheck Cond: (tags @> '{"Category": 0}'::jsonb)
Heap Blocks: exact=3521
-> Bitmap Index Scan on idxgin (cost=0.00..496.93 rows=125 width=0) (actual time=4.817..4.817 rows=8216 loops=1)
Index Cond: (tags @> '{"Category": 0}'::jsonb)
Planning time: 0.105 ms
Execution time: 123.016 ms
这应该是查询的时间还算可以,但是排序的时间很长,请问这里改如何优化呢?
................附加内容.......................
我是在virtualbox里跑个ubuntu,512M内存,单核i5,后来把
work_mem
提高到100MB,sort变成了了quicksort了,时间仍然需要100ms+
魔性D小小蝉
9 years, 7 months ago