Возник довольно нетривиальный вопрос, ищу совета в какую сторону копать. Есть

query:

with "event_rides" as (select distinct ride_id
from ride_delivery_event e
where e.jsonb_data -> 'riders' <> 'null'::jsonb
AND (object_keys(e.jsonb_data -> 'riders') && ARRAY ['id1', 'id2'])
select *
from ride_delivery as r
where ride_id IN (SELECT ride_id FROM student_rides)
AND r.ride_status IN ('STATUS1', 'STATUS2')
r.ride_start_timestamp > timestamp1 AND r.ride_start_timestamp < timestamp2;


object_keys - функция которая схлопывает jsonb_object_keys в array

select array_agg(jsonb_object_keys) from jsonb_object_keys($1)


Есть gin index:

create index riders_id_index
on ride_delivery_event using gin (object_keys(data -> 'riders'::text))
where ((data -> 'riders'::text) <> 'null'::jsonb);


и на ride_delivery присутствует индекс по ride_id:

create unique index ride_id_index
on ride_delivery (ride_id);

Analyze выдает верное использование индексов и лайтэнси < 100ms

| QUERY PLAN |
| :--- |
| Sort \(cost=2612497.02..2612498.07 rows=418 width=61\) \(actual time=4.731..4.732 rows=0 loops=1\) |
| Sort Key: rd.ride\_start\_timestamp |
| Sort Method: quicksort Memory: 25kB |
| CTE student\_rides |
| -&gt; Unique \(cost=2608428.33..2609008.35 rows=78919 width=21\) \(actual time=4.583..4.590 rows=14 loops=1\) |
| -&gt; Sort \(cost=2608428.33..2608718.34 rows=116005 width=21\) \(actual time=4.582..4.584 rows=30 loops=1\) |
| Sort Key: rde.ride\_id |
| Sort Method: quicksort Memory: 27kB |
| -&gt; Bitmap Heap Scan on ride\_delivery\_event rde \(cost=1851.04..2596290.09 rows=116005 width=21\) \(actual time=4.525..4.559 rows=30 loops=1\) |
| Recheck Cond: \(\(object\_keys\(\(data -&gt; 'riderAttendance'::text\)\) && '{-MokXwt3ZVHT2IaFzrOB,-NSRzAJqTxRlP9ZsnByq,-NSRzAJqTxRlP9ZsnByq}'::text\[\]\) AND \(\(data -&gt; 'riderAttendance'::text\) &lt;&gt; 'null'::jsonb\)\) |
| Heap Blocks: exact=29 |
| -&gt; Bitmap Index Scan on ride\_delivery\_event\_rider\_id\_index \(cost=0.00..1822.03 rows=116005 width=0\) \(actual time=4.513..4.513 rows=30 loops=1\) |
| Index Cond: \(object\_keys\(\(data -&gt; 'riderAttendance'::text\)\) && '{-MokXwt3ZVHT2IaFzrOB,-NSRzAJqTxRlP9ZsnByq,-NSRzAJqTxRlP9ZsnByq}'::text\[\]\) |
| -&gt; Nested Loop \(cost=1776.11..3470.47 rows=418 width=61\) \(actual time=4.728..4.729 rows=0 loops=1\) |
| -&gt; HashAggregate \(cost=1775.68..1777.68 rows=200 width=32\) \(actual time=4.602..4.605 rows=14 loops=1\) |
| Group Key: student\_rides.ride\_id |
| -&gt; CTE Scan on student\_rides \(cost=0.00..1578.38 rows=78919 width=32\) \(actual time=4.584..4.595 rows=14 loops=1\) |
| -&gt; Index Scan using ride\_delivery\_ride\_id\_index on ride\_delivery rd \(cost=0.43..8.45 rows=1 width=754\) \(actual time=0.008..0.008 rows=0 loops=14\) |
| Index Cond: \(ride\_id = student\_rides.ride\_id\) |
| Filter: \(\('2023-11-07 14:18:17-08'::timestamp with time zone &lt;= ride\_start\_timestamp\) AND \(ride\_status = ANY \('{SCHEDULED,DRIVER\_ENROUTE,IN\_PROGRESS}'::text\[\]\)\)\) |
| Rows Removed by Filter: 1 |
| Planning Time: 0.306 ms |
| Execution Time: 4.780 ms |


Но периодически случается ивент, когда эта квери начинает выполняться по 60+ секунд, CPU из-за этого спайкает под 100% и база становится unresponsive.
В какую сторону смотреть, может кто-то сталкивался с похожей ситуацией?

В сторону auto_explain, pg_stat_statements, log_lock_waits и т.п. — чтобы поймать "периодически случается ивент" и получить о нём какую-то информацию.

А посоветуйте почитать что-нибудь по этим техникам?

Поищите в документации PostgreSQL по этим ключевым словам — там всё довольно просто.

У вас одно ядро?

4 ядра, 64Gb