Optimize Postgres query - Bitmap heap scan seems slow-CodePudding

I'm testing a query and wondering if performance can be improved. There are 9 million records in this table. The database used is Postgres 12. The query should be quick for any account, docket type, quantity.

SELECT d.docket_id, d.docket_type_id
FROM docket d
WHERE d.account_id = 557  AND (d.docket_type_id = ANY(array[2]))
AND exists (
 select 1
 from jsonb_array_elements(d.equipment) as eq
 where (eq->'quantity')::integer >= 150)
order by docket_id desc
--limit 500

CodePudding user response：

There is no good way to directly index jsonb for the existence of a value >= some constant.

You could write a function to summarize the jsonb to the maximum value, index that, then test if this maximum is >= your constant:

create function jsonb_max(jsonb, text) returns integer language sql as $$
   select max((e->>$2)::int) from jsonb_array_elements($1) f(e); 
$$ immutable parallel safe;

create index on docket (account_id , docket_type_id, jsonb_max(equipment,'quantity'));

SELECT d.docket_id, d.docket_type_id
FROM docket d
WHERE d.account_id = 557  
   AND (d.docket_type_id = ANY(array[2])) 
   AND jsonb_max(equipment,'quantity')>=150
order by docket_id desc