Селект с фильтром,который выдаст только те записи котороые удовлетворяют условию / PostgreSQL

Мобильная версия Контакт Правила FAQ Помощь

Гость

Войти | Регистрация | Профиль | Очистить

Форумы | Пользователи | Статистика | Мод. лог | Поиск

Доб. в избранное | Игнор. тему | Прикреп. тему | Пометить прочит. / непрочит. | Фильтр

Форумы / PostgreSQL [игнор отключен] [закрыт для гостей] / Селект с фильтром,который выдаст только те записи котороые удовлетворяют условию

25 сообщений из 92, страница 2 из 4

все

Селект с фильтром,который выдаст только те записи котороые удовлетворяют условию

#40078941

Maxim Boguk

Участник

Откуда: Melbourne, Австралия

Сообщения: 5 148

Рейтинг: 0 / 0

booby,

Производительность в варианте с multiple exists перво на перво будет зависеть от селективности САМОГО селективного фильтра в наборе.
Т.е. если условно у вас в условии на channel_type 'SMS' ,'PUSH', 'доставка голубями'
и с 'SMS' ,'PUSH' по 10 миллионов строк а с 'доставка голубями' всего 10 строк
то вариант с exists выйграет у любого мыслимого альтернативного варианта
просто потому что он начнет с semijoin от
exists (select from person_channel pc where p.id = pc.person_id and pc.channel_type= 'доставка голубями' )
и далее быстро проверит найденные 10 строк по остальным 2м exits.

Все остальные варианты будут так или иначе скатываться с полным переборам таблицы person
а зачастую и таблицы person_channel с соответствующей печальной производителностью.

Если же у вас там 10 низкоселективных channel_type в условии - там надо думать как лучше и сравнивать на конкретных реальных или синтетических данных.

--
Maxim Boguk
лучшая поддержка PostgreSQL: dataegret.ru

...

Рейтинг:

0 / 0

20.06.2021, 10:31

| Ответить | Цитировать | Написать

Селект с фильтром,который выдаст только те записи котороые удовлетворяют условию

#40078947

booby

Участник

Сообщения: 2 667

Рейтинг: 1 / 0

Maxim Boguk,

Не думаю.

То, что вы описываете, видится как комбинация джойна с фильтром, при которой какие-то exists уедут в семиджойн, а какие-то останутся в фильтрах.
Допустим, что целевой построитель плана выполнения запроса достаточно умен, чтобы именно так и работать на релевантной статистике.
Тогда та часть, которая останется в фильтрах отработает за разумное время только при наличии структур типа bitmap-индексов.
(И, если они есть, то есть смысл вообще все оставить в фильтре)
Иначе кролик сдохнет под тяжестью множественных лукапов.

Принципиальный момент в такого рода задаче - по постановке всяко требуется полный просмотр person_channel,
то есть фильтрация не снижает совсем, или не снижает сильно объём просматриваемых данных, даже при пробеге по bitmap-индексу.
В этих обстоятельствах безфильтровый полный пробег по person_channel с группировкой и агрегацией вполне смотрится.
И тем лучше, чем больше сама person_channel и список обязательных требований.
Мне кажется вполне разумным ожидать, что рано или поздно он опередит вариант с exists.
А при отсутствии bitmap-индексов - опередит несомненно, и на достаточно малых значениях длинах списка требований (пусть будет - ~7-8 ).

Другой вопрос, что в варианте с группировкой кажется предпочтительным вместо ( Count(*) from таблица_требований )
иметь групповую функцию, дающую слепок группы требований, с которым непосредственно можно было бы
сравнивать слепок списка каналов конкретного person_id.
Для нумерованных списков длиной <= 64 можно было использовать bit_and, благо, он есть в postgress.
Преимущество Count в том, что он сработает стандартно на любых объёмах, без изобретения дополнительных конструкций
для поддержки списков требований большего размера.

PS
Думаю, что в случае использования технологий того рода, что в oracle называют "exadata" -
с фильтрацией на диске, блочными индексами и прочими чудесами, даже bitmap-индексам
не стоит начинать соревноваться с простым и незатейливым полным просмотром.
имхо.

...

Рейтинг:

0 / 0

20.06.2021, 11:33

| Ответить | Цитировать | Написать

Селект с фильтром,который выдаст только те записи котороые удовлетворяют условию

#40078950

asv79

Участник

Откуда: Тверь

Сообщения: 3 277

Рейтинг: 0 / 0

booby,
в паралельной теме мне предложили создать функциию ,которая будет создавать в памяти сессии временную таблицу с типами каналов

Код: plsql

1.
2.
3.
4.
5.
6.
7.
8.
9.
10.
11.
12.
13.
14.
15.

create function getRespondtenByTypes(VARIADIC mas text[]) returns setof respondent as
$$
declare str text;
  begin
  create temp table tmp_types(
  type text);
  foreach str in array mas
  loop
  insert into tmp_types (type) values (str);
end loop ;
return query select distinct  p.* from respondent p join respondent_channel rc on p.id = rc.respondent_id join tmp_types tt on rc.channel_type=tt.type
  
end;
  $$
language plpgsql;

Надо логику запроса поправить ,учитывая наличие временной таблицы,если конечно такой вариант вообще имеет место быть

...

Рейтинг:

0 / 0

20.06.2021, 11:42

| Ответить | Цитировать | Написать

Селект с фильтром,который выдаст только те записи котороые удовлетворяют условию

#40078951

booby

Участник

Сообщения: 2 667

Рейтинг: 1 / 0

с временной таблицей вы можете использовать любую из работоспособных ранее показанных логик в обоих ветка.

...

Рейтинг:

0 / 0

20.06.2021, 11:45

| Ответить | Цитировать | Написать

Селект с фильтром,который выдаст только те записи котороые удовлетворяют условию

#40078952

asv79

Участник

Откуда: Тверь

Сообщения: 3 277

Рейтинг: 0 / 0

booby

с временной таблицей вы можете использовать любую из работоспособных ранее показанных логик в обоих ветка.

а какой запрос в таком случае будет работать с временной таблицей- я честно не достаточно силен в такого рода запросах
если не сложно покажите рабочий пример .

...

Рейтинг:

0 / 0

20.06.2021, 11:51

| Ответить | Цитировать | Написать

Селект с фильтром,который выдаст только те записи котороые удовлетворяют условию

#40078955

Maxim Boguk

Участник

Откуда: Melbourne, Австралия

Сообщения: 5 148

Рейтинг: 0 / 0

booby,

"Принципиальный момент в такого рода задаче - по постановке всяко требуется полный просмотр person_channel," - с чего вы это взяли?
Задача решения с exists именно избежать полного просмотра.

--
Maxim Boguk
лучшая поддержка PostgreSQL: dataegret.ru

...

Рейтинг:

0 / 0

20.06.2021, 12:26

| Ответить | Цитировать | Написать

Селект с фильтром,который выдаст только те записи котороые удовлетворяют условию

#40078957

Maxim Boguk

Участник

Откуда: Melbourne, Австралия

Сообщения: 5 148

Рейтинг: 0 / 0

booby,

Для закрытия вопроса - вот полный test case про то как оно в реальности работает

Код: sql

1.
2.
3.
4.
5.
6.
7.
8.
9.
10.
11.
12.
13.
14.
15.
16.
17.
18.
19.
20.
21.
22.
23.
24.
25.
26.
27.
28.
29.
30.
31.
32.
33.
34.
35.
36.
37.
38.
39.

--создаем 1.000.000 persons
mboguk=# create database test;
CREATE DATABASE

mboguk=# \c test
You are now connected to database "test" as user "mboguk".

test=# create table person as select id from generate_series(1,1000000) as g(id);
SELECT 1000000

test=# alter table person add primary key (id);
ALTER TABLE

test=# vacuum freeze analyze person;
VACUUM


--создаем person_channel с типами SMS, PUSH, POSTAL DOVE c распределением вида 1:1:0.001
test=# create table person_channel as select person_id, case when random()>0.5 then 'SMS' when random()<0.001 then 'POSTAL DOVE' else 'PUSH' end as channel_type from generate_series(1,1000000) as g(person_id);
SELECT 1000000

test=# insert into person_channel select pc1.* FROM (select person_id, case when random()>0.5 then 'SMS' when random()<0.001 then 'POSTAL DOVE' else 'PUSH' end as channel_type from generate_series(1,1000000) as g(person_id)) as pc1 where not exists (select from person_channel pc2 where pc2.person_id=pc1.person_id and pc2.channel_type=pc1.channel_type);
INSERT 0 500390

test=# create UNIQUE index person_channel_person_id_channel_type on person_channel(person_id, channel_type);
CREATE INDEX

test=# create index person_channel_channel_type on person_channel(channel_type);
CREATE INDEX

test=# vacuum freeze ANALYZE person_channel;
VACUUM

test=# select count(*), channel_type from person_channel group by 2;
 count  | channel_type 
--------+--------------
   1034 | POSTAL DOVE
 749497 | PUSH
 749859 | SMS

А теперь тест:

Код: sql

1.
2.
3.
4.
5.
6.
7.
8.
9.
10.
11.
12.
13.
14.
15.
16.
17.
18.
19.
20.
21.
22.
23.
24.
25.
26.

test=# explain analyze select * from person p where
exists (select from person_channel pc where p.id = pc.person_id and pc.channel_type='SMS')
and exists (select from person_channel pc where p.id = pc.person_id and pc.channel_type='PUSH')
and exists (select from person_channel pc where p.id = pc.person_id and pc.channel_type='POSTAL DOVE')
;
                                                                                  QUERY PLAN                                                                                   
-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
 Nested Loop  (cost=13.85..7725.00 rows=590 width=4) (actual time=25.854..25.856 rows=0 loops=1)
   ->  Nested Loop  (cost=13.42..7358.65 rows=784 width=12) (actual time=0.829..22.298 rows=510 loops=1)
         ->  Nested Loop  (cost=12.99..6868.01 rows=1050 width=8) (actual time=0.811..12.571 rows=1034 loops=1)
               ->  Bitmap Heap Scan on person_channel pc_2  (cost=12.56..2875.39 rows=1050 width=4) (actual time=0.787..3.930 rows=1034 loops=1)
                     Recheck Cond: (channel_type = 'POSTAL DOVE'::text)
                     Heap Blocks: exact=953
                     ->  Bitmap Index Scan on person_channel_channel_type  (cost=0.00..12.30 rows=1050 width=0) (actual time=0.398..0.398 rows=1034 loops=1)
                           Index Cond: (channel_type = 'POSTAL DOVE'::text)
               ->  Index Only Scan using person_pkey on person p  (cost=0.42..3.80 rows=1 width=4) (actual time=0.007..0.007 rows=1 loops=1034)
                     Index Cond: (id = pc_2.person_id)
                     Heap Fetches: 0
         ->  Index Only Scan using person_channel_person_id_channel_type on person_channel pc_1  (cost=0.43..0.47 rows=1 width=4) (actual time=0.008..0.008 rows=0 loops=1034)
               Index Cond: ((person_id = p.id) AND (channel_type = 'PUSH'::text))
               Heap Fetches: 0
   ->  Index Only Scan using person_channel_person_id_channel_type on person_channel pc  (cost=0.43..0.47 rows=1 width=4) (actual time=0.006..0.006 rows=0 loops=510)
         Index Cond: ((person_id = p.id) AND (channel_type = 'SMS'::text))
         Heap Fetches: 0
 Planning Time: 3.106 ms
 Execution Time: 25.939 ms

И никаких полных переборов person_channel как и ожидалось.
"Допустим, что целевой построитель плана выполнения запроса достаточно умен, чтобы именно так и работать на релевантной статистике." - таки это базовая его функциональность - решать как такие вещи лучше делать.

Учите матчасть и проверяйте свои теории на тестовых данных перед тем как советовать.

PS: и даже в случае отсутствия селективного фильтра в наборе - продолжает работать неплохо и без
seq scan по person_channel

Код: sql

1.
2.
3.
4.
5.
6.
7.
8.
9.
10.
11.
12.
13.
14.
15.
16.
17.
18.
19.
20.
21.
22.
23.
24.
25.
26.
27.
28.
29.
30.
31.
32.
33.

explain analyze select * from person p where 
exists (select from person_channel pc where p.id = pc.person_id and pc.channel_type='SMS')
and exists (select from person_channel pc where p.id = pc.person_id and pc.channel_type='PUSH')
--and exists (select from person_channel pc where p.id = pc.person_id and pc.channel_type='POSTAL DOVE')
;
                                                                             QUERY PLAN                                                                              
---------------------------------------------------------------------------------------------------------------------------------------------------------------------
 Hash Join  (cost=74774.24..113292.60 rows=561997 width=4) (actual time=417.767..1089.731 rows=499356 loops=1)
   Hash Cond: (p.id = pc.person_id)
   ->  Hash Join  (cost=37273.93..65055.94 rows=746794 width=8) (actual time=188.063..608.533 rows=749497 loops=1)
         Hash Cond: (p.id = pc_1.person_id)
         ->  Seq Scan on person p  (cost=0.00..14425.00 rows=1000000 width=4) (actual time=0.019..61.047 rows=1000000 loops=1)
         ->  Hash  (cost=25021.01..25021.01 rows=746794 width=4) (actual time=187.852..187.853 rows=749497 loops=1)
               Buckets: 131072  Batches: 16  Memory Usage: 2676kB
               ->  Bitmap Heap Scan on person_channel pc_1  (cost=8324.08..25021.01 rows=746794 width=4) (actual time=17.158..98.042 rows=749497 loops=1)
                     Recheck Cond: (channel_type = 'PUSH'::text)
                     Heap Blocks: exact=7362
                     ->  Bitmap Index Scan on person_channel_channel_type  (cost=0.00..8137.38 rows=746794 width=0) (actual time=16.289..16.290 rows=749497 loops=1)
                           Index Cond: (channel_type = 'PUSH'::text)
   ->  Hash  (cost=25153.48..25153.48 rows=752546 width=4) (actual time=229.523..229.524 rows=749859 loops=1)
         Buckets: 131072  Batches: 16  Memory Usage: 2677kB
         ->  Bitmap Heap Scan on person_channel pc  (cost=8384.66..25153.48 rows=752546 width=4) (actual time=47.556..133.339 rows=749859 loops=1)
               Recheck Cond: (channel_type = 'SMS'::text)
               Heap Blocks: exact=7362
               ->  Bitmap Index Scan on person_channel_channel_type  (cost=0.00..8196.52 rows=752546 width=0) (actual time=36.843..36.844 rows=749859 loops=1)
                     Index Cond: (channel_type = 'SMS'::text)
 Planning Time: 1.277 ms
 JIT:
   Functions: 22
   Options: Inlining false, Optimization false, Expressions true, Deforming true
   Timing: Generation 4.455 ms, Inlining 0.000 ms, Optimization 0.370 ms, Emission 9.141 ms, Total 13.967 ms
 Execution Time: 1106.620 ms
(25 rows)

...

Рейтинг:

0 / 0

20.06.2021, 12:56

| Ответить | Цитировать | Написать

Селект с фильтром,который выдаст только те записи котороые удовлетворяют условию

#40078960

asv79

Участник

Откуда: Тверь

Сообщения: 3 277

Рейтинг: 0 / 0

Maxim Boguk,

Не подскажите а как будет выглядеть вариант запроса ,если я запишу типы каналов в временную таблицу ?заранее спасибо

...

Рейтинг:

0 / 0

20.06.2021, 13:08

| Ответить | Цитировать | Написать

Селект с фильтром,который выдаст только те записи котороые удовлетворяют условию

#40078961

Maxim Boguk

Участник

Откуда: Melbourne, Австралия

Сообщения: 5 148

Рейтинг: 0 / 0

asv79,

Я бы не стал... временные таблицы в PG очень дорогое удовольствие и на вашей задаче они выйгрыша не дадут вообще ни в каком варианте (тем более если запрос предполагается вызывать часто).
Советы про временные таблицы обычно идут от старых оракловодов где это могло помогать временами.

Сам факт что появляется слово distinct - уже убивает любую надежду на разумную производительность.

Тогда уж вместо временной таблицы конструктор values или unnest использовать (но выйгрыша это не даст).

PS: в 99% случаев подобных задач если в вашем решении появилось слово distinct - вы что то сильно не то делаете.

--
Maxim Boguk
лучшая поддержка PostgreSQL: dataegret.ru

...

Рейтинг:

0 / 0

20.06.2021, 13:13

| Ответить | Цитировать | Написать

Селект с фильтром,который выдаст только те записи котороые удовлетворяют условию

#40078963

asv79

Участник

Откуда: Тверь

Сообщения: 3 277

Рейтинг: 0 / 0

Maxim Boguk,

Спасибо .Применил ваш вариант первоначальный

...

Рейтинг:

0 / 0

20.06.2021, 13:24

| Ответить | Цитировать | Написать

Селект с фильтром,который выдаст только те записи котороые удовлетворяют условию

#40078964

booby

Участник

Сообщения: 2 667

Рейтинг: 1 / 0

Maxim Boguk,

Признаю, это не было умной идеей - свой пост мне оформлять в виде обращения к вам.
Хотя я мог бы догадаться об этом по стилю вашей подписи.

...

Рейтинг:

0 / 0

20.06.2021, 13:35

| Ответить | Цитировать | Написать

Селект с фильтром,который выдаст только те записи котороые удовлетворяют условию

#40078973

mayton

Участник

Откуда: loopback

Сообщения: 53 422

Рейтинг: 2 / 0

PetroNotC Sharp

asv79,
Пятый вариант триггер поставить и перечень SMS,EMAIL....etc' будет всегда автоматом в табле person
Смотря что по бизнесу надо. OLAP/OLTP

Хороший вариант. Но я-бы обсудил различные сценарии обратных (компенсирующих) DML
когда мы например удаляем "SMS" из дочерней таблицы. И возможную легкую денормализацию
родительской таблички.

...

Рейтинг:

0 / 0

20.06.2021, 16:01

| Ответить | Цитировать | Написать

Селект с фильтром,который выдаст только те записи котороые удовлетворяют условию

#40079002

PetroNotC Sharp

Участник

Сообщения: 10 098

Рейтинг: 0 / 0

mayton,
Конечно. Делал такое и была функция генерируйВьюху()
А она вызывалась по триггеру на события как вставки так и удаления.
То есть мы меняли схему бд.
Но можно менять и нормализацию.
Мы генерим идеи, а выбирает архитектор по многим факторам.

...

Рейтинг:

0 / 0

20.06.2021, 19:14

| Ответить | Цитировать | Написать

Селект с фильтром,который выдаст только те записи котороые удовлетворяют условию

#40079004

PetroNotC Sharp

Участник

Сообщения: 10 098

Рейтинг: 0 / 0

Maxim Boguk,
+1 про distinct
Не люблю его)

...

Рейтинг:

0 / 0

20.06.2021, 19:20

| Ответить | Цитировать | Написать

Селект с фильтром,который выдаст только те записи котороые удовлетворяют условию

#40079009

mayton

Участник

Откуда: loopback

Сообщения: 53 422

Рейтинг: 2 / 0

PetroNotC Sharp

Я вот щас еще раз сравнил реализацию materialized views в Оракле и в PG.
В последнем - тухляк. Не поддерживаются fast-режимы. Это когда мы обновляем
не весь mview а отслеживаем только изменённые data-rows по журналам.

Всему импортозамещению я сочувствую. PG - конешно опенсорсный. Но блин... это-же
сколько тонн человеко-часов надо потратить чтобы взять PG и впилить в него фичу
(реально классную фичу) из Оракла. Мне-бы до конца жизни хватило сидеть и разбираться
в исходниках постгреса. Увлекательное занятие было-бы на пенсии. Мдя.

...

Рейтинг:

0 / 0

20.06.2021, 19:29

| Ответить | Цитировать | Написать

Селект с фильтром,который выдаст только те записи котороые удовлетворяют условию

#40079028

booby

Участник

Сообщения: 2 667

Рейтинг: 1 / 0

PetroNotC Sharp

Maxim Boguk,
+1 про distinct
Не люблю его)

В тех специальных условиях, которые для себя сформулировал Максим, путём определения уникального индекса, никакого distinct технически не требуется.
В данном конкретном случае существо дела совершенно в другом.

2mayton
содержательный oracle-проект не только на db2, но даже на tibero db весьма непросто перенести.
А перенести в postgress, для проекта сложностью в три - пять сотен человеко-лет,
это примерно как человечеству переселиться, если не в соседнюю галактику, то, как минимум, на спутники Сатурна или Юпитера.
При том, что круг идей и совокупность алгоритмов у любых разумно построенных субд совпадает на 95-98%
Оставшиеся пара-тройка процентов образуют непреодолимую пропасть.

...

Рейтинг:

0 / 0

20.06.2021, 21:02

| Ответить | Цитировать | Написать

Селект с фильтром,который выдаст только те записи котороые удовлетворяют условию

#40079036

PetroNotC Sharp

Участник

Сообщения: 10 098

Рейтинг: 0 / 0

Господа.
Правильно я понял что сабж относится к области:
- вхождение множества А в множество Б.
Или термин выше относится к варианту с временной таблицей?
Например такой вариант составил

Код: sql

1.
2.
3.
4.
5.
6.
7.
8.
9.
10.
11.
12.
13.
14.
15.
16.
17.
18.
19.
20.
21.
22.
23.
24.
25.
26.
27.
28.
29.
30.
31.

with

   t2 as (select * from (values ('POST'), ('GET')) as p (typ))

 

select p.id,

CASE

 WHEN 

 ( exists

               (

                    (select typ from t2)

                     EXCEPT

                    (select pc.type from person_channel pc where pc.person_id = p.id)

                )

) THEN 'NO'

ELSE 'YES'

END

from person p

...

Рейтинг:

0 / 0

20.06.2021, 21:49

| Ответить | Цитировать | Написать

Селект с фильтром,который выдаст только те записи котороые удовлетворяют условию

#40079041

booby

Участник

Сообщения: 2 667

Рейтинг: 1 / 0

PetroNotC Sharp

Господа.
Правильно я понял что сабж относится к области:
- вхождение множества А в множество Б.

ммм. не то, чтобы совсем не так, но, в целом - нет.

Пусть Б- обязательное множество экзаменов, при сдаче которых студент из А-множетсва результатов сдачи экзаменов, переводится на следующий курс.
(Здесь предполагается, что "двойки" в A не пишутся - если двойка, у студента просто нет записи в результатах экзаменов А)
варианты задачи:

1) Требуется отобрать для перевода на следующий курс всех таких студентов, которые сдали не менее, чем множество обязательных экзаменов (требований). Если кроме обязательных сдали какие-то еще - то такие годятся, их тоже, несомненно переведут на следующий курс.
Это обычное реляционное деление А на Б, с допустимым "остатком"

2) Отыскиваются только те, кто сдал набор экзаменов, точно равный указанному с множестве Б - не меньше, но и не больше.
Это называют точным реляционным делением.
Например, это претенденты на обычную стипендию. В них нехорошо помещать тех, кто сдал экзаменов больше обязательного списка.
Таких надо в претенденты на повышенную стипендию определять.

То есть это задача на отбор персон, соответствующих квалификационным требованиям.
Если сказано в наборе обязательных требований к квалификации кандидата
автор Java 11, Kotlin, Spring Boot, WebFlux, R2DBC и прочее;
• legacy: Java 8, Groovy, Spring, Hibernate, ActiveMQ;
• Git, Gitlab, Gitlab-CI;
• Docker и Kubernetes;
• JIRA, Confluence, Agile Scrum;
• Quality gate: code coverage + vulnerabilities + maintainability.

И хотя бы одного слова в резюме не встречается (например - "прочее"), то кандидат в любом случае на собеседование не вызывается.
А если есть лишние слова - Fortran, Ada, C++, Julia, Oracle, Postgress, MySql,
то человек вызывается на собеседование, если "остаток от деления" допускается, и не вызывается, если выполняется точное деление, "без остатка".

...

Рейтинг:

0 / 0

20.06.2021, 22:19

| Ответить | Цитировать | Написать

Селект с фильтром,который выдаст только те записи котороые удовлетворяют условию

#40079050

Maxim Boguk

Участник

Откуда: Melbourne, Австралия

Сообщения: 5 148

Рейтинг: 0 / 0

booby,

Уникальный индекс в моём решении не требуется и никак на его эффект и скорость работы не влияет.

PS: в контексте задачи обычно если мы о реляционном делении говорим обычно студент не может иметь два одинаковых сданных экзамена поэтому uniq обычно предполагается.

Код: sql

1.
2.
3.
4.
5.
6.
7.
8.
9.
10.
11.
12.
13.
14.
15.
16.
17.
18.
19.
20.
21.
22.

test=# drop table person_channel;
DROP TABLE
test=# create table person_channel as select person_id, case when random()>0.5 then 'SMS' when random()<0.001 then 'POSTAL DOVE' else 'PUSH' end as channel_type from generate_series(1,1000000) as g(person_id);
SELECT 1000000
test=# insert into person_channel select pc1.* FROM (select person_id, case when random()>0.5 then 'SMS' when random()<0.001 then 'POSTAL DOVE' else 'PUSH' end as channel_type from generate_series(1,1000000) as g(person_id)) as pc1;
INSERT 0 1000000
test=# create index person_channel_person_id_channel_type on person_channel(person_id, channel_type);
CREATE INDEX

test=# create index person_channel_channel_type on person_channel(channel_type);
CREATE INDEX

test=# vacuum freeze ANALYZE person_channel;
VACUUM

--куча дубликатов
test=# select count(*), channel_type from person_channel group by 2;
 count  | channel_type 
--------+--------------
    953 | POSTAL DOVE
 999390 | PUSH
 999657 | SMS

Код: sql

1.
2.
3.
4.
5.

test=# explain analyze select * from person p where exists (select from person_channel pc where p.id = pc.person_id and pc.channel_type='SMS')
and
exists (select from person_channel pc where p.id = pc.person_id and pc.channel_type='PUSH')
and exists (select from person_channel pc where p.id = pc.person_id and pc.channel_type='POSTAL DOVE')
;

Код: plaintext

1.
2.
3.
4.
5.
6.
7.
8.
9.
10.
11.
12.
13.
14.
15.
16.
17.
18.
19.
20.
21.
22.

                                                                                   QUERY PLAN                                                                                   
--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
 Nested Loop Semi Join  (cost=2700.45..7174.53 rows=865 width=4) (actual time=22.187..22.190 rows=0 loops=1)
   ->  Nested Loop Semi Join  (cost=2700.03..6747.99 rows=886 width=12) (actual time=3.007..19.106 rows=477 loops=1)
         ->  Nested Loop  (cost=2699.60..6309.92 rows=910 width=8) (actual time=2.983..10.912 rows=953 loops=1)
               ->  HashAggregate  (cost=2699.17..2708.50 rows=933 width=4) (actual time=2.950..3.506 rows=953 loops=1)
                     Group Key: pc_2.person_id
                     Batches: 1  Memory Usage: 129kB
                     ->  Index Scan using person_channel_channel_type on person_channel pc_2  (cost=0.43..2696.84 rows=933 width=4) (actual time=0.039..2.053 rows=953 loops=1)
                           Index Cond: (channel_type = 'POSTAL DOVE'::text)
               ->  Index Only Scan using person_pkey on person p  (cost=0.42..3.86 rows=1 width=4) (actual time=0.007..0.007 rows=1 loops=953)
                     Index Cond: (id = pc_2.person_id)
                     Heap Fetches: 0
         ->  Index Only Scan using person_channel_person_id_channel_type on person_channel pc  (cost=0.43..0.47 rows=1 width=4) (actual time=0.008..0.008 rows=1 loops=953)
               Index Cond: ((person_id = p.id) AND (channel_type = 'SMS'::text))
               Heap Fetches: 0
   ->  Index Only Scan using person_channel_person_id_channel_type on person_channel pc_1  (cost=0.43..0.47 rows=1 width=4) (actual time=0.006..0.006 rows=0 loops=477)
         Index Cond: ((person_id = p.id) AND (channel_type = 'PUSH'::text))
         Heap Fetches: 0
 Planning Time: 1.697 ms
 Execution Time: 22.298 ms
(19 rows)

Альтернативное решение которое действительно может быть быстрее если много низкоселективных условий будет иметь вид:

SELECT * from person p where p.id IN (SELECT person_id FROM person_channel WHERE channel_type IN ('SMS', 'PUSH', 'POSTAL DOVE') GROUP BY person_id HAVING count(distinct channel_type)>=3);

которое будет иметь константное более менее время работы вне зависимости от селективности набора channel_type

Код: sql

1.
2.
3.
4.
5.
6.
7.
8.
9.
10.
11.
12.
13.
14.
15.
16.
17.
18.
19.
20.

test=# explain analyze SELECT  * from person p where p.id IN (SELECT person_id FROM person_channel WHERE channel_type IN ('SMS',  'PUSH',  'POSTAL DOVE') GROUP BY person_id HAVING count(distinct channel_type)>=3);
                                                                                       QUERY PLAN                                                                                        
-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
 Merge Join  (cost=0.85..120641.68 rows=341747 width=4) (actual time=1188.548..1188.550 rows=0 loops=1)
   Merge Cond: (p.id = person_channel.person_id)
   ->  Index Only Scan using person_pkey on person p  (cost=0.42..25980.42 rows=1000000 width=4) (actual time=0.105..0.106 rows=1 loops=1)
         Heap Fetches: 0
   ->  GroupAggregate  (cost=0.43..84471.95 rows=341747 width=4) (actual time=1177.114..1177.115 rows=0 loops=1)
         Group Key: person_channel.person_id
         Filter: (count(DISTINCT person_channel.channel_type) >= 3)
         Rows Removed by Filter: 1000000
         ->  Index Only Scan using person_channel_person_id_channel_type on person_channel  (cost=0.43..61656.43 rows=2000000 width=8) (actual time=0.088..304.581 rows=2000000 loops=1)
               Filter: (channel_type = ANY ('{SMS,PUSH,"POSTAL DOVE"}'::text[]))
               Heap Fetches: 0
 Planning Time: 0.474 ms
 JIT:
   Functions: 8
   Options: Inlining false, Optimization false, Expressions true, Deforming true
   Timing: Generation 3.186 ms, Inlining 0.000 ms, Optimization 0.577 ms, Emission 10.271 ms, Total 14.034 ms
 Execution Time: 1191.897 ms

Что проигрывает в 30 раз версии с exists НА СЕЛЕКТИВНОМ ФИЛЬТРЕ.

Если же фильтр не селективен то получаем сравнимое время работы:

Код: sql

1.
2.
3.
4.
5.
6.
7.
8.
9.
10.
11.
12.
13.
14.
15.
16.
17.
18.
19.
20.
21.
22.
23.
24.
25.
26.
27.
28.
29.
30.
31.
32.
33.
34.
35.
36.
37.
38.
39.
40.
41.
42.
43.
44.
45.
46.
47.
48.
49.

test=# explain analyze SELECT  * from person p where p.id IN (SELECT person_id FROM person_channel WHERE channel_type IN ('SMS',  'PUSH') GROUP BY person_id HAVING count(distinct channel_type)>=2);
                                                                                       QUERY PLAN                                                                                        
-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
 Merge Join  (cost=0.85..118137.02 rows=341747 width=4) (actual time=10.267..1458.642 rows=500030 loops=1)
   Merge Cond: (p.id = person_channel.person_id)
   ->  Index Only Scan using person_pkey on person p  (cost=0.42..25980.42 rows=1000000 width=4) (actual time=0.058..91.289 rows=1000000 loops=1)
         Heap Fetches: 0
   ->  GroupAggregate  (cost=0.43..81967.29 rows=341747 width=4) (actual time=0.123..1241.724 rows=500030 loops=1)
         Group Key: person_channel.person_id
         Filter: (count(DISTINCT person_channel.channel_type) >= 2)
         Rows Removed by Filter: 499970
         ->  Index Only Scan using person_channel_person_id_channel_type on person_channel  (cost=0.43..59156.43 rows=1999067 width=8) (actual time=0.071..313.379 rows=1999047 loops=1)
               Filter: (channel_type = ANY ('{SMS,PUSH}'::text[]))
               Rows Removed by Filter: 953
               Heap Fetches: 0
 Planning Time: 0.402 ms
 JIT:
   Functions: 8
   Options: Inlining false, Optimization false, Expressions true, Deforming true
   Timing: Generation 2.104 ms, Inlining 0.000 ms, Optimization 0.373 ms, Emission 9.337 ms, Total 11.813 ms
 Execution Time: 1474.000 ms

vs

test=# explain analyze select * from person p where exists (select from person_channel pc where p.id = pc.person_id and pc.channel_type='SMS')
and
exists (select from person_channel pc where p.id = pc.person_id and pc.channel_type='PUSH')
;                                                                                                     
                                                                                        QUERY PLAN                                                                                        
------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
 Merge Semi Join  (cost=4.88..153455.42 rows=950476 width=4) (actual time=7.121..640.741 rows=500030 loops=1)
   Merge Cond: (p.id = pc_1.person_id)
   ->  Merge Semi Join  (cost=1.71..89851.05 rows=973689 width=8) (actual time=7.061..386.027 rows=750082 loops=1)
         Merge Cond: (p.id = pc.person_id)
         ->  Index Only Scan using person_pkey on person p  (cost=0.42..25980.42 rows=1000000 width=4) (actual time=0.055..85.834 rows=1000000 loops=1)
               Heap Fetches: 0
         ->  Index Only Scan using person_channel_person_id_channel_type on person_channel pc  (cost=0.43..49139.10 rows=998267 width=4) (actual time=0.098..116.771 rows=999657 loops=1)
               Index Cond: (channel_type = 'SMS'::text)
               Heap Fetches: 0
   ->  Index Only Scan using person_channel_person_id_channel_type on person_channel pc_1  (cost=0.43..49164.43 rows=1000800 width=4) (actual time=0.045..114.944 rows=999390 loops=1)
         Index Cond: (channel_type = 'PUSH'::text)
         Heap Fetches: 0
 Planning Time: 0.974 ms
 JIT:
   Functions: 8
   Options: Inlining false, Optimization false, Expressions true, Deforming true
   Timing: Generation 1.916 ms, Inlining 0.000 ms, Optimization 0.340 ms, Emission 6.218 ms, Total 8.474 ms
 Execution Time: 655.357 ms
(18 rows)

В итоге еще раз подтверждается правило что надо тестировать тестировать и еще раз тестировать разные варианты как минимум на синтетике а в идеале на копии реальных данных и главное реальных запросов и фильтров (понимая распределения этих фильтов в production).

--
Maxim Boguk
лучшая поддержка PostgreSQL: dataegret.ru

...

Рейтинг:

0 / 0

21.06.2021, 00:05

| Ответить | Цитировать | Написать

Селект с фильтром,который выдаст только те записи котороые удовлетворяют условию

#40079051

Maxim Boguk

Участник

Откуда: Melbourne, Австралия

Сообщения: 5 148

Рейтинг: 0 / 0

mayton

PetroNotC Sharp

Практика показала что если мы переносим сколь угодно большой ВЕБ проект (а я переносил как минимум один из топовых сайтов рунета с mysql на postgres в одни руки) - это не так уж сложно и вполне поддается решению за разумное время.

Совсем другое дело когда надо перенести какой то биллинг или АБС с миллионами строк хранимок и (mat)views - там сложность переноса в том что там нарушена изоляция данных от кода и фактически мы не базу с данными и формализованным языком SQL переносим а переносим весь проект с языка А (хранимки ORACLE) на язык Б (хранимки postgresql) - и тут обьем работ конечно очень большой
(хотя учитывая цену лицензии на оракел часто оправданный... не всегда конечно).
Это как с С++ на Java переписать... вроде и похоже но автоматических трансляторов рабочих не существует.

--
Maxim Boguk
лучшая поддержка PostgreSQL: dataegret.ru

...

Рейтинг:

0 / 0

21.06.2021, 00:12

| Ответить | Цитировать | Написать

Селект с фильтром,который выдаст только те записи котороые удовлетворяют условию

#40079052

mayton

Участник

Откуда: loopback

Сообщения: 53 422

Рейтинг: 2 / 0

Интересная эта штука.... partial indexes. Вот попробовал. Но нужны реальные вероятности и взаимовязи
предикатов SMS/Push/Email. На синтетике - не те ощущения. Вобщем пускай Стасик проверяет если хочет.
И триггер надо добавить.

Код: plsql

1.
2.
3.
4.
5.
6.
7.
8.
9.
10.
11.
12.
13.
14.
15.
16.
17.
18.
19.
20.
21.
22.
23.
24.
25.
26.
27.
28.
29.
30.
31.
32.
33.
34.
35.
36.
37.
38.
39.
40.
41.
42.
43.
44.
45.
46.
47.
48.
49.
50.
51.
52.
53.
54.
55.
56.
57.
58.
59.
60.
61.
62.
63.
64.
65.
66.
67.

-- Denormalized respndents

drop table respondent;

create table respondent(
   person_id integer primary key,   
   channel_type_sms boolean,
   channel_type_push boolean,
   channel_type_email boolean
   -- all other respondent stuff...
);

-- TODO: Implement trigger for each row to update predicates
-- .......

-- Generate random noize with 0.25 probability of predicates


DO 
$code$
BEGIN
    FOR i IN 1..1000000 LOOP
      insert into respondent values(i, random() < 0.25, random() < 0.25, random() < 0.25);
    END LOOP;
    raise notice 'OK';
END
$code$;

--

analyze verbose respondent;
INFO:  analyzing "public.respondent"
INFO:  "respondent": scanned 4425 of 4425 pages, containing 1000000 live rows and 0 dead rows; 30000 rows in sample, 1000000 estimated total rows
ANALYZE


explain analyze select * from respondent where channel_type_sms and channel_type_push and channel_type_email;
-----------------------------------------------------------------------------------------------------------------------------
 Gather  (cost=1000.00..11175.17 rows=15835 width=7) (actual time=0.410..36.277 rows=15794 loops=1)
   Workers Planned: 2
   Workers Launched: 2
   ->  Parallel Seq Scan on respondent  (cost=0.00..8591.67 rows=6598 width=7) (actual time=0.034..27.342 rows=5265 loops=3)
         Filter: (channel_type_sms AND channel_type_push AND channel_type_email)
         Rows Removed by Filter: 328069
 Planning Time: 0.225 ms
 Execution Time: 36.849 ms
(8 rows)


-- Partial index. TODO: Investigate for probability range (0.25 - 0.95) benefit.


CREATE INDEX respondent_function ON respondent(person_id) WHERE NOT (channel_type_sms AND channel_type_push AND channel_type_email);

dht=> explain analyze select * from respondent where channel_type_sms and channel_type_push and channel_type_email;

explain analyze select * from respondent where channel_type_sms and channel_type_push and channel_type_email;
-----------------------------------------------------------------------------------------------------------------------------
 Gather  (cost=1000.00..11164.17 rows=15725 width=7) (actual time=0.286..36.859 rows=15794 loops=1)
   Workers Planned: 2
   Workers Launched: 2
   ->  Parallel Seq Scan on respondent  (cost=0.00..8591.67 rows=6552 width=7) (actual time=0.023..28.560 rows=5265 loops=3)
         Filter: (channel_type_sms AND channel_type_push AND channel_type_email)
         Rows Removed by Filter: 328069
 Planning Time: 0.311 ms
 Execution Time: 37.510 ms
(8 rows)

...

Рейтинг:

0 / 0

21.06.2021, 00:20

| Ответить | Цитировать | Написать

Селект с фильтром,который выдаст только те записи котороые удовлетворяют условию

#40079054

booby

Участник

Сообщения: 2 667

Рейтинг: 1 / 0

Maxim Boguk

booby,

Уникальный индекс в моём решении не требуется и никак на его эффект и скорость работы не влияет.
...

вот что вы говорили:
Maxim BogukСам факт что появляется слово distinct - уже убивает любую надежду на разумную производительность.
Так вот, неизбежно distinct появляется только в варианте запроса с группировкой с последующим фильтром
вида

Код: sql

Having (Select count(*) From B) = Count(distinct A.subject)

когда пара (A.person_id, A.subject) неуникальна.
И, если такая пара ограничена уникальностью, то это единственное обязательное вхождение distinct автоматически исчезает.

...

Рейтинг:

0 / 0

21.06.2021, 00:32

| Ответить | Цитировать | Написать

Селект с фильтром,который выдаст только те записи котороые удовлетворяют условию

#40079057

booby

Участник

Сообщения: 2 667

Рейтинг: 1 / 0

хм.
на миллионе записей:

Код: sql

1.
2.
3.
4.
5.

               ->  Bitmap Heap Scan on person_channel pc_2  (cost=12.56..2875.39 rows=1050 width=4) (actual time=0.787..3.930 rows=1034 loops=1)
                     Recheck Cond: (channel_type = 'POSTAL DOVE'::text)
                     Heap Blocks: exact=953
                     ->  Bitmap Index Scan on person_channel_channel_type  (cost=0.00..12.30 rows=1050 width=0) (actual time=0.398..0.398 rows=1034 loops=1)
                           Index Cond: (channel_type = 'POSTAL DOVE'::text)

а на двух миллионах:

Код: sql

1.
2.
3.
4.
5.

               ->  HashAggregate  (cost=2699.17..2708.50 rows=933 width=4) (actual time=2.950..3.506 rows=953 loops=1)
                     Group Key: pc_2.person_id
                     Batches: 1  Memory Usage: 129kB
                     ->  Index Scan using person_channel_channel_type on person_channel pc_2  (cost=0.43..2696.84 rows=933 width=4) (actual time=0.039..2.053 rows=953 loops=1)
                           Index Cond: (channel_type = 'POSTAL DOVE'::text)

Как оперативка у бобика сдохла для построения битмап-карты, так сразу и Group By заиграл, негромкой дудочкой.

...

Рейтинг:

0 / 0

21.06.2021, 01:00

| Ответить | Цитировать | Написать

Селект с фильтром,который выдаст только те записи котороые удовлетворяют условию

#40079062

PetroNotC Sharp

Участник

Сообщения: 10 098

Рейтинг: 0 / 0

booby,
Не, ну у нас то задача определена и вариант только один.
Множество детей у мастера А должно обязательно содержать множество Б называемых автором фильтры.
Поэтому и спросил как это одной фразой назвать.
Так как оператор in проверяет вхождение одного элемента.
А оператор EXCEPT то что надо но вроде только от постгри.
Решение выше вообще вроде работает.
Возможно это называется как вы написали
Это обычное реляционное деление А на Б, с допустимым "остатком".
ОК.

...

Рейтинг:

0 / 0

21.06.2021, 07:11

| Ответить | Цитировать | Написать

Селект с фильтром,который выдаст только те записи котороые удовлетворяют условию

#40079078

Maxim Boguk

Участник

Откуда: Melbourne, Австралия

Сообщения: 5 148

Рейтинг: 0 / 0

booby,

Разница в том что в первом плане есть uniq индекс а во втором нет.
Поэтому в первом плане не требуется стадия убирания дубликатов строк.
Да и по скорости они не отличаются почти.

32MB work_mem достаточно на очень дофига bitmap scan строк.

--
Maxim Boguk
лучшая поддержка PostgreSQL: dataegret.ru

...

Рейтинг:

0 / 0

21.06.2021, 10:07

| Ответить | Цитировать | Написать

25 сообщений из 92, страница 2 из 4

все

Цитировать

Написать

Автор*:

Ввести пароль для входа

Тема*:

Сообщение

Данное сообщение тематическое

Сообщение содержит картинки или видео 18+

Автор:

ВНИМАНИЕ! На данном подфоруме действуют строгие правила. Удостоверьтесь, что ваше сообщение соответствует им!

Форум или тема закрыты для гостей. Необходима авторизация!

Загрузить последнюю сохраненную версию

Вложение:

Вставить как галерею

Максимальный размер вложений: 4,0 МБ, аудио/видео: 8,0 МБ. Картинки большего размера ужимаются, если возможно.

Введите код, изображенный на картинке. Если код нечитаемый, кликните картинку, чтобы загрузить другой вариант.

Отправляя сообщение, я выражаю свое согласие с правилами форума и принимаю пользовательское соглашение.

Читали тему (0):

Читали форум (0):

Пользователи онлайн (0):

start [/forum/topic.php?fid=53&msg=40079041&tid=1993940]:	0ms
get settings:	11ms
get forum list:	15ms
check forum access:	4ms
check topic access:	4ms
track hit:	53ms
get topic data:	11ms
get forum data:	3ms
get page messages:	70ms
get tp. blocked users:	1ms
others:	260ms

total:	432ms