Добро пожаловать в форум, Guest  >>   Войти | Регистрация | Поиск | Правила | В избранное | Подписаться
Все форумы / PostgreSQL Новый топик    Ответить
 Планировщик выбирает seq_scan  [new]
kap1lan
Member

Откуда:
Сообщений: 3
Добрый день!

Ситуация как из топика "Почему не используется индекс из двух полей при выборке только по первому полю?", но есть отличия.

Есть запрос:
+
select
  case when clFirst.src_abonent_type = 'SP' then clFirst.src_abonent else clFirst.src_id end as abonent, 
  que.enqueued_time as enqueued_time,
  que.unblocked_time as unblocked_time,
  inc2.title as project,
  case when que.unblocked_time is not NULL then INTERVALTOSEC(que.unblocked_time - que.enqueued_time) else INTERVALTOSEC(que.dequeued_time- que.enqueued_time) end as ivrtime,
  case when que.unblocked_time is not NULL then INTERVALTOSEC(que.dequeued_time - que.unblocked_time) else 0 end as waittime,  
  que.final_stage as final_stage,
  coalesce(clOp.dst_abonent,case when clNum.dst_abonent_type = 'SP' then clNum.dst_abonent else clNum.dst_id end) as nextId,
  coalesce(clOp.created,clNum.created) as nextCreated,
  coalesce(clOp.connected,clNum.connected) as nextConnected,
  coalesce(clOp.ended,clNum.ended) as nextEnded,
  coalesce(INTERVALTOSEC(clOp.connected-clOp.created),INTERVALTOSEC(clNum.connected-clNum.created)) as pickupTime,
  coalesce(INTERVALTOSEC(clOp.ended-clOp.connected),INTERVALTOSEC(clNum.ended-clNum.connected)) as speakingTime,
  inc.uuid as changecallproject,
  coalesce(ph.wrapuptime,0) as wrapuptime,
  ph.formuuid as phuuid,
  que.session_id as session_id_2,
  que.session_id as session_id
from 
  queued_calls que
  join call_legs clFirst on (clFirst.session_id = que.session_id and clFirst.leg_id = que.first_leg_id)
  left join call_legs clOp on (clOp.session_id = que.session_id and clOp.leg_id = que.next_leg_id and que.final_stage = 'operator')
  left join call_legs clNum on (clNum.session_id = que.session_id and clNum.leg_id = que.next_leg_id and que.final_stage = 'redirect')
  left join mv_employee emp on (emp.login = clOp.dst_abonent or emp.login = clNum.dst_abonent)
  left join mv_incoming_call_project inc on (inc.uuid = que.change_call_project and que.final_stage = 'changecallproject')
  left join mv_phone_call ph on (ph.projectuuid = que.project_id and ph.sessionid = que.session_id and ph.operatoruuid = emp.uuid)
  left join mv_incoming_call_project inc2 on (inc2.uuid = que.project_id)
where 
  que.enqueued_time >= '2020-05-01' and que.enqueued_time < '2020-06-01'
  and que.project_id in 
                                    
               ('0ma57t2aon9ul800', 
                '0mcko0m6k3utcq1s',
                '0ma57thknjaaq3kg',
               '0ma57tb6l1bnuml8', 
               '0mavrfon73dg88n0', 
               '0mavrclt6kn95bm4', 
               '0mad0fnq8nq408q4', 
               '0mbafmjq9kh2kkok', 
               '0mavr9eqlg47esbs', 
               '0mdbub6jhlb8n67g', 
               '0me206gphg8j910s', 
               '0meniml6jgn3cq28', 
               '0mfaqdsnb6k23sbk',
               '0mh7h659b38q3ub0',
               '0mb41s15b2l0h34g' 
               )


При выполнении запрос, планировщик считает, что seq_scan в данном случае выгоднее, чем сходить в индекс:

-> Hash (cost=11927086.76..11927086.76 rows=206463976 width=86)
-> Seq Scan on call_legs clop (cost=0.00..11927086.76 rows=206463976 width=86)
-> Hash (cost=11927086.76..11927086.76 rows=206463976 width=102)
-> Seq Scan on call_legs clnum (cost=0.00..11927086.76 rows=206463976 width=102)
-> Hash (cost=11927086.76..11927086.76 rows=206463976 width=77)
-> Seq Scan on call_legs clfirst (cost=0.00..11927086.76 rows=206463976 width=77)

Но, если отключить seq_scan, то он выбирает уже план с индексом:

                                 ->  Index Scan using idx_call_legs_session_id_2 on call_legs clnum  (cost=0.70..54.59 rows=1 width=102) (actual time=0.038..0.039 rows=1 loops=313832)
Index Cond: ((session_id)::text = (que.session_id)::text)
Filter: (leg_id = que.next_leg_id)
Rows Removed by Filter: 5
Buffers: shared hit=3141803 read=31
I/O Timings: read=103.063
-> Hash (cost=43.32..43.32 rows=203 width=33) (actual time=7.342..7.342 rows=203 loops=1)
Buckets: 1024 Batches: 1 Memory Usage: 21kB
Buffers: shared hit=3 read=1
I/O Timings: read=7.277

-> Index Scan using idx_call_legs_session_id_2 on call_legs clfirst (cost=0.70..54.59 rows=1 width=77) (actual time=0.039..0.039 rows=1 loops=313832)
Index Cond: ((session_id)::text = (que.session_id)::text)
Filter: (que.first_leg_id = leg_id)
Rows Removed by Filter: 5
Buffers: shared hit=3141834

Сбор статистики по call_legs.session_id уже увеличен до 10000.
pg_stats.n_distinct = -0.19461073
pg_class.reltuples = 206 427 152 (это к тому, что статистика обновляется)

Общее количество строк в таблице = 206 468 782
Количество строк за нужный период = 6 763 138
Количество уникальных строк за период = 1 989 633

PostgreSQL 10.5

Настройки PG:
cpu_index_tuple_cost = 0.005
cpu_operator_cost = 0.0025
cpu_tuple_cost = 0.01
parallel_setup_cost = 1000
parallel_tuple_cost = 0.1
random_page_cost = 10 (hdd)
seq_page_cost = 1
effective_cache_size = 30GB
default_statistics_target = 400


А теперь вопрос: Возможно как-то еще подсказать планировщику, что в данном случае план с индексами будет оптимальнее? Потому что, на текущий момент, кажется, что решить подобную проблему можно только введением дополнительных условий фильтрации.
2 авг 20, 14:49    [22176694]     Ответить | Цитировать Сообщить модератору
 Re: Планировщик выбирает seq_scan  [new]
Maxim Boguk
Member

Откуда: Melbourne, Австралия
Сообщений: 4226
kap1lan,

если у вас реально физически
random_page_cost = 10 (hdd)
seq_page_cost = 1

то прочитать все страницы последовательно из call_legs
может быть быстрее чем читать 313832 строк случайным чтением с использованием индекса..

если вам надо чтобы планировщик предпочитал index scan over seq scan
можно крутить 3 параметра
1)сильно повышать effective_cache_size но он нелинейно работает
2)уменьшать random_page_cost в пределе до == seq_page_cost
3)увеличивать cpu_tuple_cost

учитывая что у вас при index scan 99.99% обращений к таблице идут
Buffers: shared hit=3141803 read=31
через память а не с диска... то я делаю вывод что ваши настройки seq/random page cost не соответствуют тому что у вас в реальности с данными в базе наблюдается (с учетом кеширования в памяти базы и OS).

я бы просто random_page_cost бы уменьшал...

НО если вдруг окажется доступ к тем данным что на диске реально а не в кеше - random index scan может легко оказаться сильно медленнее.


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

Сообщение было отредактировано: 2 авг 20, 15:05
2 авг 20, 15:00    [22176701]     Ответить | Цитировать Сообщить модератору
 Re: Планировщик выбирает seq_scan  [new]
kap1lan
Member

Откуда:
Сообщений: 3
Maxim Boguk, спасибо за быстрый ответ.

Про hdd, не совсем так, как я написал. Есть условная храникал, в которой массив каких-то дисков, поэтому для упрощения написал, что там hdd. ssd точно нет.

Buffers: shared hit=3141803 read=31

Скорее всего, тут моя вина, не дождался, пока данные вытеснятся из файлового кеша. Перепроверю этот момент.

Параметры покручу, посмотрим, как это повлияет на оценки планировщиком.
2 авг 20, 16:00    [22176714]     Ответить | Цитировать Сообщить модератору
 Re: Планировщик выбирает seq_scan  [new]
Maxim Boguk
Member

Откуда: Melbourne, Австралия
Сообщений: 4226
kap1lan
Maxim Boguk, спасибо за быстрый ответ.

Про hdd, не совсем так, как я написал. Есть условная храникал, в которой массив каких-то дисков, поэтому для упрощения написал, что там hdd. ssd точно нет.

Buffers: shared hit=3141803 read=31

Скорее всего, тут моя вина, не дождался, пока данные вытеснятся из файлового кеша. Перепроверю этот момент.

Параметры покручу, посмотрим, как это повлияет на оценки планировщиком.



Buffers: shared hit=3141803 read=31
это только про кеш базы (shared buffers)
файловый кеш OS для базы не отличим от дисков физических

ps: а сколько у вас shared buffers ?

pps: выбор между index scan / seq scan на 90% определяется именно значениями random_page_cost / seq_page_cost

--
Maxim Boguk
лучшая поддержка PostgreSQL: dataegret.ru
2 авг 20, 16:07    [22176716]     Ответить | Цитировать Сообщить модератору
 Re: Планировщик выбирает seq_scan  [new]
kap1lan
Member

Откуда:
Сообщений: 3
Maxim Boguk, в shared_buffers сейчас 7Gb, общее на сервере 64Gb. Есть несколько еще java приложений на сервер, они суммарно забирают примерно 5Gb. Остальное использует (или мне так кажется) PG.

Да, планировщик начал ходить в индекс, после изменения random_page_cost = 1.
2 авг 20, 16:35    [22176724]     Ответить | Цитировать Сообщить модератору
Все форумы / PostgreSQL Ответить