Search code examples
postgresqljsonb

Postgresql gin index high cpu usage ratio

I design a table with jsonb column to store userlist, and I set up a gin index on that column.

select * from table where userlist @> '"Username":"a01"'::josnb

My data type in table like

`[{"Username":"a01"},{"Username":"a02"},{"Username":"a03"}....`]

When I had backend unit test, the query speed was fast.

But when I wrote a function to call this sp, I found it had very high cpu usage ratio problems(almost 100%).

After I vacuum table and reindex this DB, it down to 80%, but it's still high.

I don't know how to decrease the problem. if u have any idea, I am looking forward your suggestions, thank you.

DECLARE @EndCNT;
SET @EndCNT = 10000;

WHILE @EndCNT > 0
BEGIN

SELECT public.query_cust('a01');
SET @EndCNT = @EndCNT - 1;
END
Solution

  • Finally, I found it's jsonb data type problem.

    If you store too long jsonb data, when system load data into memory, it makes unavoidable high CPU ratio usage.