Добро пожаловать в форум, Guest  >>   Войти | Регистрация | Поиск | Правила | В избранное | Подписаться
Все форумы / PostgreSQL Новый топик    Ответить
Топик располагается на нескольких страницах: [1] 2   вперед  Ctrl      все
 Почему не используется индекс из двух полей при выборке только по первому полю?  [new]
Alexey Trizno
Member

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

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):

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 есть такой вот подходящий индекс:

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):

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?

При этом, если напишем запрос аля:
select * from interview_question_details where interview_id = 2374287

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

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)

Держать в таблице такого размера два, по сути одинаковых, индекса - очень накладно.
22 ноя 19, 17:32    [22023147]     Ответить | Цитировать Сообщить модератору
 Re: Почему не используется индекс из двух полей при выборке только по первому полю?  [new]
Павел Лузанов
Member

Откуда:
Сообщений: 736
Alexey Trizno,

Вероятно план с доступом по индексу планировщик считает более дорогим.
А покажите план, предварительно выключив: set enable_seqscan = off;
22 ноя 19, 17:52    [22023160]     Ответить | Цитировать Сообщить модератору
 Re: Почему не используется индекс из двух полей при выборке только по первому полю?  [new]
Melkij
Member

Откуда: Санкт-Петербург
Сообщений: 1149
и сделайте 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 появился.
22 ноя 19, 18:32    [22023206]     Ответить | Цитировать Сообщить модератору
 Re: Почему не используется индекс из двух полей при выборке только по первому полю?  [new]
Alexey Trizno
Member

Откуда: Санкт-Петербург
Сообщений: 130
Melkij,

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

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

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 - не получается вообще.
Могу попробовать запустить ночью, если поможет.
22 ноя 19, 18:51    [22023215]     Ответить | Цитировать Сообщить модератору
 Re: Почему не используется индекс из двух полей при выборке только по первому полю?  [new]
Alexey Trizno
Member

Откуда: Санкт-Петербург
Сообщений: 130
Павел Лузанов,

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

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)

Это единственное решение в такой ситуации?
22 ноя 19, 18:55    [22023220]     Ответить | Цитировать Сообщить модератору
 Re: Почему не используется индекс из двух полей при выборке только по первому полю?  [new]
Melkij
Member

Откуда: Санкт-Петербург
Сообщений: 1149
Alexey Trizno,

показывайте explain analyze ещё раз прошу. Не только для исходного, но и для быстрого плана.
Понятно что план со стоимостью на порядок выше планировщику не нравится. Если по факту он быстрее - значит в какой-то момент планировщик ошибается, скорей всего по числу уникальных строк для interview_id. Stats target какой?
22 ноя 19, 19:45    [22023255]     Ответить | Цитировать Сообщить модератору
 Re: Почему не используется индекс из двух полей при выборке только по первому полю?  [new]
Alexey Trizno
Member

Откуда: Санкт-Петербург
Сообщений: 130
Melkij,

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

 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.
23 ноя 19, 17:49    [22023636]     Ответить | Цитировать Сообщить модератору
 Re: Почему не используется индекс из двух полей при выборке только по первому полю?  [new]
vyegorov
Member

Откуда: Рига
Сообщений: 1199
Alexey Trizno,

в продукции не хватает индекса `ON interview_question_details(interview_id)`
23 ноя 19, 18:47    [22023654]     Ответить | Цитировать Сообщить модератору
 Re: Почему не используется индекс из двух полей при выборке только по первому полю?  [new]
Alexey Trizno
Member

Откуда: Санкт-Петербург
Сообщений: 130
vyegorov,

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

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.
23 ноя 19, 18:58    [22023658]     Ответить | Цитировать Сообщить модератору
 Re: Почему не используется индекс из двух полей при выборке только по первому полю?  [new]
Maxim Boguk
Member

Откуда: Melbourne, Австралия
Сообщений: 4297
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 выполнить как я написал):

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 поправим).

Сообщение было отредактировано: 23 ноя 19, 19:47
23 ноя 19, 19:46    [22023680]     Ответить | Цитировать Сообщить модератору
 Re: Почему не используется индекс из двух полей при выборке только по первому полю?  [new]
Melkij
Member

Откуда: Санкт-Петербург
Сообщений: 1149
Alexey Trizno
Melkij,

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

второго 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 опередил с ответом уже
23 ноя 19, 19:49    [22023681]     Ответить | Цитировать Сообщить модератору
 Re: Почему не используется индекс из двух полей при выборке только по первому полю?  [new]
Alexey Trizno
Member

Откуда: Санкт-Петербург
Сообщений: 130
Maxim Boguk,

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

explain analyze select distinct project_id from respondents; 

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

analyze respondents;


explain analyze select distinct project_id from respondents; 

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

Вот как-то так.
23 ноя 19, 19:54    [22023684]     Ответить | Цитировать Сообщить модератору
 Re: Почему не используется индекс из двух полей при выборке только по первому полю?  [new]
Alexey Trizno
Member

Откуда: Санкт-Петербург
Сообщений: 130
Melkij
второго explain analyze так и не увижу?


Я стараюсь, всё будет, но построить индекс новый по interview_id на продуктовой базе (чтобы уж всё было честно) - пока не получается. Поставлю сегодня после 23:00 его создание, там долго. В рабочее время начинает сильно проседать производительность.
23 ноя 19, 19:56    [22023688]     Ответить | Цитировать Сообщить модератору
 Re: Почему не используется индекс из двух полей при выборке только по первому полю?  [new]
Maxim Boguk
Member

Откуда: Melbourne, Австралия
Сообщений: 4297
Alexey Trizno
Maxim Boguk,

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

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.
23 ноя 19, 20:01    [22023689]     Ответить | Цитировать Сообщить модератору
 Re: Почему не используется индекс из двух полей при выборке только по первому полю?  [new]
Alexey Trizno
Member

Откуда: Санкт-Петербург
Сообщений: 130
Maxim Boguk,

Выполнил.

explain analyze select * from respondents where project_id = 6;

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

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

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)

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

Вроде миллиарды записей в табличках - не так и много, в планах умножить эту цифру на много.
В обычной работе такие запросы (как наш проблемный обсуждаемый) не используются - он появился как раз при работе мигратора в новую схему базы, для переноса данных всего проекта разом.
Почему же такая ошибка в статистике?
23 ноя 19, 21:14    [22023719]     Ответить | Цитировать Сообщить модератору
 Re: Почему не используется индекс из двух полей при выборке только по первому полю?  [new]
Maxim Boguk
Member

Откуда: Melbourne, Австралия
Сообщений: 4297
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;
?
24 ноя 19, 01:58    [22023801]     Ответить | Цитировать Сообщить модератору
 Re: Почему не используется индекс из двух полей при выборке только по первому полю?  [new]
Maxim Boguk
Member

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

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

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

в ситуации когда в таблице 100 миллионов строк - корректно оценить сколько строк попадет под редкое условие (а project_id = 6 с его 9 строками - очевидно редкое) - тоже к сожалению не реально. Вот база и гадает как умеет.
Проблема называется сильно неравномерное распределение данных (и тоже не имеет хорошего решения кроме решения проблем по месту).
24 ноя 19, 02:03    [22023802]     Ответить | Цитировать Сообщить модератору
 Re: Почему не используется индекс из двух полей при выборке только по первому полю?  [new]
Alexey Trizno
Member

Откуда: Санкт-Петербург
Сообщений: 130
Maxim Boguk,

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

В конфиге на тему cost:
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.

Два запроса для анализа значений - выполню ночью, сейчас они опять же - полностью кладут базу :(
24 ноя 19, 12:21    [22023897]     Ответить | Цитировать Сообщить модератору
 Re: Почему не используется индекс из двух полей при выборке только по первому полю?  [new]
Alexey Trizno
Member

Откуда: Санкт-Петербург
Сообщений: 130
Maxim Boguk,

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

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
24 ноя 19, 12:22    [22023900]     Ответить | Цитировать Сообщить модератору
 Re: Почему не используется индекс из двух полей при выборке только по первому полю?  [new]
Maxim Boguk
Member

Откуда: Melbourne, Австралия
Сообщений: 4297
Alexey Trizno
Maxim Boguk,

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

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. Но могут какие то другие планы испортится так что надо осторожно.
24 ноя 19, 12:57    [22023923]     Ответить | Цитировать Сообщить модератору
 Re: Почему не используется индекс из двух полей при выборке только по первому полю?  [new]
Alexey Trizno
Member

Откуда: Санкт-Петербург
Сообщений: 130
Maxim Boguk,

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

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

"для всех этих популярных project_id hash join скорее всего будет быстрее заметно чем nested loop" - тут есть сомнение, т.к. он же пытался сканировать все 3.5 млрд, вместо например 600 тыс. строк (самое большо кол-во для project_id).
сканировать 3.5 млрд всегда же будет медленнее (это почти 500Gb на диске, и просто даже прочитать такие данные... долго), чем пройтись по индексу.
24 ноя 19, 15:49    [22024003]     Ответить | Цитировать Сообщить модератору
 Re: Почему не используется индекс из двух полей при выборке только по первому полю?  [new]
qwwq
Member

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


покажите оценку костов:
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
24 ноя 19, 16:22    [22024013]     Ответить | Цитировать Сообщить модератору
 Re: Почему не используется индекс из двух полей при выборке только по первому полю?  [new]
Maxim Boguk
Member

Откуда: Melbourne, Австралия
Сообщений: 4297
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
и отсюда неверно оценивает сколько строк там будет получатся на выходе.
24 ноя 19, 17:43    [22024051]     Ответить | Цитировать Сообщить модератору
 Re: Почему не используется индекс из двух полей при выборке только по первому полю?  [new]
Alexey Trizno
Member

Откуда: Санкт-Петербург
Сообщений: 130
qwwq,

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

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)
25 ноя 19, 09:33    [22024323]     Ответить | Цитировать Сообщить модератору
 Re: Почему не используется индекс из двух полей при выборке только по первому полю?  [new]
Alexey Trizno
Member

Откуда: Санкт-Петербург
Сообщений: 130
Maxim Boguk,

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

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

и проверить исходный запрос?
т.к. с этими вот подсказками он и так индекс сейчас использует
25 ноя 19, 09:35    [22024326]     Ответить | Цитировать Сообщить модератору
Топик располагается на нескольких страницах: [1] 2   вперед  Ctrl      все
Все форумы / PostgreSQL Ответить