请问如何优化我这个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+

PostgreSQL

魔性D小小蝉 9 years, 7 months ago

Your Answer