Почему не используется индекс из двух полей при выборке только по первому полю? / PostgreSQL

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

Гость

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

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

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

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

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

все

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

#39893091

Alexey Trizno

Гость

Коллеги, ткните пожалуйста носом в нужном направлении (речь про postgresql 9.6).
Есть простой запрос вида:

Код: plsql

1.
2.
3.
4.
5.

select iqd.* 
from interview_question_details iqd 
    inner join interviews i on i.id = iqd.interview_id
    inner join respondents r on r.id = i.respondent_id 
where r.project_id = 6

Который нам даёт такой страшный план (в табличке более 3.5 млрд. строк и время выполнения запроса соответственно - больше 20 минут, на ssd дисках и random_page_cost = 1.1):

Код: plaintext

1.
2.
3.
4.
5.
6.
7.
8.
9.

Hash Join  (cost=86543.64..89268074.85 rows=885580 width=220)
  Hash Cond: (iqd.interview_id = i.id)
   ->  Seq Scan on interview_question_details iqd  (cost=0.00..75858303.10 rows=3550499310 width=220) 
  ->  Hash  (cost=86337.65..86337.65 rows=16479 width=8)
        ->  Nested Loop  (cost=1.13..86337.65 rows=16479 width=8)
              ->  Index Only Scan using ix_respondents_projectid_id on respondents r  (cost=0.57..18779.68 rows=26597 width=8)
                    Index Cond: (project_id = 6)
              ->  Index Only Scan using ix_interviews_respondentid_id on interviews i  (cost=0.56..2.52 rows=2 width=16)
                    Index Cond: (respondent_id = r.id)

При этом в табличке interview_question_details есть такой вот подходящий индекс:

Код: plsql

1.
2.
3.
4.
5.
6.
7.
8.
9.
10.

CREATE TABLE public.interview_question_details (
	id bigserial NOT NULL,
	interview_id int8 NOT NULL,
	question_number int8 NOT NULL,
...
...
	CONSTRAINT pk_interviewquestiondetails PRIMARY KEY (id),
	CONSTRAINT fk_interviewquestiondetails_interviewid FOREIGN KEY (interview_id) REFERENCES interviews(id)
);
CREATE UNIQUE INDEX ix_interviewquestiondetails_interviewid_questionnumber ON public.interview_question_details USING btree (interview_id, question_number);

При этом, если в табличку добавить индекс только по interview_id - то планировщик сразу его выбирает и исходный запрос выполняется за несколько миллисекунд, которые и ожидались (индекс назвали fki_interviewquestiondetails_interviewid):

Код: plaintext

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

Gather  (cost=1143.54..22375226.99 rows=802068 width=8)
  Workers Planned: 2
  ->  Nested Loop  (cost=143.54..22294020.19 rows=334195 width=8)
        ->  Nested Loop  (cost=142.96..26866.39 rows=6208 width=8)
              ->  Parallel Bitmap Heap Scan on respondents r  (cost=142.39..12763.80 rows=4905 width=8)
                    Recheck Cond: (project_id = 6)
                    ->  Bitmap Index Scan on ix_respondents_projectid_id  (cost=0.00..139.45 rows=11771 width=0)
                          Index Cond: (project_id = 6)
              ->  Index Scan using fki_interviews_respondentid on interviews i  (cost=0.56..2.86 rows=2 width=16)
                    Index Cond: (respondent_id = r.id)
         ->  Index Scan using fki_interviewquestiondetails_interviewid on interview_question_details iqd  (cost=0.58..2886.44 rows=70041 width=16) 
              Index Cond: (interview_id = i.id)

Вопрос: почему планировщик не хочет использовать индекс по interview_id + question_number, но прекрасно берёт индекс по одному полю interview_id?

При этом, если напишем запрос аля:

Код: plsql

select * from interview_question_details where interview_id = 2374287

то получим нормальный ожидаемый план запроса:

Код: plaintext

1.
2.

Index Scan using ix_interviewquestiondetails_interviewid_questionnumber on interview_question_details  (cost=0.70..42515.30 rows=82377 width=220)
  Index Cond: (interview_id = 2374287)

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

...

Рейтинг:

0 / 0

22.11.2019, 17:32

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

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

#39893099

Павел Лузанов

Участник

Сообщения: 866

Рейтинг: 0 / 0

Alexey Trizno,

Вероятно план с доступом по индексу планировщик считает более дорогим.
А покажите план, предварительно выключив: set enable_seqscan = off;

...

Рейтинг:

0 / 0

22.11.2019, 17:52

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

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

#39893116

Melkij

Участник

Откуда: Санкт-Петербург

Сообщения: 1 447

Рейтинг: 0 / 0

и сделайте explain (analyze)

Alexey Trizno

речь про postgresql 9.6

Alexey Trizno

-> Parallel Bitmap Heap Scan on respondents r (cost=142.39..12763.80 rows=4905 width=8)

А если не обманывать по поводу версии базы? В 9.6 в принципе parallel bitmap не было. Только parallel seqscan появился.

...

Рейтинг:

0 / 0

22.11.2019, 18:32

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

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

#39893122

Alexey Trizno

Гость

Melkij,

Каюсь про 9.6 - первый запрос из неё (это продовая база), а второй из копии этой базы на dev-сервере, там postgresql уже поновее. Но суть от этого не меняется, к сожалению :(

Вот план первого запроса на той же базе и версии, что план с индексом по interview_id:

Код: plaintext

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

Gather  (cost=27943.99..55487859.91 rows=802068 width=585)
  Workers Planned: 2
  ->  Parallel Hash Join  (cost=26943.99..55406653.11 rows=334195 width=585)
        Hash Cond: (iqd.interview_id = i.id)
        ->  Parallel Seq Scan on interview_question_details iqd  (cost=0.00..50316569.53 rows=1349799253 width=585)
        ->  Parallel Hash  (cost=26866.39..26866.39 rows=6208 width=8)
              ->  Nested Loop  (cost=142.96..26866.39 rows=6208 width=8)
                    ->  Parallel Bitmap Heap Scan on respondents r  (cost=142.39..12763.80 rows=4905 width=8)
                          Recheck Cond: (project_id = 6)
                          ->  Bitmap Index Scan on ix_respondents_projectid_id  (cost=0.00..139.45 rows=11771 width=0)
                                Index Cond: (project_id = 6)
                    ->  Index Scan using fki_interviews_respondentid on interviews i  (cost=0.56..2.86 rows=2 width=16)
                          Index Cond: (respondent_id = r.id)

На прод-базе (где 9.6) дождаться выполнения долго запроса с analyze - не получается вообще.
Могу попробовать запустить ночью, если поможет.

...

Рейтинг:

0 / 0

22.11.2019, 18:51

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

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

#39893127

Alexey Trizno

Гость

Павел Лузанов,

отключение seqscan - да, помогает решить проблему:

Код: plaintext

1.
2.
3.
4.
5.
6.
7.
8.

Nested Loop  (cost=1.84..689582801.65 rows=885805 width=220)
  ->  Nested Loop  (cost=1.13..86343.64 rows=16483 width=8)
        ->  Index Only Scan using ix_respondents_projectid_id on respondents r  (cost=0.57..18779.68 rows=26597 width=8)
              Index Cond: (project_id = 6)
        ->  Index Only Scan using ix_interviews_respondentid_id on interviews i  (cost=0.56..2.52 rows=2 width=16)
              Index Cond: (respondent_id = r.id)
  ->  Index Scan using ix_interviewquestiondetails_interviewid_questionnumber on interview_question_details iqd  (cost=0.70..40995.67 rows=83509 width=220)
        Index Cond: (interview_id = i.id)

Это единственное решение в такой ситуации?

...

Рейтинг:

0 / 0

22.11.2019, 18:55

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

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

#39893141

Melkij

Участник

Откуда: Санкт-Петербург

Сообщения: 1 447

Рейтинг: 0 / 0

Alexey Trizno,

показывайте explain analyze ещё раз прошу. Не только для исходного, но и для быстрого плана.
Понятно что план со стоимостью на порядок выше планировщику не нравится. Если по факту он быстрее - значит в какой-то момент планировщик ошибается, скорей всего по числу уникальных строк для interview_id. Stats target какой?

...

Рейтинг:

0 / 0

22.11.2019, 19:45

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

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

#39893389

Alexey Trizno

Гость

Melkij,

было не просто, но удалось выполнить analyze на продуктовой базе для основной ситуации, когда нет отдельного индекса по interview_id:

Код: plaintext

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

 Hash Join  (cost=86559.56..89306662.56 rows=885963 width=220) (actual time=2560277.828..2560277.828 rows=0 loops=1)
   Hash Cond: (iqd.interview_id = i.id)
   ->  Seq Scan on interview_question_details iqd  (cost=0.00..75891112.18 rows=3552034918 width=220) (actual time=0.050..1407987.890 rows=3551872911 loops=1)
   ->  Hash  (cost=86353.42..86353.42 rows=16491 width=8) (actual time=0.295..0.295 rows=9 loops=1)
         Buckets: 32768  Batches: 1  Memory Usage: 257kB
         ->  Nested Loop  (cost=1.13..86353.42 rows=16491 width=8) (actual time=0.216..0.286 rows=9 loops=1)
               ->  Index Only Scan using ix_respondents_projectid_id on respondents r  (cost=0.57..18779.68 rows=26597 width=8) (actual time=0.159..0.167 rows=9 loops=1)
                     Index Cond: (project_id = 6)
                     Heap Fetches: 9
               ->  Index Only Scan using ix_interviews_respondentid_id on interviews i  (cost=0.56..2.52 rows=2 width=16) (actual time=0.010..0.011 rows=1 loops=9)
                     Index Cond: (respondent_id = r.id)
                     Heap Fetches: 0
 Planning time: 10.601 ms
 Execution time: 2560278.140 ms

default_statistics_target не менялся, т.е. сейчас 100 by default.

...

Рейтинг:

0 / 0

23.11.2019, 17:49

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

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

#39893403

vyegorov

Участник

Откуда: Баньоло-ин-Пьяно

Сообщения: 1 237

Рейтинг: 0 / 0

Alexey Trizno,

в продукции не хватает индекса `ON interview_question_details(interview_id)`

...

Рейтинг:

0 / 0

23.11.2019, 18:47

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

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

#39893404

Alexey Trizno

Гость

vyegorov,

:) это да, он его хочет, но вопрос топика в другом - чем не нравится имеющийся индекс:

Код: plsql

CREATE UNIQUE INDEX ix_interviewquestiondetails_interviewid_questionnumber ON public.interview_question_details USING btree (interview_id, question_number);

Он есть и прекрасно, вроде бы, подходит, что подтверждается выполнением того же самого запроса при принудительно отключенном seqscan.

Хочется понять проблему, т.к. у нас в базе, с целью экономии объема (только в этой одной таблице 3.5 млрд. записей и кол-во постоянно увеличивается), много подобных мест, когда сделаны индексы по нескольким полям, вместо пачки индексов с нарастающим кол-вом полей под все варианты выборок. Ведь чтобы не делать индексы по A+B+C, A+B, A - достаточно одного по A+B+C, при условии выборок по A, либо по A+B, либо по A+B+C. Согласно документации. Но... что-то сбивает планировщик запросов и он хочет отдельный индекс по A при выборке только по A.

...

Рейтинг:

0 / 0

23.11.2019, 18:58

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

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

#39893418

Maxim Boguk

Участник

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

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

Рейтинг: 0 / 0

Alexey Trizno,

Основная ошибка я думаю происходит изза ошибки на 3 порядка в этой части:

-> Index Only Scan using ix_respondents_projectid_id on respondents r (cost=0.57..18779.68 rows=26597 width=8) (actual time=0.159..0.167 rows=9 loops=1)
Index Cond: (project_id = 6)

После этого уже на хороший план нет смысла рассчитывать.

Что говорит (вот все 3 выполнить как я написал):

Код: plsql

1.
2.
3.

explain analyze select distinct project_id from respondents; 
analyze respondents;
explain analyze select distinct project_id from respondents;

Я подозреваю что начинать придется с проставления повышенного statistic_target (1000 - 10000) для respondents.project_id
(и вероятнее всего с проставления n_distinct для него же).

Возможно тоже самое надо будет сделать для interview_question_details.interview_id (но это будет понятно после того как мы оценку селективности для respondents поправим).

...

Рейтинг:

0 / 0

23.11.2019, 19:46

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

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

#39893419

Melkij

Участник

Откуда: Санкт-Петербург

Сообщения: 1 447

Рейтинг: 0 / 0

Alexey Trizno

второго explain analyze так и не увижу?

Alexey Trizno

-> Index Only Scan using ix_respondents_projectid_id on respondents r (cost=0.57..18779.68 rows=26597 width=8) (actual time=0.159..0.167 rows=9 loops=1)

неплохо так расходится. Maxim Boguk опередил с ответом уже

...

Рейтинг:

0 / 0

23.11.2019, 19:49

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

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

#39893421

Alexey Trizno

Гость

Maxim Boguk,

Ок, по порядку:

Код: plsql

explain analyze select distinct project_id from respondents;

Код: plaintext

1.
2.
3.
4.
5.

HashAggregate  (cost=2293820.20..2293848.46 rows=2826 width=8) (actual time=71403.735..71407.911 rows=8013 loops=1)
  Group Key: project_id
  ->  Seq Scan on respondents  (cost=0.00..2027235.96 rows=106633696 width=8) (actual time=95.171..43242.950 rows=52296470 loops=1)
Planning time: 0.700 ms
Execution time: 71408.851 ms

Код: plsql

analyze respondents;

Код: plsql

explain analyze select distinct project_id from respondents;

Код: plaintext

1.
2.
3.
4.
5.

HashAggregate  (cost=1616207.70..1616237.02 rows=2932 width=8) (actual time=82439.615..82443.463 rows=8013 loops=1)
  Group Key: project_id
  ->  Seq Scan on respondents  (cost=0.00..1485145.96 rows=52424696 width=8) (actual time=98.997..54378.182 rows=52296792 loops=1)
Planning time: 0.678 ms
Execution time: 82444.365 ms

Вот как-то так.

...

Рейтинг:

0 / 0

23.11.2019, 19:54

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

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

#39893424

Alexey Trizno

Гость

Melkij

второго explain analyze так и не увижу?

Я стараюсь, всё будет, но построить индекс новый по interview_id на продуктовой базе (чтобы уж всё было честно) - пока не получается. Поставлю сегодня после 23:00 его создание, там долго. В рабочее время начинает сильно проседать производительность.

...

Рейтинг:

0 / 0

23.11.2019, 19:56

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

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

#39893425

Maxim Boguk

Участник

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

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

Рейтинг: 0 / 0

Alexey Trizno

Maxim Boguk,

Ок, по порядку:

Код: plaintext

1.
2.
3.
4.
5.

HashAggregate  (cost=2293820.20..2293848.46 rows=2826 width=8) (actual time=71403.735..71407.911 rows=8013 loops=1)
  Group Key: project_id
  ->  Seq Scan on respondents  (cost=0.00..2027235.96 rows=106633696 width=8) (actual time=95.171..43242.950 rows=52296470 loops=1)
Planning time: 0.700 ms
Execution time: 71408.851 ms

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

попробуйте сделать (с неким запасом все задал):
alter table respondents alter project_id set STATISTICS 10000;
alter table respondents alter project_id set (n_distinct = 10000);
analyze respondents;
explain analyze select * from respondents where project_id = 6;

и покажите план проблемного запроса после этого (без analyze), просто explain.

...

Рейтинг:

0 / 0

23.11.2019, 20:01

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

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

#39893443

Alexey Trizno

Гость

Maxim Boguk,

Выполнил.

Код: plsql

explain analyze select * from respondents where project_id = 6;

Код: plaintext

1.
2.
3.
4.

Index Scan using ix_respondents_projectid_id on respondents  (cost=0.56..352.74 rows=425 width=40) (actual time=0.159..0.167 rows=9 loops=1)
  Index Cond: (project_id = 6)
Planning time: 1.058 ms
Execution time: 0.204 ms

Код: plsql

1.
2.
3.
4.
5.
6.

explain
select iqd.* 
from interview_question_details iqd 
    inner join interviews i on i.id = iqd.interview_id
    inner join respondents r on r.id = i.respondent_id 
where r.project_id = 6

Код: plaintext

1.
2.
3.
4.
5.
6.
7.
8.

Nested Loop  (cost=1.83..22537285.29 rows=28894 width=220)
  ->  Nested Loop  (cost=1.13..1289.17 rows=538 width=8)
        ->  Index Only Scan using ix_respondents_projectid_id on respondents r  (cost=0.56..186.44 rows=425 width=8)
              Index Cond: (project_id = 6)
        ->  Index Only Scan using ix_interviews_respondentid_id on interviews i  (cost=0.56..2.57 rows=2 width=16)
              Index Cond: (respondent_id = r.id)
  ->  Index Scan using ix_interviewquestiondetails_interviewid_questionnumber on interview_question_details iqd  (cost=0.70..41052.29 rows=83618 width=220)
        Index Cond: (interview_id = i.id)

Индекс теперь используется. Можно пояснить что ему было не так и как жить дальше? :)
У нас скоро будет сильное изменение схемы базы для возможности выделения данных проектов в отдельные базы и на отдельные сервера. Хотелось бы понять - что делаем не так?

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

...

Рейтинг:

0 / 0

23.11.2019, 21:14

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

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

#39893498

Maxim Boguk

Участник

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

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

Рейтинг: 0 / 0

Alexey Trizno,

Я бы еще порекомендовал бы сделать следующие запросы чтобы понимать ситуцию

explain analyze select distinct interview_id from interview_question_details

посмотрим насколько база ошибается в оценке количества уникальных interview_id

ps; вообще проблема эффективной корректной оценки количества уникальных значений без вычитывания всей таблицы не очень разрешима алгоритмически к сожалению поэтому возня с ручной простановкой корректных n_distinct на очень больших таблицах для исправления проблемных планов она неустранима на данном этапе.

pps: какие то странные цены у вас база на самом деле высчитывает кстати слишком уж дешевый seq_scan выходит
что у вас показывает
select name,setting from pg_settings where name like '%_cost' order by name;
?

...

Рейтинг:

0 / 0

24.11.2019, 01:58

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

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

#39893499

Maxim Boguk

Участник

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

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

Рейтинг: 0 / 0

Alexey Trizno,

кстати а что у вас показывает

select project_id, count(*) from respondents group by project_id order by 2 desc limit 20;

в ситуации когда в таблице 100 миллионов строк - корректно оценить сколько строк попадет под редкое условие (а project_id = 6 с его 9 строками - очевидно редкое) - тоже к сожалению не реально. Вот база и гадает как умеет.
Проблема называется сильно неравномерное распределение данных (и тоже не имеет хорошего решения кроме решения проблем по месту).

...

Рейтинг:

0 / 0

24.11.2019, 02:03

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

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

#39893537

Alexey Trizno

Гость

Maxim Boguk,

Изначально (перешли на postgresql только года 3 как) база у нас была mssql и подобные проблемы решались просто указанием в запросах - какие индексы надо использовать, если планировщик ошибался. В postgresql такой возможности нет, вот и споткнулись, и теперь надо разбираться - как заставить планировщик выбрать нужное, более правильным и умным путем.

В конфиге на тему cost:

Код: plaintext

1.
2.
3.
4.
5.
6.

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	1.1
seq_page_cost	1

Диски в серверах под базу только ssd и скоро под часть данных будут nvme.

Два запроса для анализа значений - выполню ночью, сейчас они опять же - полностью кладут базу :(

...

Рейтинг:

0 / 0

24.11.2019, 12:21

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

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

#39893539

Alexey Trizno

Гость

Maxim Boguk,

Вот второй запрос выполнился:

Код: plaintext

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

project_id	count
4304	623761
3806	554521
2910	541532
4973	526766
2672	509797
6960	425692
5501	398906
6796	386573
2248	365839
6089	307317
6159	296067
6200	287334
1634	237731
6177	230222
8091	222169
7654	215211
5272	210681
4742	209731
6713	208151
5440	183329

...

Рейтинг:

0 / 0

24.11.2019, 12:22

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

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

#39893559

Maxim Boguk

Участник

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

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

Рейтинг: 0 / 0

Alexey Trizno

Maxim Boguk,

Вот второй запрос выполнился:

Код: plaintext

1.
2.
3.
4.

project_id	count
4304	623761
3806	554521
...

Ну вот для всех этих популярных project_id hash join скорее всего будет быстрее заметно чем nested loop
и форсированное использование индекса бы ситуацию испортило.

ps: а зачем вообще такой запрос используется? я вот с трудом понимаю в какой ситуации его могут выполнить с r.project_id = 4304 например и что после этого делать с результатами (которых будет очень много).

по настройкам я бы скорее всего порекомендовал бы поднять cpu_tuple_cost до 0.1 или уменьшить random/seq _page_cost до 0.11/0.1
у вас база явно недооценивает цену перебора 3.5 миллиардов строк по cpu. Но могут какие то другие планы испортится так что надо осторожно.

...

Рейтинг:

0 / 0

24.11.2019, 12:57

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

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

#39893600

Alexey Trizno

Гость

Maxim Boguk,

Такой запрос - это для миграции базы в другую схему, т.е. массовый перенос данных по проектам с ремапингом всех id в памяти приложением. Т.е. ситуация - редкая.

Сам вопрос возник именно из-за непонимания, почему так. Сейчас понимания уже стало побольше.

"для всех этих популярных project_id hash join скорее всего будет быстрее заметно чем nested loop" - тут есть сомнение, т.к. он же пытался сканировать все 3.5 млрд, вместо например 600 тыс. строк (самое большо кол-во для project_id).
сканировать 3.5 млрд всегда же будет медленнее (это почти 500Gb на диске, и просто даже прочитать такие данные... долго), чем пройтись по индексу.

...

Рейтинг:

0 / 0

24.11.2019, 15:49

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

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

#39893606

qwwq

Участник

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

Рейтинг: 0 / 0

Alexey Trizno

<>
"для всех этих популярных project_id hash join скорее всего будет быстрее заметно чем nested loop" - тут есть сомнение, т.к. он же пытался сканировать все 3.5 млрд, вместо например 600 тыс. строк (самое большо кол-во для project_id).
сканировать 3.5 млрд всегда же будет медленнее (это почти 500Gb на диске, и просто даже прочитать такие данные... долго), чем пройтись по индексу.

покажите оценку костов:

Код: sql

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

explain
select iqd.* 
from respondents r
,lateral (SELECT * FROM interviews i 
		WHERE r.id = i.respondent_id 
		--order by respondent_id ,
		limit 26597) i
,lateral (SELECT * FROM interview_question_details iqd
		WHERE i.id = iqd.interview_id
		order by interview_id, question_number 
		limit 885580) iqd

where r.project_id = 6

...

Рейтинг:

0 / 0

24.11.2019, 16:22

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

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

#39893627

Maxim Boguk

Участник

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

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

Рейтинг: 0 / 0

Alexey Trizno

"для всех этих популярных project_id hash join скорее всего будет быстрее заметно чем nested loop" - тут есть сомнение, т.к. он же пытался сканировать все 3.5 млрд, вместо например 600 тыс. строк (самое большо кол-во для project_id).
сканировать 3.5 млрд всегда же будет медленнее (это почти 500Gb на диске, и просто даже прочитать такие данные... долго), чем пройтись по индексу.

Вот чтобы это базе обьяснить более точно я и предлагал поднять cpu_tuple_cost (цену обработки 1 строки базой).
+ база судя по всему ожидаемо сильно недооценивает количество уникальных interview_id в interview_question_details
и отсюда неверно оценивает сколько строк там будет получатся на выходе.

...

Рейтинг:

0 / 0

24.11.2019, 17:43

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

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

#39893761

Alexey Trizno

Гость

qwwq,

вот как-то так:

Код: plaintext

1.
2.
3.
4.
5.
6.
7.
8.
9.
10.

Nested Loop  (cost=1.83..26492558.82 rows=49417220 width=220)
  ->  Nested Loop  (cost=1.13..876.38 rows=590 width=8)
        ->  Index Only Scan using ix_respondents_projectid_id on respondents r  (cost=0.56..12.82 rows=295 width=8)
              Index Cond: (project_id = 6)
        ->  Limit  (cost=0.56..2.89 rows=2 width=2288)
              ->  Index Scan using fki_interviews_respondentid on interviews i  (cost=0.56..2.89 rows=2 width=2288)
                    Index Cond: (r.id = respondent_id)
  ->  Limit  (cost=0.70..43226.00 rows=83758 width=220)
        ->  Index Scan using ix_interviewquestiondetails_interviewid_questionnumber on interview_question_details iqd  (cost=0.70..43226.00 rows=83758 width=220)
              Index Cond: (i.id = interview_id)

...

Рейтинг:

0 / 0

25.11.2019, 09:33

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

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

#39893763

Alexey Trizno

Гость

Maxim Boguk,

Могу попробовать изменить cost на cpu.
Наверное после надо убрать это:

alter table respondents alter project_id set STATISTICS 10000;
alter table respondents alter project_id set (n_distinct = 10000);

и проверить исходный запрос?
т.к. с этими вот подсказками он и так индекс сейчас использует

...

Рейтинг:

0 / 0

25.11.2019, 09:35

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

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

все

Цитировать

Написать

Автор*:

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

Тема*:

Сообщение

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

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

Автор:

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

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

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

Вложение:

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

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

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

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

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

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

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

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

total:	164ms