добрый, нет, я пока отложил эту задачу

но если есть идеи - выслушаю)

Рейтинг в виде числа ?
Выглядит как избыточная нормализация

Рейтинг в отдельной таблице. uuid + название рейтинга

deduplication detected

this is "many to one"

1) через INTERSECT:

SELECT client_id FROM log WHERE created_at >= ‘2021-03-09 09:00:00’ AND created_at  < ‘2021-03-09 10:00:00’
INTERSECT
SELECT client_id FROM log WHERE created_at >= ‘2021-03-09 11:00:00’ AND created_at  < ‘2021-03-09 11:30:00’
INTERSECT
SELECT client_id FROM log WHERE created_at >= ‘2021-03-09 12:00:00’ AND created_at  < ‘2021-03-09 14:00:00’ AND user_agent = ‘mobile’

2) через JOIN-ы на саму себя


SELECT * FROM log l1
   JOIN log l2 ON (l2.client_id = l1.client_id)
   JOIN log l3 ON (l3.client_id = l2.client_id)
WHERE
   l1.created_at >= ‘2021-03-09 09:00:00’ AND l1.created_at  < ‘2021-03-09 10:00:00’
   AND l2.created_at >= ‘2021-03-09 11:00:00’ AND l2.created_at  < ‘2021-03-09 11:30:00’
   AND l3.created_at >= ‘2021-03-09 12:00:00’ AND l3.created_at  < ‘2021-03-09 14:00:00’ AND l3.user_agent = ‘mobile’

SELECT client_id FROM log WHERE created_at >= ‘2021-03-09 09:00:00’ AND created_at  < ‘2021-03-09 10:00:00’
INTERSECT
SELECT client_id FROM log WHERE created_at >= ‘2021-03-09 11:00:00’ AND created_at  < ‘2021-03-09 11:30:00’
INTERSECT
SELECT client_id FROM log WHERE created_at >= ‘2021-03-09 12:00:00’ AND created_at  < ‘2021-03-09 14:00:00’ AND user_agent = ‘mobile’

не, задача немного в другом состоит
пользователь, находясь на странице, через js шлет запросы, что я такой-то uid, нахожусь на такой-то странице в такое-то время
шлет он это раз в 10 секунд (условно)
тут нет события "я пришел на страницу" и "я ушел со страницы"
мне надо узнать промежутки, когда пользователь посещал вот эту конкретную страницу, сколько он провел на ней времени
то есть первая запись появилась в 9.00 утра, а последняя запись с 10 секундным интервалом засветилась в 10.00, а потом пользователь опять начал отсылать запросы в 11.00 - это уже другой период надо записывать

То есть, нужно найти периоды непрерывных 10-секундных логирований? Если между отметками больше 10 секунд - это уже другая сессия?

типа того, да

в плане частоты обновления, да, не часто, точнее раз в 12 часов.

 Gather  (cost=1000.57..708159.38 rows=599247 width=34) (actual time=0.443..2211.231 rows=656211 loops=1)
   Workers Planned: 4
   Workers Launched: 4
   Buffers: shared hit=3306333 read=251357
   ->  Nested Loop  (cost=0.56..647234.68 rows=149812 width=34) (actual time=0.122..2100.391 rows=131242 loops=5)
         Buffers: shared hit=3306333 read=251357
         ->  Parallel Seq Scan on "AO_8542F1_IFJ_OBJ_ATTR" oa  (cost=0.00..423486.20 rows=150497 width=8) (actual time=0.060..1047.498 rows=131242 loops=5)
               Filter: ("OBJECT_TYPE_ATTRIBUTE_ID" = ANY ('{529,10,506,143}'::integer[]))
               Rows Removed by Filter: 7891082
               Buffers: shared hit=21702 read=251357
         ->  Index Scan using index_ao_8542f1_ifj228666017 on "AO_8542F1_IFJ_OBJ_ATTR_VAL" oav  (cost=0.56..1.48 rows=1 width=42) (actual time=0.008..0.008 rows=1 loops=656211)
               Index Cond: ("OBJECT_ATTRIBUTE_ID" = oa."ID")
               Buffers: shared hit=3284631
 Planning time: 0.279 ms
 Execution time: 2238.087 ms 

что-то ничего не дало,

Table "public.AO_8542F1_IFJ_OBJ_ATTR"
          Column          |  Type   |                               Modifiers
--------------------------+---------+-----------------------------------------------------------------------
 ID                       | bigint  | not null default nextval('"AO_8542F1_IFJ_OBJ_ATTR_ID_seq"'::regclass)
 OBJECT_ID                | integer |
 OBJECT_TYPE_ATTRIBUTE_ID | integer |
 UPDATED                  | bigint  |
Indexes:
    "AO_8542F1_IFJ_OBJ_ATTR_pkey" PRIMARY KEY, btree ("ID")
    "index_ao_8542f1_ifj268009346" btree ("OBJECT_TYPE_ATTRIBUTE_ID")
    "index_ao_8542f1_ifj43488772" btree ("OBJECT_ID")
    "index_ao_insight_1" btree ("OBJECT_TYPE_ATTRIBUTE_ID", "ID")
Foreign-key constraints:
    "fk_ao_8542f1_ifj_obj_attr_object_id" FOREIGN KEY ("OBJECT_ID") REFERENCES "AO_8542F1_IFJ_OBJ"("ID")
    "fk_ao_8542f1_ifj_obj_attr_object_type_attribute_id" FOREIGN KEY ("OBJECT_TYPE_ATTRIBUTE_ID") REFERENCES "AO_8542F1_IFJ_OBJ_TYPE_ATTR"("ID")
Referenced by:
    TABLE ""AO_8542F1_IFJ_OBJ_ATTR_VAL"" CONSTRAINT "fk_ao_8542f1_ifj_obj_attr_val_object_attribute_id" FOREIGN KEY ("OBJECT_ATTRIBUTE_ID") REFERENCES "AO_8542F1_IFJ_OBJ_ATTR"("ID")

как я вижу в параметре where одинаковые параметры, частичный индекс поможет?

поскольку индекс не взлетел

Тут я уже не буду пробовать сочинять SQL, потому что сложно, но концепция такая:
1) Применить на всю последовательность записей для одного пользователя оконную функцию, которая находит разницу по времени между двумя соседними записями.

2) Там, где разница больше условных 10 секунд - это граница интервала.

3) Внешним запросом пройтись по результатам нахождения границ и уже собрать интервалы в тот вид, который нужен на выходе.

Подскажите, пожалуйста, в связи с чем могла быть вызвана Parameters exist but IPD isn't set. Please call SQLDescribeParam()

Спасибо.

Если они всегда одинаковые прямо, то вполне может помочь. Зависит от соотношения количества строк, попадающих под условие к количеству строк в таблице. Но у вас реально процентов 5 строк попадает под условие и планировщик это более-менее адекватно оценивает. Так что достаточно странно что он у вас в принципе seq scan предпочитает на даже имеющихся сейчас  индексах. Хотя, с другой стороны,  перезапись раз в 12 часов может и не позволять не заглядывать в visibility map.
Попробуйте частичный, если условия позволяют использовать его. Вам просто нужно сделать так, чтобы с диска читалось минимум данных, в идеале вообще чтобы нужное в кэш влезало и не вымывалось.

Кстати говоря, оперативной памяти то вообще на виртуалке хватает или всё грустно? Настройки постгреса, насколько могу судить, не дефолтные, но непонятно насколько соответствуют количеству оперативной памяти опять же. Можно и в эту сторону быстро посмотреть на предмет возможного устранения чтения с дисков.

А он вообще может использоваться?
Попробуйте отключить seq.scan (и т.д.), и посмотреть (и заодно проверить оценки).

  Gather  (cost=1000.57..708213.47 rows=599273 width=34) (actual time=0.598..2202.731 rows=656324 loops=1)
   Workers Planned: 4
   Workers Launched: 4
   Buffers: shared hit=3296018 read=262261
   ->  Nested Loop  (cost=0.56..647286.17 rows=149818 width=34) (actual time=0.127..2101.260 rows=131265 loops=5)
         Buffers: shared hit=3296018 read=262261
         ->  Parallel Seq Scan on "AO_8542F1_IFJ_OBJ_ATTR" oa  (cost=0.00..423521.28 rows=150508 width=8) (actual time=0.070..1007.676 rows=131265 loops=5)
               Filter: ("OBJECT_TYPE_ATTRIBUTE_ID" = ANY ('{529,10,506,143}'::integer[]))
               Rows Removed by Filter: 7891993
               Buffers: shared hit=10822 read=262261
         ->  Index Scan using index_ao_8542f1_ifj228666017 on "AO_8542F1_IFJ_OBJ_ATTR_VAL" oav  (cost=0.56..1.48 rows=1 width=42) (actual time=0.008..0.008 rows=1 loops=656324)
               Index Cond: ("OBJECT_ATTRIBUTE_ID" = oa."ID")
               Buffers: shared hit=3285196
 Planning time: 0.431 ms
 Execution time: 2225.289 ms
(15 rows) 

не помог,

                                   Table "public.AO_8542F1_IFJ_OBJ_ATTR"
          Column          |  Type   |                               Modifiers
--------------------------+---------+-----------------------------------------------------------------------
 ID                       | bigint  | not null default nextval('"AO_8542F1_IFJ_OBJ_ATTR_ID_seq"'::regclass)
 OBJECT_ID                | integer |
 OBJECT_TYPE_ATTRIBUTE_ID | integer |
 UPDATED                  | bigint  |
Indexes:
    "AO_8542F1_IFJ_OBJ_ATTR_pkey" PRIMARY KEY, btree ("ID")
    "index_ao_8542f1_ifj268009346" btree ("OBJECT_TYPE_ATTRIBUTE_ID")
    "index_ao_8542f1_ifj43488772" btree ("OBJECT_ID")
    "index_ao_insight_2" btree ("ID") WHERE "OBJECT_TYPE_ATTRIBUTE_ID" = ANY (ARRAY[529, 10, 506, 143])
Foreign-key constraints:
    "fk_ao_8542f1_ifj_obj_attr_object_id" FOREIGN KEY ("OBJECT_ID") REFERENCES "AO_8542F1_IFJ_OBJ"("ID")
    "fk_ao_8542f1_ifj_obj_attr_object_type_attribute_id" FOREIGN KEY ("OBJECT_TYPE_ATTRIBUTE_ID") REFERENCES "AO_8542F1_IFJ_OBJ_TYPE_ATTR"("ID")
Referenced by:
    TABLE ""AO_8542F1_IFJ_OBJ_ATTR_VAL"" CONSTRAINT "fk_ao_8542f1_ifj_obj_attr_val_object_attribute_id" FOREIGN KEY ("OBJECT_ATTRIBUTE_ID") REFERENCES "AO_8542F1_IFJ_OBJ_ATTR"("ID")

в плане, памяти,

 
              total        used        free      shared  buff/cache   available
Mem:          24947        2118         207        5803       22620       16659
Swap:          3071           0        3071
Total:        28019        2118        3279 

max_wal_size = 4GB
min_wal_size = 1GB
checkpoint_completion_target = 0.7
effective_io_concurrency = 200          # 1-1000; 0 disables prefetching
max_worker_processes = 8                # (change requires restart)
max_parallel_workers_per_gather = 4
work_mem = 8987kB
maintenance_work_mem = 1408MB
shared_buffers = 6240MB

временно добавлял
ALTER SYSTEM SET shared_buffers = '10GB';

Привет. подскажите, пожалуйста, есть ли способ при группировке в запросе аггрегировать по одному полю кортежа, но вытаскивать другое. Кейс: нужно было выбрать самый старый кейс для каждого пациента.

Выкрутились с помощью конкатенации строк, но мне кажется, что задача довольно популярная, чтобы существовал более адекватное решение.
substring(min((resource->>'date') || '__' || id), 13)

Есть опция сделать свою аггергационную функцию, но это по странности идет наравне с конкатенацией и substring.

  Gather  (cost=1000.57..708213.47 rows=599273 width=34) (actual time=0.598..2202.731 rows=656324 loops=1)
   Workers Planned: 4
   Workers Launched: 4
   Buffers: shared hit=3296018 read=262261
   ->  Nested Loop  (cost=0.56..647286.17 rows=149818 width=34) (actual time=0.127..2101.260 rows=131265 loops=5)
         Buffers: shared hit=3296018 read=262261
         ->  Parallel Seq Scan on "AO_8542F1_IFJ_OBJ_ATTR" oa  (cost=0.00..423521.28 rows=150508 width=8) (actual time=0.070..1007.676 rows=131265 loops=5)
               Filter: ("OBJECT_TYPE_ATTRIBUTE_ID" = ANY ('{529,10,506,143}'::integer[]))
               Rows Removed by Filter: 7891993
               Buffers: shared hit=10822 read=262261
         ->  Index Scan using index_ao_8542f1_ifj228666017 on "AO_8542F1_IFJ_OBJ_ATTR_VAL" oav  (cost=0.56..1.48 rows=1 width=42) (actual time=0.008..0.008 rows=1 loops=656324)
               Index Cond: ("OBJECT_ATTRIBUTE_ID" = oa."ID")
               Buffers: shared hit=3285196
 Planning time: 0.431 ms
 Execution time: 2225.289 ms
(15 rows) 

                                   Table "public.AO_8542F1_IFJ_OBJ_ATTR"
          Column          |  Type   |                               Modifiers
--------------------------+---------+-----------------------------------------------------------------------
 ID                       | bigint  | not null default nextval('"AO_8542F1_IFJ_OBJ_ATTR_ID_seq"'::regclass)
 OBJECT_ID                | integer |
 OBJECT_TYPE_ATTRIBUTE_ID | integer |
 UPDATED                  | bigint  |
Indexes:
    "AO_8542F1_IFJ_OBJ_ATTR_pkey" PRIMARY KEY, btree ("ID")
    "index_ao_8542f1_ifj268009346" btree ("OBJECT_TYPE_ATTRIBUTE_ID")
    "index_ao_8542f1_ifj43488772" btree ("OBJECT_ID")
    "index_ao_insight_2" btree ("ID") WHERE "OBJECT_TYPE_ATTRIBUTE_ID" = ANY (ARRAY[529, 10, 506, 143])
Foreign-key constraints:
    "fk_ao_8542f1_ifj_obj_attr_object_id" FOREIGN KEY ("OBJECT_ID") REFERENCES "AO_8542F1_IFJ_OBJ"("ID")
    "fk_ao_8542f1_ifj_obj_attr_object_type_attribute_id" FOREIGN KEY ("OBJECT_TYPE_ATTRIBUTE_ID") REFERENCES "AO_8542F1_IFJ_OBJ_TYPE_ATTR"("ID")
Referenced by:
    TABLE ""AO_8542F1_IFJ_OBJ_ATTR_VAL"" CONSTRAINT "fk_ao_8542f1_ifj_obj_attr_val_object_attribute_id" FOREIGN KEY ("OBJECT_ATTRIBUTE_ID") REFERENCES "AO_8542F1_IFJ_OBJ_ATTR"("ID")

 
              total        used        free      shared  buff/cache   available
Mem:          24947        2118         207        5803       22620       16659
Swap:          3071           0        3071
Total:        28019        2118        3279 

max_wal_size = 4GB
min_wal_size = 1GB
checkpoint_completion_target = 0.7
effective_io_concurrency = 200          # 1-1000; 0 disables prefetching
max_worker_processes = 8                # (change requires restart)
max_parallel_workers_per_gather = 4
work_mem = 8987kB
maintenance_work_mem = 1408MB
shared_buffers = 6240MB

> временно добавлял
> ALTER SYSTEM SET shared_buffers = '10GB';

Это бесполезно без перезапуска сервера. И не влияет на планирование.
Вы проверили, может ли использоваться тот индекс  (и какие при этом оценки, если может)?

И, кстати, откуда такие странные значения параметров (как их подбирали и т.п.)?