ReSQL.ru
     
powered by simpleCommunicator - 2.0.54     © 2025 Programmizd 02
Мобильная версия    Контакт    Правила    FAQ    Помощь
Целевая тема:
Создать новую тему:
Автор:
Закрыть
Цитировать
Доб. в избранное | Игнор. тему | Прикреп. тему | Пометить прочит. / непрочит. | Фильтр
Форумы / PostgreSQL [игнор отключен] [закрыт для гостей] / Тяжелый запрос к связанным таблицам
142 сообщений из 142, показаны все 6 страниц
  все  
Тяжелый запрос к связанным таблицам
    #39327151
maxxstorm
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
maxxstorm
Участник
Есть 2 таблицы(material, materialdata)):

Код: sql
1.
2.
3.
4.
5.
6.
7.
8.
9.
10.
11.
12.
13.
14.
15.
16.
17.
18.
19.
CREATE TABLE san_materialdata (
    id integer NOT NULL,
    material_id integer NOT NULL,
    tag_id integer NOT NULL,

);

CREATE INDEX san_materialdata_76f094bc ON san_materialdata USING btree (tag_id);
CREATE INDEX san_materialdata_eb4b9aaa ON san_materialdata USING btree (material_id);

-------------------------------------------------

CREATE TABLE san_material (
    id integer NOT NULL,

    detection_datetime timestamp with time zone
);

CREATE INDEX san_material_detection_datetime_cb0a0120_uniq ON san_material USING btree (detection_datetime);




Выполняю такой запрос:
Код: sql
1.
2.
3.
4.
SELECT id FROM "san_material" WHERE ("san_material"."detection_datetime" BETWEEN '2016-10-10 00:00:00+03:00' AND '2016-10-14 23:59:59.999999+03:00' 
AND "san_material"."id" IN (SELECT U0."material_id" FROM "san_materialdata" U0 WHERE (U0."tag_id" IN (602)))) 
ORDER BY "san_material"."detection_datetime" DESC
limit 50



По идее должен выполниться быстро, так как на tag_id и detection_datetime висят индексы. Но в реальности происходит следующее:

Код: sql
1.
2.
3.
4.
5.
6.
7.
8.
9.
10.
11.
12.
13.
14.
15.
16.
17.
18.
19.
"Limit  (cost=632168.84..632168.96 rows=50 width=12) (actual time=15984.467..15984.485 rows=50 loops=1)"
"  ->  Sort  (cost=632168.84..632179.99 rows=4461 width=12) (actual time=15984.464..15984.474 rows=50 loops=1)"
"        Sort Key: san_material.detection_datetime DESC"
"        Sort Method: top-N heapsort  Memory: 27kB"
"        ->  Nested Loop  (cost=630741.35..632020.65 rows=4461 width=12) (actual time=8033.408..15966.010 rows=22716 loops=1)"
"              ->  HashAggregate  (cost=630740.92..630742.43 rows=151 width=4) (actual time=8033.052..8282.750 rows=314878 loops=1)"
"                    Group Key: u0.material_id"
"                    ->  Bitmap Heap Scan on san_materialdata u0  (cost=4652.60..630118.97 rows=248779 width=4) (actual time=245.386..7599.882 rows=516671 loops=1)"
"                          Recheck Cond: (tag_id = 602)"
"                          Rows Removed by Index Recheck: 19702747"
"                          Heap Blocks: exact=23189 lossy=240927"
"                          ->  Bitmap Index Scan on san_materialdata_76f094bc  (cost=0.00..4590.41 rows=248779 width=0) (actual time=235.536..235.536 rows=531585 loops=1)"
"                                Index Cond: (tag_id = 602)"
"              ->  Index Scan using san_material_pkey on san_material  (cost=0.43..8.46 rows=1 width=12) (actual time=0.024..0.024 rows=0 loops=314878)"
"                    Index Cond: (id = u0.material_id)"
"                    Filter: ((detection_datetime >= '2016-10-10 00:00:00+03'::timestamp with time zone) AND (detection_datetime <= '2016-10-14 23:59:59.999999+03'::timestamp with time zone))"
"                    Rows Removed by Filter: 1"
"Planning time: 0.960 ms"
"Execution time: 15986.226 ms"



16 секунд длится запрос. Меня смущает Bitmap Heap Scan on san_materialdata u0 (cost=4652.60..630118.97 rows=248779 width=4). Это нормально?
Можно ли как нибудь оптимизировать? 16 секунд это еще не самый долгий запрос. Есть похожие запросы по несколько минут.
...
Рейтинг: 0 / 0
14.10.2016, 15:03
| Ответить | Цитировать | Написать  
Тяжелый запрос к связанным таблицам
    #39327202
SharuPoNemnogu
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
SharuPoNemnogu
Участник
а чего не join?
...
Рейтинг: 0 / 0
14.10.2016, 15:50
| Ответить | Цитировать | Написать  
Тяжелый запрос к связанным таблицам
    #39327206
maxxstorm
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
maxxstorm
Участник
SharuPoNemnoguа чего не join?
джойны чуть побыстрее работают, но не всегда:

Код: sql
1.
2.
3.
4.
5.
6.
7.
8.
9.
10.
11.
12.
13.
14.
15.
16.
17.
18.
"Limit  (cost=1011190.20..1011190.33 rows=50 width=12) (actual time=12039.094..12039.107 rows=50 loops=1)"
"  ->  Sort  (cost=1011190.20..1011229.74 rows=15813 width=12) (actual time=12039.090..12039.096 rows=50 loops=1)"
"        Sort Key: san_material.detection_datetime DESC"
"        Sort Method: top-N heapsort  Memory: 27kB"
"        ->  Hash Join  (cost=379879.93..1010664.91 rows=15813 width=12) (actual time=11380.787..12031.376 rows=31987 loops=1)"
"              Hash Cond: (san_materialdata.material_id = san_material.id)"
"              ->  Bitmap Heap Scan on san_materialdata  (cost=4653.45..630392.97 rows=248888 width=4) (actual time=242.964..7264.115 rows=516936 loops=1)"
"                    Recheck Cond: (tag_id = 602)"
"                    Rows Removed by Index Recheck: 19702370"
"                    Heap Blocks: exact=23317 lossy=240925"
"                    ->  Bitmap Index Scan on san_materialdata_76f094bc  (cost=0.00..4591.23 rows=248888 width=0) (actual time=233.170..233.170 rows=531850 loops=1)"
"                          Index Cond: (tag_id = 602)"
"              ->  Hash  (cost=368078.14..368078.14 rows=411227 width=12) (actual time=4355.421..4355.421 rows=420070 loops=1)"
"                    Buckets: 131072  Batches: 8  Memory Usage: 3496kB"
"                    ->  Index Scan Backward using san_material_detection_datetime_cb0a0120_uniq on san_material  (cost=0.43..368078.14 rows=411227 width=12) (actual time=0.066..4043.210 rows=420070 loops=1)"
"                          Index Cond: ((detection_datetime >= '2016-10-10 00:00:00+03'::timestamp with time zone) AND (detection_datetime <= '2016-10-14 00:00:00+03'::timestamp with time zone))"
"Planning time: 0.960 ms"
"Execution time: 12039.197 ms"
...
Рейтинг: 0 / 0
14.10.2016, 15:54
| Ответить | Цитировать | Написать  
Тяжелый запрос к связанным таблицам
    #39327233
SharuPoNemnogu
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
SharuPoNemnogu
Участник
maxxstorm,

это вот с такого запроса explain ?
Код: plsql
1.
2.
3.
4.
5.
6.
7.
SELECT sm.id
FROM san_material sm
INNER JOIN san_materialdata smd ON sm.id = smd.material_id
           AND smd.tag_id = 602
WHERE sm.detection_datetime BETWEEN '2016-10-10 00:00:00+03:00' AND '2016-10-14 23:59:59.999999+03:00'
ORDER BY sm.detection_datetime DESC
LIMIT 50;
...
Рейтинг: 0 / 0
14.10.2016, 16:15
| Ответить | Цитировать | Написать  
Тяжелый запрос к связанным таблицам
    #39327236
maxxstorm
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
maxxstorm
Участник
SharuPoNemnogumaxxstorm,

это вот с такого запроса explain ?
Код: plsql
1.
2.
3.
4.
5.
6.
7.
SELECT sm.id
FROM san_material sm
INNER JOIN san_materialdata smd ON sm.id = smd.material_id
           AND smd.tag_id = 602
WHERE sm.detection_datetime BETWEEN '2016-10-10 00:00:00+03:00' AND '2016-10-14 23:59:59.999999+03:00'
ORDER BY sm.detection_datetime DESC
LIMIT 50;


Код: sql
1.
2.
3.
4.
5.
6.
7.
8.
9.
10.
11.
12.
13.
14.
15.
16.
17.
18.
"Limit  (cost=1042286.46..1042286.59 rows=50 width=12) (actual time=12629.650..12629.662 rows=50 loops=1)"
"  ->  Sort  (cost=1042286.46..1042329.44 rows=17190 width=12) (actual time=12629.648..12629.652 rows=50 loops=1)"
"        Sort Key: sm.detection_datetime DESC"
"        Sort Method: top-N heapsort  Memory: 27kB"
"        ->  Hash Join  (cost=410493.83..1041715.42 rows=17190 width=12) (actual time=11955.837..12619.864 rows=35951 loops=1)"
"              Hash Cond: (smd.material_id = sm.id)"
"              ->  Bitmap Heap Scan on san_materialdata smd  (cost=4658.21..630645.20 rows=248986 width=4) (actual time=226.175..7282.886 rows=517132 loops=1)"
"                    Recheck Cond: (tag_id = 602)"
"                    Rows Removed by Index Recheck: 19701960"
"                    Heap Blocks: exact=23423 lossy=240924"
"                    ->  Bitmap Index Scan on san_materialdata_76f094bc  (cost=0.00..4595.96 rows=248986 width=0) (actual time=218.932..218.932 rows=532046 loops=1)"
"                          Index Cond: (tag_id = 602)"
"              ->  Hash  (cost=398064.20..398064.20 rows=447074 width=12) (actual time=4913.849..4913.849 rows=470948 loops=1)"
"                    Buckets: 131072  Batches: 8  Memory Usage: 3794kB"
"                    ->  Index Scan Backward using san_material_detection_datetime_cb0a0120_uniq on san_material sm  (cost=0.43..398064.20 rows=447074 width=12) (actual time=0.038..4578.718 rows=470948 loops=1)"
"                          Index Cond: ((detection_datetime >= '2016-10-10 00:00:00+03'::timestamp with time zone) AND (detection_datetime <= '2016-10-14 23:59:59.999999+03'::timestamp with time zone))"
"Planning time: 0.751 ms"
"Execution time: 12629.752 ms"
...
Рейтинг: 0 / 0
14.10.2016, 16:16
| Ответить | Цитировать | Написать  
Тяжелый запрос к связанным таблицам
    #39327239
maxxstorm
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
maxxstorm
Участник
К тому же, если джойн делать, то надо distinct или group by, потому что material_id в materialdata могут повторяться. Поэтому эксплейн еще вырастет
...
Рейтинг: 0 / 0
14.10.2016, 16:20
| Ответить | Цитировать | Написать  
Тяжелый запрос к связанным таблицам
    #39327253
SharuPoNemnogu
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
SharuPoNemnogu
Участник
а если составной индекс на material_id, tag_id?
и еще попробовать вариант с exists, вместо join
...
Рейтинг: 0 / 0
14.10.2016, 16:27
| Ответить | Цитировать | Написать  
Тяжелый запрос к связанным таблицам
    #39327260
big-trot
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
big-trot
Гость
автор(cost=0.00..4591.23 rows=248888 width=0) (actual time=233.170..233.170 rows=531850 loops=1)
похоже статистика устарела. Сделайте analyze san_materialdata.
...
Рейтинг: 0 / 0
14.10.2016, 16:36
| Ответить | Цитировать | Написать  
Тяжелый запрос к связанным таблицам
    #39327304
qwwq
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
qwwq
Участник
1.
Код: sql
1.
2.
3.
4.
5.
6.
7.
8.
9.
10.
SELECT sm.id
FROM san_material sm
INNER JOIN san_materialdata smd ON sm.id = smd.material_id
           AND smd.tag_id = 602
WHERE 
           -- sm.detection_datetime BETWEEN '2016-10-10 00:00:00+03:00' AND '2016-10-14 23:59:59.999999+03:00' -- убивать
           sm.detection_datetime >='2016-10-10 00:00:00+03:00'
           AND sm.detection_datetime<'2016-10-15 00:00:00+03:00'
ORDER BY sm.detection_datetime DESC
LIMIT 50;



2 куда--то вот сюдой можно подумать


Код: sql
1.
2.
3.
4.
5.
6.
7.
8.
9.
10.
11.
12.
13.
14.
SELECT sm.id
FROM san_materialdata smd
,LATERAL (SELECT  sm.id , sm.detection_datetime
    FROM san_material sm
    WHERE sm.id = smd.material_id
           AND sm.detection_datetime >='2016-10-10 00:00:00+03:00'
           AND sm.detection_datetime<'2016-10-15 00:00:00+03:00'
ORDER BY sm.detection_datetime DESC
LIMIT 50
) sm
WHERE 
           smd.tag_id = 602           
ORDER BY sm.detection_datetime DESC
LIMIT 50;



к чему индекс типа (id,detection_datetime) на san_material.
но, думается, т.к. таких тагов не менее 500 000 -- это будет плохо. (0.5 -- 2.5 ляма произвольных доступов)

тот случай, когда можно посмотреть возможность tag_ids integer[] в самой san_material (если оно редко обновляется). с гистами бтри_гистами и т.п.
...
Рейтинг: 0 / 0
14.10.2016, 17:47
| Ответить | Цитировать | Написать  
Тяжелый запрос к связанным таблицам
    #39327306
qwwq
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
qwwq
Участник
qwwq,

чото я не падумал, что ид==пк, поторопился.
таки сколько записей в san_material" (без филтрации тагов) в оценке
Код: sql
1.
san_material" WHERE ("san_material"."detection_datetime" BETWEEN '2016-10-10 00:00:00+03:00' AND '2016-10-14 23:59:59.999999+03:00' )


?
...
Рейтинг: 0 / 0
14.10.2016, 17:54
| Ответить | Цитировать | Написать  
Тяжелый запрос к связанным таблицам
    #39327309
maxxstorm
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
maxxstorm
Участник
qwwqqwwq,

чото я не падумал, что ид==пк, поторопился.
таки сколько записей в san_material" (без филтрации тагов) в оценке
Код: sql
1.
san_material" WHERE ("san_material"."detection_datetime" BETWEEN '2016-10-10 00:00:00+03:00' AND '2016-10-14 23:59:59.999999+03:00' )


?

6.000.000 в таблице san_material всего
110.000.000 в таблице san_materialdata всего

За эту конкретную дату примерно
500.000 в san_material
10.000.000 в san_materaildata
...
Рейтинг: 0 / 0
14.10.2016, 17:59
| Ответить | Цитировать | Написать  
Тяжелый запрос к связанным таблицам
    #39327313
qwwq
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
qwwq
Участник
maxxstorm
6.000.000 в таблице san_material всего
110.000.000 в таблице san_materialdata всего

За эту конкретную дату примерно
500.000 в san_material
10.000.000 в san_materaildataт.е в среднем 20 тагов на материал ?
а сколько всего уникальных тагов ?
т.е. какая вероятность того, что первый попавшийся материал имеет заданный таг ?

м.б. просто бежать вдоль времени, и проверять (лейтералом и т.п.), что таг подходит ?
...
Рейтинг: 0 / 0
14.10.2016, 18:09
| Ответить | Цитировать | Написать  
Тяжелый запрос к связанным таблицам
    #39327323
maxxstorm
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
maxxstorm
Участник
qwwq,

100 тагов. То есть вероятность 1 к 5, если предположить, что они равномерно повешены.

По поводу проверять лейтералом можно поподробнее?
...
Рейтинг: 0 / 0
14.10.2016, 18:24
| Ответить | Цитировать | Написать  
Тяжелый запрос к связанным таблицам
    #39327339
Фотография vyegorov
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
vyegorov
Участник
maxxstorm,

`work_mem` покажите. И заодно попробуйте его поднять перед запуском запроса:
Код: sql
1.
SET work_mem TO '64MB';
...
Рейтинг: 0 / 0
14.10.2016, 19:00
| Ответить | Цитировать | Написать  
Тяжелый запрос к связанным таблицам
    #39327433
qwwq
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
qwwq
Участник
Код: sql
1.
2.
3.
4.
5.
6.
7.
8.
9.
10.
11.
12.
13.
SELECT  sm.id , sm.detection_datetime
FROM san_material sm
WHERE 
           sm.detection_datetime >='2016-10-10 00:00:00+03:00'
           AND sm.detection_datetime<'2016-10-15 00:00:00+03:00'
          AND EXISTS( SELECT 1 FROM 
                               FROM san_materialdata smd
                               WHERE 
                                    sm.id = smd.material_id
                                    AND smd.tag_id = 602
                                )
ORDER BY sm.detection_datetime DESC
LIMIT 50;


ожидаемые затраты - 50*5 произвольных доступов вдоль индекса + до ~20 [тагов в материале] -- вдоль проверяемого (полу--пессимистическая 50*5*20). если конечно вы оффсет потом не напишете.

неплохо пойдет индекс (tag_id,material_id) или симметричный. чтобы вот в ту 20--ку не упираться.
а лейтерал не нужен, вы из второй только фильтр дёргаете, его екзистс исполняет.
...
Рейтинг: 0 / 0
14.10.2016, 22:55
| Ответить | Цитировать | Написать  
Тяжелый запрос к связанным таблицам
    #39327435
qwwq
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
qwwq
Участник
qwwq
Код: sql
1.
2.
3.
4.
5.
6.
7.
8.
9.
10.
11.
12.
13.
SELECT  sm.id , sm.detection_datetime
FROM san_material sm
WHERE 
           sm.detection_datetime >='2016-10-10 00:00:00+03:00'
           AND sm.detection_datetime<'2016-10-15 00:00:00+03:00'
          AND EXISTS( SELECT 1  
                               FROM san_materialdata smd
                               WHERE 
                                    sm.id = smd.material_id
                                    AND smd.tag_id = 602
                                )
ORDER BY sm.detection_datetime DESC
LIMIT 50;


ожидаемые затраты - 50*5 произвольных доступов вдоль индекса + до ~20 [тагов в материале] -- вдоль проверяемого (полу--пессимистическая 50*5*20). если конечно вы оффсет потом не напишете.

неплохо пойдет индекс (tag_id,material_id) или симметричный. чтобы вот в ту 20--ку не упираться.
а лейтерал не нужен, вы из второй только фильтр дёргаете, его екзистс исполняет.
--поправил
...
Рейтинг: 0 / 0
14.10.2016, 22:57
| Ответить | Цитировать | Написать  
Тяжелый запрос к связанным таблицам
    #39327951
maxxstorm
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
maxxstorm
Участник
vyegorovmaxxstorm,

`work_mem` покажите. И заодно попробуйте его поднять перед запуском запроса:
Код: sql
1.
SET work_mem TO '64MB';



4mb
...
Рейтинг: 0 / 0
17.10.2016, 08:48
| Ответить | Цитировать | Написать  
Тяжелый запрос к связанным таблицам
    #39327953
maxxstorm
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
maxxstorm
Участник
qwwqqwwq
Код: sql
1.
2.
3.
4.
5.
6.
7.
8.
9.
10.
11.
12.
13.
SELECT  sm.id , sm.detection_datetime
FROM san_material sm
WHERE 
           sm.detection_datetime >='2016-10-10 00:00:00+03:00'
           AND sm.detection_datetime<'2016-10-15 00:00:00+03:00'
          AND EXISTS( SELECT 1  
                               FROM san_materialdata smd
                               WHERE 
                                    sm.id = smd.material_id
                                    AND smd.tag_id = 602
                                )
ORDER BY sm.detection_datetime DESC
LIMIT 50;


ожидаемые затраты - 50*5 произвольных доступов вдоль индекса + до ~20 [тагов в материале] -- вдоль проверяемого (полу--пессимистическая 50*5*20). если конечно вы оффсет потом не напишете.

неплохо пойдет индекс (tag_id,material_id) или симметричный. чтобы вот в ту 20--ку не упираться.
а лейтерал не нужен, вы из второй только фильтр дёргаете, его екзистс исполняет.
--поправил

Те же 16 секунд. Причем первоначально запрос выполнялся 150 секунд, а повторно 16. И мои оригинальные столько же. Походу 16 секунд это кэш, а 150 реальное время выполнения
...
Рейтинг: 0 / 0
17.10.2016, 08:52
| Ответить | Цитировать | Написать  
Тяжелый запрос к связанным таблицам
    #39327954
maxxstorm
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
maxxstorm
Участник
Код: sql
1.
2.
3.
4.
5.
6.
7.
8.
9.
10.
11.
12.
13.
14.
15.
16.
17.
18.
"Limit  (cost=639430.56..639430.68 rows=50 width=12) (actual time=92772.916..92772.935 rows=50 loops=1)"
"  ->  Sort  (cost=639430.56..639440.74 rows=4075 width=12) (actual time=92772.915..92772.922 rows=50 loops=1)"
"        Sort Key: sm.detection_datetime DESC"
"        Sort Method: top-N heapsort  Memory: 27kB"
"        ->  Nested Loop  (cost=638015.89..639295.19 rows=4075 width=12) (actual time=48860.399..92755.021 rows=15613 loops=1)"
"              ->  HashAggregate  (cost=638015.46..638016.97 rows=151 width=4) (actual time=48860.197..49033.202 rows=193278 loops=1)"
"                    Group Key: smd.material_id"
"                    ->  Bitmap Heap Scan on san_materialdata smd  (cost=4706.84..637386.34 rows=251648 width=4) (actual time=225.466..48465.411 rows=313019 loops=1)"
"                          Recheck Cond: (tag_id = 622)"
"                          Heap Blocks: exact=173042"
"                          ->  Bitmap Index Scan on san_materialdata_76f094bc  (cost=0.00..4643.93 rows=251648 width=0) (actual time=126.899..126.899 rows=313158 loops=1)"
"                                Index Cond: (tag_id = 622)"
"              ->  Index Scan using san_material_pkey on san_material sm  (cost=0.43..8.46 rows=1 width=12) (actual time=0.225..0.225 rows=0 loops=193278)"
"                    Index Cond: (id = smd.material_id)"
"                    Filter: ((detection_datetime >= '2016-10-10 00:00:00+03'::timestamp with time zone) AND (detection_datetime < '2016-10-15 00:00:00+03'::timestamp with time zone))"
"                    Rows Removed by Filter: 1"
"Planning time: 0.943 ms"
"Execution time: 92773.037 ms"
...
Рейтинг: 0 / 0
17.10.2016, 08:53
| Ответить | Цитировать | Написать  
Тяжелый запрос к связанным таблицам
    #39327964
Фотография Legushka
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Legushka
Участник
maxxstorm, попробуйте сделать индекс для таблицы san_material на поле detection_datetime
...
Рейтинг: 0 / 0
17.10.2016, 09:17
| Ответить | Цитировать | Написать  
Тяжелый запрос к связанным таблицам
    #39327965
maxxstorm
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
maxxstorm
Участник
Legushkamaxxstorm, попробуйте сделать индекс для таблицы san_material на поле detection_datetime

Индекс имеется
...
Рейтинг: 0 / 0
17.10.2016, 09:18
| Ответить | Цитировать | Написать  
Тяжелый запрос к связанным таблицам
    #39328007
Фотография Legushka
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Legushka
Участник
maxxstormИндекс имеется
могли бы вы показать как создан индекс на detection_datetime?

может попробовать делать не AND sm.detection_datetime<'2016-10-15 00:00:00+03:00'
a по включительную дату:
AND sm.detection_datetime<='2016-10-14 23:59:59+03:00'

просто в плане не видно что бы индекс работал.
...
Рейтинг: 0 / 0
17.10.2016, 10:15
| Ответить | Цитировать | Написать  
Тяжелый запрос к связанным таблицам
    #39328009
maxxstorm
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
maxxstorm
Участник
LegushkamaxxstormИндекс имеется
могли бы вы показать как создан индекс на detection_datetime?

может попробовать делать не AND sm.detection_datetime<'2016-10-15 00:00:00+03:00'
a по включительную дату:
AND sm.detection_datetime<='2016-10-14 23:59:59+03:00'

просто в плане не видно что бы индекс работал.

А вот в первом посте я написал:

Код: sql
1.
2.
3.
4.
5.
6.
7.
CREATE TABLE san_material (
    id integer NOT NULL,

    detection_datetime timestamp with time zone
);

CREATE INDEX san_material_detection_datetime_cb0a0120_uniq ON san_material USING btree (detection_datetime);
...
Рейтинг: 0 / 0
17.10.2016, 10:16
| Ответить | Цитировать | Написать  
Тяжелый запрос к связанным таблицам
    #39328018
Фотография Maxim Boguk
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Maxim Boguk
Участник
LegushkamaxxstormИндекс имеется
могли бы вы показать как создан индекс на detection_datetime?

может попробовать делать не AND sm.detection_datetime<'2016-10-15 00:00:00+03:00'
a по включительную дату:
AND sm.detection_datetime<='2016-10-14 23:59:59+03:00'

просто в плане не видно что бы индекс работал.

Так и не должен работать учитывая
авторЗа эту конкретную дату примерно
500.000 в san_material
полмиллиона строк под условие.
Вопрос к автору... как вы ожидаете при то что у вас 500.000 строк на дату "По идее должен выполниться быстро"?


Я бы сделал индекс вида san_material(id, detection_datetime) для начала.
И очень сильно бы поднял work_mem (до 32 или 64MB пока у вас не станет lossy=0 вместо lossy=240927 в плане).

Скорее всего станет лучше. Ну и увеличить shared_buffers чтобы данные нормально в памяти помещались.

--
Maxim Boguk
www.postgresql-consulting.ru
...
Рейтинг: 0 / 0
17.10.2016, 10:22
| Ответить | Цитировать | Написать  
Тяжелый запрос к связанным таблицам
    #39328060
Фотография vyegorov
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
vyegorov
Участник
maxxstorm,

В первом плане было:
Код: sql
1.
2.
3.
4.
"                    ->  Bitmap Heap Scan on san_materialdata u0  (cost=4652.60..630118.97 rows=248779 width=4) (actual time=245.386..7599.882 rows=516671 loops=1)"
"                          Recheck Cond: (tag_id = 602)"
"                          Rows Removed by Index Recheck: 19702747"
"                          Heap Blocks: exact=23189 lossy=240927"


Потом стало:
Код: sql
1.
2.
3.
"                    ->  Bitmap Heap Scan on san_materialdata smd  (cost=4706.84..637386.34 rows=251648 width=4) (actual time=225.466..48465.411 rows=313019 loops=1)"
"                          Recheck Cond: (tag_id = 622)"
"                          Heap Blocks: exact=173042"


Что за сервер? И что на нём кроме базы бегает?
...
Рейтинг: 0 / 0
17.10.2016, 11:30
| Ответить | Цитировать | Написать  
Тяжелый запрос к связанным таблицам
    #39328063
maxxstorm
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
maxxstorm
Участник
vyegorovmaxxstorm,

В первом плане было:
Код: sql
1.
2.
3.
4.
"                    ->  Bitmap Heap Scan on san_materialdata u0  (cost=4652.60..630118.97 rows=248779 width=4) (actual time=245.386..7599.882 rows=516671 loops=1)"
"                          Recheck Cond: (tag_id = 602)"
"                          Rows Removed by Index Recheck: 19702747"
"                          Heap Blocks: exact=23189 lossy=240927"


Потом стало:
Код: sql
1.
2.
3.
"                    ->  Bitmap Heap Scan on san_materialdata smd  (cost=4706.84..637386.34 rows=251648 width=4) (actual time=225.466..48465.411 rows=313019 loops=1)"
"                          Recheck Cond: (tag_id = 622)"
"                          Heap Blocks: exact=173042"


Что за сервер? И что на нём кроме базы бегает?

Это от разных запросов планы.
На сервере еще много сервисов, rabbitmq, например.
...
Рейтинг: 0 / 0
17.10.2016, 11:35
| Ответить | Цитировать | Написать  
Тяжелый запрос к связанным таблицам
    #39328111
maxxstorm
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
maxxstorm
Участник
Пробую выполнить другой запрос:

Код: sql
1.
SELECT count(U0."material_id") FROM "san_materialdata" U0 WHERE (U0."tag_id" IN (622))



Код: sql
1.
2.
3.
4.
5.
6.
7.
8.
"Aggregate  (cost=354454.70..354454.71 rows=1 width=4) (actual time=177751.140..177751.140 rows=1 loops=1)"
"  ->  Bitmap Heap Scan on san_materialdata u0  (cost=3123.60..354125.98 rows=131489 width=4) (actual time=894.821..177688.042 rows=77969 loops=1)"
"        Recheck Cond: (tag_id = 622)"
"        Heap Blocks: exact=47755"
"        ->  Bitmap Index Scan on san_materialdata_76f094bc  (cost=0.00..3090.73 rows=131489 width=0) (actual time=882.126..882.126 rows=78950 loops=1)"
"              Index Cond: (tag_id = 622)"
"Planning time: 0.135 ms"
"Execution time: 177751.210 ms"



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

Код: sql
1.
2.
3.
4.
5.
6.
7.
8.
"Aggregate  (cost=354454.70..354454.71 rows=1 width=4) (actual time=126.222..126.223 rows=1 loops=1)"
"  ->  Bitmap Heap Scan on san_materialdata u0  (cost=3123.60..354125.98 rows=131489 width=4) (actual time=23.161..114.814 rows=77969 loops=1)"
"        Recheck Cond: (tag_id = 622)"
"        Heap Blocks: exact=47755"
"        ->  Bitmap Index Scan on san_materialdata_76f094bc  (cost=0.00..3090.73 rows=131489 width=0) (actual time=12.191..12.191 rows=78950 loops=1)"
"              Index Cond: (tag_id = 622)"
"Planning time: 0.139 ms"
"Execution time: 126.281 ms"



Кэш?
...
Рейтинг: 0 / 0
17.10.2016, 12:27
| Ответить | Цитировать | Написать  
Тяжелый запрос к связанным таблицам
    #39328142
Фотография vyegorov
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
vyegorov
Участник
maxxstorm,

Скорее всего. `EXPLAIN (analyze, buffers)` покажет доступ к блокам.
...
Рейтинг: 0 / 0
17.10.2016, 13:13
| Ответить | Цитировать | Написать  
Тяжелый запрос к связанным таблицам
    #39328154
Фотография Maxim Boguk
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Maxim Boguk
Участник
maxxstormПробую выполнить другой запрос:

Код: sql
1.
SELECT count(U0."material_id") FROM "san_materialdata" U0 WHERE (U0."tag_id" IN (622))



Код: sql
1.
2.
3.
4.
5.
6.
7.
8.
"Aggregate  (cost=354454.70..354454.71 rows=1 width=4) (actual time=177751.140..177751.140 rows=1 loops=1)"
"  ->  Bitmap Heap Scan on san_materialdata u0  (cost=3123.60..354125.98 rows=131489 width=4) (actual time=894.821..177688.042 rows=77969 loops=1)"
"        Recheck Cond: (tag_id = 622)"
"        Heap Blocks: exact=47755"
"        ->  Bitmap Index Scan on san_materialdata_76f094bc  (cost=0.00..3090.73 rows=131489 width=0) (actual time=882.126..882.126 rows=78950 loops=1)"
"              Index Cond: (tag_id = 622)"
"Planning time: 0.135 ms"
"Execution time: 177751.210 ms"



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

Код: sql
1.
2.
3.
4.
5.
6.
7.
8.
"Aggregate  (cost=354454.70..354454.71 rows=1 width=4) (actual time=126.222..126.223 rows=1 loops=1)"
"  ->  Bitmap Heap Scan on san_materialdata u0  (cost=3123.60..354125.98 rows=131489 width=4) (actual time=23.161..114.814 rows=77969 loops=1)"
"        Recheck Cond: (tag_id = 622)"
"        Heap Blocks: exact=47755"
"        ->  Bitmap Index Scan on san_materialdata_76f094bc  (cost=0.00..3090.73 rows=131489 width=0) (actual time=12.191..12.191 rows=78950 loops=1)"
"              Index Cond: (tag_id = 622)"
"Planning time: 0.139 ms"
"Execution time: 126.281 ms"



Кэш?


Не кеш. Ну точнее кеш не в том смысле что вы думаете.
В первом случае куча данных читалась с диска (что не быстро).
Во втором случае данные читались из shared buffers базы.
Хотите чтобы было быстро - данные должны в shared buffers лежать (учитывая что сервер не выделенный и походу с очень медленными дисками).
И про увеличение shared buffers я вам написал уже.

--
Maxim Boguk
www.postgresql-consulting.ru
...
Рейтинг: 0 / 0
17.10.2016, 13:27
| Ответить | Цитировать | Написать  
Тяжелый запрос к связанным таблицам
    #39328173
maxxstorm
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
maxxstorm
Участник
Maxim Boguk

Не кеш. Ну точнее кеш не в том смысле что вы думаете.
В первом случае куча данных читалась с диска (что не быстро).
Во втором случае данные читались из shared buffers базы.
Хотите чтобы было быстро - данные должны в shared buffers лежать (учитывая что сервер не выделенный и походу с очень медленными дисками).
И про увеличение shared buffers я вам написал уже.

--
Maxim Boguk
www.postgresql-consulting.ru

Но база весит 50гб, а shared buffers у меня 16 гб. Как быть?
...
Рейтинг: 0 / 0
17.10.2016, 13:46
| Ответить | Цитировать | Написать  
Тяжелый запрос к связанным таблицам
    #39328208
qwwq
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
qwwq
Участник
maxxstorm
 
Код: sql
1.
2.
3.
4.
5.
6.
7.
8.
9.
10.
11.
12.
13.
14.
15.
16.
17.
18.
"Limit  (cost=639430.56..639430.68 rows=50 width=12) (actual time=92772.916..92772.935 rows=50 loops=1)"
"  ->  Sort  (cost=639430.56..639440.74 rows=4075 width=12) (actual time=92772.915..92772.922 rows=50 loops=1)"
"        Sort Key: sm.detection_datetime DESC"
"        Sort Method: top-N heapsort  Memory: 27kB"
"        ->  Nested Loop  (cost=638015.89..639295.19 rows=4075 width=12) (actual time=48860.399..92755.021 rows=15613 loops=1)"
"              ->  HashAggregate  (cost=638015.46..638016.97 rows=151 width=4) (actual time=48860.197..49033.202 rows=193278 loops=1)"
"                    Group Key: smd.material_id"
"                    ->  Bitmap Heap Scan on san_materialdata smd  (cost=4706.84..637386.34 rows=251648 width=4) (actual time=225.466..48465.411 rows=313019 loops=1)"
"                          Recheck Cond: (tag_id = 622)"
"                          Heap Blocks: exact=173042"
"                          ->  Bitmap Index Scan on san_materialdata_76f094bc  (cost=0.00..4643.93 rows=251648 width=0) (actual time=126.899..126.899 rows=313158 loops=1)"
"                                Index Cond: (tag_id = 622)"
"              ->  Index Scan using san_material_pkey on san_material sm  (cost=0.43..8.46 rows=1 width=12) (actual time=0.225..0.225 rows=0 loops=193278)"
"                    Index Cond: (id = smd.material_id)"
"                    Filter: ((detection_datetime >= '2016-10-10 00:00:00+03'::timestamp with time zone) AND (detection_datetime < '2016-10-15 00:00:00+03'::timestamp with time zone))"
"                    Rows Removed by Filter: 1"
"Planning time: 0.943 ms"
"Execution time: 92773.037 ms"


надо бороться за другой план :

в ожидании,, что условие фильтра вдоль индекса ""
Код: sql
1.
san_material_detection_datetime_cb0a0120_uniq ON san_material USING btree (detection_datetime);


у вас выполняется довольно часто, и 50 наберется быстро.

как загнать -- надо думать. я предлагал
авториндекс (tag_id,material_id) (10--20 отыграть множитель)

но попробуйте [без этого индекса] LIMIT 1 пока поставить в итоге, вместо 50 . потом костами (не помню, но каким то костом запинывалось) или енейблами хеш--агг/джойн ('enable_hashagg','enable_hashjoin' TO OFF) отключить -- посмотрим, как быстро вдоль индекса по detection_datetime условие фильтра срабатывает. не надо нам эти пол--ляма читать, а потом сортировать дикие выборки, думаю. (там на 50 умножить -- время оценить)

но если фильтр не так часто -- то тогда пж всё правильно делает.
...
Рейтинг: 0 / 0
17.10.2016, 14:26
| Ответить | Цитировать | Написать  
Тяжелый запрос к связанным таблицам
    #39328218
maxxstorm
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
maxxstorm
Участник
vyegorovmaxxstorm,

Скорее всего. `EXPLAIN (analyze, buffers)` покажет доступ к блокам.

Показывает вот что:

Код: sql
1.
2.
3.
4.
5.
6.
7.
8.
9.
10.
11.
"Aggregate  (cost=630038.79..630038.80 rows=1 width=4) (actual time=84728.395..84728.395 rows=1 loops=1)"
"  Buffers: shared hit=4 read=271373 written=38"
"  ->  Bitmap Heap Scan on san_materialdata u0  (cost=4615.91..629422.23 rows=246625 width=4) (actual time=2494.228..84496.662 rows=528156 loops=1)"
"        Recheck Cond: (tag_id = 602)"
"        Heap Blocks: exact=269804"
"        Buffers: shared hit=4 read=271373 written=38"
"        ->  Bitmap Index Scan on san_materialdata_76f094bc  (cost=0.00..4554.26 rows=246625 width=0) (actual time=2349.480..2349.480 rows=543070 loops=1)"
"              Index Cond: (tag_id = 602)"
"              Buffers: shared hit=4 read=1569"
"Planning time: 0.187 ms"
"Execution time: 84728.476 ms"



Я так понимаю, что 271373 записи уже были в буфере?
...
Рейтинг: 0 / 0
17.10.2016, 14:34
| Ответить | Цитировать | Написать  
Тяжелый запрос к связанным таблицам
    #39328229
qwwq
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
qwwq
Участник
вспомнил, как запинывать без енейблов :

qwwqqwwq
Код: sql
1.
2.
3.
4.
5.
6.
7.
8.
9.
10.
11.
12.
13.
14.
SELECT  sm.id , sm.detection_datetime
FROM san_material sm
WHERE 
           sm.detection_datetime >='2016-10-10 00:00:00+03:00'
           AND sm.detection_datetime<'2016-10-15 00:00:00+03:00'
          AND EXISTS( SELECT 1  
                               FROM san_materialdata smd
                               WHERE 
                                    sm.id = smd.material_id
                                    AND smd.tag_id+0 = 602 -- запинываем 1 на индекс по material_id
                                ORDER BY material_id LIMIT 1 -- припарки, но иногда помогает
                                )
ORDER BY sm.detection_datetime DESC
LIMIT 1 /* 50 */;


ожидаемые затраты - 50*5 произвольных доступов вдоль индекса + до ~20 [тагов в материале] -- вдоль проверяемого (полу--пессимистическая 50*5*20). если конечно вы оффсет потом не напишете.

неплохо пойдет индекс (tag_id,material_id) или симметричный. чтобы вот в ту 20--ку не упираться.
а лейтерал не нужен, вы из второй только фильтр дёргаете, его екзистс исполняет.
--поправил

и покажите план, даже если плохой . (если не дождетесь аналайза -- то просто план)
...
Рейтинг: 0 / 0
17.10.2016, 14:43
| Ответить | Цитировать | Написать  
Тяжелый запрос к связанным таблицам
    #39328241
maxxstorm
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
maxxstorm
Участник
qwwqвспомнил, как запинывать без енейблов :

qwwqпропущено...

--поправил

и покажите план, даже если плохой . (если не дождетесь аналайза -- то просто план)

С limit 1 очень быстро работает(и первоначальный запрос тоже):

Код: sql
1.
2.
3.
4.
5.
6.
7.
8.
9.
10.
"Limit  (cost=1.00..71727.60 rows=1 width=12) (actual time=0.859..0.860 rows=1 loops=1)"
"  ->  Nested Loop Semi Join  (cost=1.00..289201637.19 rows=4032 width=12) (actual time=0.858..0.858 rows=1 loops=1)"
"        ->  Index Scan Backward using san_material_detection_datetime_cb0a0120_uniq on san_material sm  (cost=0.43..372519.13 rows=415430 width=12) (actual time=0.082..0.321 rows=23 loops=1)"
"              Index Cond: ((detection_datetime >= '2016-10-10 00:00:00+03'::timestamp with time zone) AND (detection_datetime < '2016-10-15 00:00:00+03'::timestamp with time zone))"
"        ->  Index Scan using san_materialdata_eb4b9aaa on san_materialdata smd  (cost=0.57..5521.25 rows=8 width=4) (actual time=0.022..0.022 rows=0 loops=23)"
"              Index Cond: (material_id = sm.id)"
"              Filter: ((tag_id + 0) = 602)"
"              Rows Removed by Filter: 18"
"Planning time: 0.808 ms"
"Execution time: 0.914 ms"



А если поставить limit 10, то уже идет по другому плану
...
Рейтинг: 0 / 0
17.10.2016, 14:53
| Ответить | Цитировать | Написать  
Тяжелый запрос к связанным таблицам
    #39328256
qwwq
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
qwwq
Участник
maxxstorm,

Ну вот -- 23 лупа на попадание в фильтр, как ожидалось. т.е. бороться за такой план стоит

можно конечно угробище родить в стиле а-ля лузскан ( "with recursive " -- вот это вот всё), но лучше енейблами запинать.
...
Рейтинг: 0 / 0
17.10.2016, 15:03
| Ответить | Цитировать | Написать  
Тяжелый запрос к связанным таблицам
    #39328268
qwwq
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
qwwq
Участник
qwwqНу вот -- 23 лупа на попадание в фильтр
что--то не то посмотрел :
Код: sql
1.
Rows Removed by Filter: 18"


--- а, кажется все правильно. 23 лупа вдоль времени, а в нутре еще фильтр по тагу сработал на 18--й раз. Был бы составной -- было бы в этот раз в 18 раз дешевле.
...
Рейтинг: 0 / 0
17.10.2016, 15:08
| Ответить | Цитировать | Написать  
Тяжелый запрос к связанным таблицам
    #39328310
Фотография vyegorov
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
vyegorov
Участник
maxxstormПоказывает вот что:
Код: sql
1.
2.
3.
4.
5.
6.
7.
8.
9.
10.
11.
"Aggregate  (cost=630038.79..630038.80 rows=1 width=4) (actual time=84728.395..84728.395 rows=1 loops=1)"
"  Buffers: shared hit=4 read=271373 written=38"
"  ->  Bitmap Heap Scan on san_materialdata u0  (cost=4615.91..629422.23 rows=246625 width=4) (actual time=2494.228..84496.662 rows=528156 loops=1)"
"        Recheck Cond: (tag_id = 602)"
"        Heap Blocks: exact=269804"
"        Buffers: shared hit=4 read=271373 written=38"
"        ->  Bitmap Index Scan on san_materialdata_76f094bc  (cost=0.00..4554.26 rows=246625 width=0) (actual time=2349.480..2349.480 rows=543070 loops=1)"
"              Index Cond: (tag_id = 602)"
"              Buffers: shared hit=4 read=1569"
"Planning time: 0.187 ms"
"Execution time: 84728.476 ms"



Я так понимаю, что 271373 записи уже были в буфере?
Это не записи, это блоки, каждый по 8192 байта.
Запрос поднимает слишком много холодных данных с дисков. Вам его следует переписать -- если для `LIMIT 1` запрос отрабатывает быстро, loose index scan может помочь.
...
Рейтинг: 0 / 0
17.10.2016, 15:39
| Ответить | Цитировать | Написать  
Тяжелый запрос к связанным таблицам
    #39328346
maxxstorm
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
maxxstorm
Участник
qwwqmaxxstorm,

Ну вот -- 23 лупа на попадание в фильтр, как ожидалось. т.е. бороться за такой план стоит

можно конечно угробище родить в стиле а-ля лузскан ( "with recursive " -- вот это вот всё), но лучше енейблами запинать.

Я все равно не понимаю, почему планы разные для разных лимитов?
...
Рейтинг: 0 / 0
17.10.2016, 16:03
| Ответить | Цитировать | Написать  
Тяжелый запрос к связанным таблицам
    #39328426
qwwq
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
qwwq
Участник
maxxstormqwwqmaxxstorm,

Ну вот -- 23 лупа на попадание в фильтр, как ожидалось. т.е. бороться за такой план стоит

можно конечно угробище родить в стиле а-ля лузскан ( "with recursive " -- вот это вот всё), но лучше енейблами запинать.

Я все равно не понимаю, почему планы разные для разных лимитов?

патамушта "вдоль индекса" заканчивает чтение тогда, когда набирается LIMIT (такой унутре там луп с выходом по условию лимита) , и оценка цены (cost) растёт линейно с ограничением в лимите. а план для хеш-а вычитывает всё (что нужно соединять) всегда, и лимит берёт уже после соединения этого всего и сорта всего насоединенного (а вот "вдоль индекса" ресорт не нужен). т.е. его цена константна (для заданных статистик). вы можете подсобрать статистику наново, подкрутить косты (сделать цену рендом доступа не 4, а скажем 2), и напрямую залочить для вашего запроса енейблы хеш--траляля (см выше). но это грубо. тонко -- написать with recursive перебор, и добавить удешевляющий его индекс (тогда и хак с +0 убрать). есть ещё подозрение, что у вас табличка (одна из, или обе) может сильно апдейтиться -- тогда в цену войдет лишняя пробежка по дед-ровсам, например.
...
Рейтинг: 0 / 0
17.10.2016, 17:19
| Ответить | Цитировать | Написать  
Тяжелый запрос к связанным таблицам
    #39328429
maxxstorm
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
maxxstorm
Участник
qwwqmaxxstormпропущено...


Я все равно не понимаю, почему планы разные для разных лимитов?

патамушта "вдоль индекса" заканчивает чтение тогда, когда набирается LIMIT (такой унутре там луп с выходом по условию лимита) , и оценка цены (cost) растёт линейно с ограничением в лимите. а план для хеш-а вычитывает всё (что нужно соединять) всегда, и лимит берёт уже после соединения этого всего и сорта всего насоединенного (а вот "вдоль индекса" ресорт не нужен). т.е. его цена константна (для заданных статистик). вы можете подсобрать статистику наново, подкрутить косты (сделать цену рендом доступа не 4, а скажем 2), и напрямую залочить для вашего запроса енейблы хеш--траляля (см выше). но это грубо. тонко -- написать with recursive перебор, и добавить удешевляющий его индекс (тогда и хак с +0 убрать). есть ещё подозрение, что у вас табличка (одна из, или обе) может сильно апдейтиться -- тогда в цену войдет лишняя пробежка по дед-ровсам, например.

Андейтится да, довольно часто
...
Рейтинг: 0 / 0
17.10.2016, 17:22
| Ответить | Цитировать | Написать  
Тяжелый запрос к связанным таблицам
    #39329049
maxxstorm
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
maxxstorm
Участник
qwwqmaxxstormпропущено...


Я все равно не понимаю, почему планы разные для разных лимитов?

патамушта "вдоль индекса" заканчивает чтение тогда, когда набирается LIMIT (такой унутре там луп с выходом по условию лимита) , и оценка цены (cost) растёт линейно с ограничением в лимите. а план для хеш-а вычитывает всё (что нужно соединять) всегда, и лимит берёт уже после соединения этого всего и сорта всего насоединенного (а вот "вдоль индекса" ресорт не нужен). т.е. его цена константна (для заданных статистик). вы можете подсобрать статистику наново, подкрутить косты (сделать цену рендом доступа не 4, а скажем 2), и напрямую залочить для вашего запроса енейблы хеш--траляля (см выше). но это грубо. тонко -- написать with recursive перебор, и добавить удешевляющий его индекс (тогда и хак с +0 убрать). есть ещё подозрение, что у вас табличка (одна из, или обе) может сильно апдейтиться -- тогда в цену войдет лишняя пробежка по дед-ровсам, например.

Помогите написать with recursive. Не могу понять как
...
Рейтинг: 0 / 0
18.10.2016, 15:41
| Ответить | Цитировать | Написать  
Тяжелый запрос к связанным таблицам
    #39329097
qwwq
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
qwwq
Участник
maxxstormqwwqпропущено...


патамушта "вдоль индекса" заканчивает чтение тогда, когда набирается LIMIT (такой унутре там луп с выходом по условию лимита) , и оценка цены (cost) растёт линейно с ограничением в лимите. а план для хеш-а вычитывает всё (что нужно соединять) всегда, и лимит берёт уже после соединения этого всего и сорта всего насоединенного (а вот "вдоль индекса" ресорт не нужен). т.е. его цена константна (для заданных статистик). вы можете подсобрать статистику наново, подкрутить косты (сделать цену рендом доступа не 4, а скажем 2), и напрямую залочить для вашего запроса енейблы хеш--траляля (см выше). но это грубо. тонко -- написать with recursive перебор, и добавить удешевляющий его индекс (тогда и хак с +0 убрать). есть ещё подозрение, что у вас табличка (одна из, или обе) может сильно апдейтиться -- тогда в цену войдет лишняя пробежка по дед-ровсам, например.

Помогите написать with recursive. Не могу понять как
"по одной записи вдоль индекса"

а с каким лимитом план ещё такой ?
может быть запинаем пересбором статистики, не ?

а то предвижу геморрой, так как индекс по неуникальному полю, надо писать в ордер бай уникализирующий постфикс, а это поделие передовых студентов камнедробилки(тм) сразу свалится в сорт по всей выборке, придётся писать ручной проход (внутри той же конструкции) только до следующего значения вдоль неуникального индекса с ручной же сортировкой частичной выборки. или , для простоты, построить индекс (дата, ид) -- уникальный за счет постфикса "ид", и бегать с гораздо меньшим хенджобом вдоль него. т.е. на страничку А4 будет запрос, с кучкой трюков -- оно вам надо ?
...
Рейтинг: 0 / 0
18.10.2016, 16:14
| Ответить | Цитировать | Написать  
Тяжелый запрос к связанным таблицам
    #39329103
maxxstorm
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
maxxstorm
Участник
qwwqmaxxstormпропущено...


Помогите написать with recursive. Не могу понять как
"по одной записи вдоль индекса"

а с каким лимитом план ещё такой ?
может быть запинаем пересбором статистики, не ?

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

С limit 4 план такой же, а вот 5 уже тот старый.
Что значит запинаем статистикой? Авто analyze?
...
Рейтинг: 0 / 0
18.10.2016, 16:18
| Ответить | Цитировать | Написать  
Тяжелый запрос к связанным таблицам
    #39329533
Фотография Legushka
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Legushka
Участник
[quot qwwq]maxxstormхак с +0 qwwq, мог бы поподробнее рассказать или кинуть ссылку про этот хак: когда в каких случаях помогает/использовать? :)
...
Рейтинг: 0 / 0
19.10.2016, 09:09
| Ответить | Цитировать | Написать  
Тяжелый запрос к связанным таблицам
    #39329804
Фотография vyegorov
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
vyegorov
Участник
Legushka,

Индексируются значения аттрибутов (колонки таблиц или результаты функций).
Если в запросе использовать не сам аттрибут, а выражение с ним (включая явное/неявное приведение типов), то индекс неприменим.

В данном случае, если к индексированной числовой колонке прибавить 0, то значение колонки не измениться, но вот индекс уже применить нельзя, т.к. выборка делается не по атрибуту, а по результату выражения над ним.
...
Рейтинг: 0 / 0
19.10.2016, 13:35
| Ответить | Цитировать | Написать  
Тяжелый запрос к связанным таблицам
    #39329920
maxxstorm
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
maxxstorm
Участник
qwwqвспомнил, как запинывать без енейблов :

qwwqпропущено...

--поправил

и покажите план, даже если плохой . (если не дождетесь аналайза -- то просто план)

На другой бд(тестовой) выполняется дольше этот запрос:

Код: sql
1.
2.
3.
4.
5.
6.
7.
8.
9.
10.
"Limit  (cost=1.00..726.75 rows=1 width=12) (actual time=874.319..874.319 rows=1 loops=1)"
"  ->  Nested Loop Semi Join  (cost=1.00..3638929.55 rows=5014 width=12) (actual time=874.317..874.317 rows=1 loops=1)"
"        ->  Index Scan Backward using san_material_detection_datetime_cb0a0120_uniq on san_material sm  (cost=0.43..329401.46 rows=503240 width=12) (actual time=0.029..382.297 rows=237988 loops=1)"
"              Index Cond: ((detection_datetime >= '2016-10-10 00:00:00+03'::timestamp with time zone) AND (detection_datetime < '2016-10-15 00:00:00+03'::timestamp with time zone))"
"        ->  Index Scan using san_materialdata_eb4b9aaa on san_materialdata smd  (cost=0.57..42.34 rows=7 width=4) (actual time=0.002..0.002 rows=0 loops=237988)"
"              Index Cond: (material_id = sm.id)"
"              Filter: ((tag_id + 0) = 602)"
"              Rows Removed by Filter: 0"
"Planning time: 0.357 ms"
"Execution time: 874.363 ms"



С чем это может быть связано?
...
Рейтинг: 0 / 0
19.10.2016, 15:05
| Ответить | Цитировать | Написать  
Тяжелый запрос к связанным таблицам
    #39329953
maxxstorm
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
maxxstorm
Участник
Причем даже если поставить limit 50, то план запроса не меняется:

Код: sql
1.
2.
3.
4.
5.
6.
7.
8.
9.
10.
"Limit  (cost=1.00..70336.26 rows=50 width=12) (actual time=821.381..847.151 rows=50 loops=1)"
"  ->  Nested Loop Semi Join  (cost=1.00..7053221.31 rows=5014 width=12) (actual time=821.379..847.131 rows=50 loops=1)"
"        ->  Index Scan Backward using san_material_detection_datetime_cb0a0120_uniq on san_material sm  (cost=0.43..329571.41 rows=503498 width=12) (actual time=0.016..346.531 rows=239007 loops=1)"
"              Index Cond: ((detection_datetime >= '2016-10-10 00:00:00+03'::timestamp with time zone) AND (detection_datetime < '2016-10-15 00:00:00+03'::timestamp with time zone))"
"        ->  Index Scan using san_materialdata_eb4b9aaa on san_materialdata smd  (cost=0.57..38.72 rows=3 width=4) (actual time=0.002..0.002 rows=0 loops=239007)"
"              Index Cond: (material_id = sm.id)"
"              Filter: (tag_id = 602)"
"              Rows Removed by Filter: 0"
"Planning time: 0.358 ms"
"Execution time: 847.195 ms"
...
Рейтинг: 0 / 0
19.10.2016, 15:39
| Ответить | Цитировать | Написать  
Тяжелый запрос к связанным таблицам
    #39330005
maxxstorm
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
maxxstorm
Участник
qwwq
Код: sql
1.
2.
3.
4.
5.
6.
7.
8.
9.
10.
11.
12.
13.
SELECT  sm.id , sm.detection_datetime
FROM san_material sm
WHERE 
           sm.detection_datetime >='2016-10-10 00:00:00+03:00'
           AND sm.detection_datetime<'2016-10-15 00:00:00+03:00'
          AND EXISTS( SELECT 1 FROM 
                               FROM san_materialdata smd
                               WHERE 
                                    sm.id = smd.material_id
                                    AND smd.tag_id = 602
                                )
ORDER BY sm.detection_datetime DESC
LIMIT 50;


ожидаемые затраты - 50*5 произвольных доступов вдоль индекса + до ~20 [тагов в материале] -- вдоль проверяемого (полу--пессимистическая 50*5*20). если конечно вы оффсет потом не напишете.

неплохо пойдет индекс (tag_id,material_id) или симметричный. чтобы вот в ту 20--ку не упираться.
а лейтерал не нужен, вы из второй только фильтр дёргаете, его екзистс исполняет.

не сразу прочитал про оффсет, а он будет нужен потом( Так что backword не подходит походу
...
Рейтинг: 0 / 0
19.10.2016, 16:28
| Ответить | Цитировать | Написать  
Тяжелый запрос к связанным таблицам
    #39330065
qwwq
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
qwwq
Участник
maxxstormне сразу прочитал про оффсет, а он будет нужен потом( Так что backword не подходит походу

с этого бы и начинали
любителей пагинации оффсетом не лечим совсем
как и любителей отрезать хвост собакам тонкими слайсами
или искать следующий (от текущего "n--го") элемент в связанном списке -- перебором всех n+1 от начала, а не прямым переходом к next
т.е. принципиально неоперабельные клинические случаи

тогда ПЖ вам все правильно делает -- соединяет всё со всем, всё до конца сортирует, и , старательно слюнявя пальчик, отсчитывает в этой бескрайней простынке с нужного места по нужное место.
...
Рейтинг: 0 / 0
19.10.2016, 17:16
| Ответить | Цитировать | Написать  
Тяжелый запрос к связанным таблицам
    #39330075
maxxstorm
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
maxxstorm
Участник
qwwqmaxxstormне сразу прочитал про оффсет, а он будет нужен потом( Так что backword не подходит походу

с этого бы и начинали
любителей пагинации оффсетом не лечим совсем
как и любителей отрезать хвост собакам тонкими слайсами
или искать следующий (от текущего "n--го") элемент в связанном списке -- перебором всех n+1 от начала, а не прямым переходом к next
т.е. принципиально неоперабельные клинические случаи

тогда ПЖ вам все правильно делает -- соединяет всё со всем, всё до конца сортирует, и , старательно слюнявя пальчик, отсчитывает в этой бескрайней простынке с нужного места по нужное место.

А какие еще варианты для пагинации?
...
Рейтинг: 0 / 0
19.10.2016, 17:21
| Ответить | Цитировать | Написать  
Тяжелый запрос к связанным таблицам
    #39330088
qwwq
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
qwwq
Участник
maxxstorm ...
А какие еще варианты для пагинации?
от достигнутого [WHERE {unique_index_list}>{last_index_row} ORDER BY {unique_index_list} LIMIT $Page ]
или однопроходный -- "сразу всё" с сохранением нарезки границ

у всех есть свои модельные ограничения, [как и у многократного оффсета в быстроменяющейся обстановке]. но затрат сильно меньше
...
Рейтинг: 0 / 0
19.10.2016, 17:32
| Ответить | Цитировать | Написать  
Тяжелый запрос к связанным таблицам
    #39330155
Фотография vyegorov
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
vyegorov
Участник
maxxstormА какие еще варианты для пагинации?
http://www.slideshare.net/MarkusWinand/p2d2-pagination-done-the-postgresql-way
...
Рейтинг: 0 / 0
19.10.2016, 19:49
| Ответить | Цитировать | Написать  
Тяжелый запрос к связанным таблицам
    #39330349
maxxstorm
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
maxxstorm
Участник
qwwqmaxxstorm ...
А какие еще варианты для пагинации?
от достигнутого [WHERE {unique_index_list}>{last_index_row} ORDER BY {unique_index_list} LIMIT $Page ]
или однопроходный -- "сразу всё" с сохранением нарезки границ

у всех есть свои модельные ограничения, [как и у многократного оффсета в быстроменяющейся обстановке]. но затрат сильно меньше

Добавил индекс по tag_id, material_id. Теперь backword план работает при лимите 1000. Осталось переписать пагинацию
...
Рейтинг: 0 / 0
20.10.2016, 09:44
| Ответить | Цитировать | Написать  
Тяжелый запрос к связанным таблицам
    #39330397
maxxstorm
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
maxxstorm
Участник
Вот план:
Код: sql
1.
2.
3.
4.
5.
6.
7.
8.
9.
"Limit  (cost=1.00..27509.35 rows=50 width=12) (actual time=1517.514..1517.514 rows=0 loops=1)"
"  ->  Nested Loop Semi Join  (cost=1.00..2673262.14 rows=4859 width=12) (actual time=1517.514..1517.514 rows=0 loops=1)"
"        ->  Index Scan Backward using san_material_detection_datetime_cb0a0120_uniq on san_material sm  (cost=0.43..463394.21 rows=540016 width=12) (actual time=0.025..655.094 rows=491587 loops=1)"
"              Index Cond: ((detection_datetime >= '2016-10-10 00:00:00+03'::timestamp with time zone) AND (detection_datetime < '2016-10-15 00:00:00+03'::timestamp with time zone))"
"        ->  Index Only Scan using san_materialdata_mat_tag on san_materialdata smd  (cost=0.57..63.53 rows=18 width=4) (actual time=0.002..0.002 rows=0 loops=491587)"
"              Index Cond: ((tag_id = 613) AND (material_id = sm.id))"
"              Heap Fetches: 0"
"Planning time: 0.481 ms"
"Execution time: 1517.565 ms"



Еще вопрос, если я добавлю в exist еще одно поле(stop=false), мне надо будет сделать тройной индекс (tag_id, material_id, stop) или просто добавить к существующему (tag_id, material_id) одинарный индекс на поле stop?

Код: sql
1.
2.
3.
4.
5.
6.
7.
8.
9.
10.
11.
12.
13.
14.
SELECT  sm.id , sm.detection_datetime
FROM san_material sm
WHERE 
           sm.detection_datetime >='2016-10-10 00:00:00+03:00'
           AND sm.detection_datetime<'2016-10-15 00:00:00+03:00'
          AND EXISTS( SELECT 1  
                               FROM san_materialdata smd
                               WHERE 
                                    sm.id = smd.material_id
                                    AND smd.tag_id = 613 
                                    AND stop=false -- <--- новый фильтр
                                )
ORDER BY sm.detection_datetime DESC
LIMIT 50;
...
Рейтинг: 0 / 0
20.10.2016, 10:25
| Ответить | Цитировать | Написать  
Тяжелый запрос к связанным таблицам
    #39330829
maxxstorm
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
maxxstorm
Участник
После создания индекса (material_id, tag_id) выяснилось, что есть теги, которые редко встречаются и для них план запрос "вдоль индекса не подходит", зато старый(джойн) работает быстро. Для сравнения 2 разных плана для одного и того же запроса:

Код: sql
1.
2.
3.
4.
5.
6.
7.
8.
9.
10.
11.
12.
explain analyze SELECT  sm.id , sm.detection_datetime
FROM san_material sm
WHERE EXISTS( SELECT 1  
                               FROM san_materialdata smd
                               WHERE 
                                    sm.id = smd.material_id
                                    AND smd.tag_id = 597
                                    AND smd.stop=false
                                    ORDER BY material_id LIMIT 1
                                )
ORDER BY sm.detection_datetime DESC
LIMIT 5;



Код: sql
1.
2.
3.
4.
5.
6.
7.
8.
"Limit  (cost=1.00..3076.66 rows=5 width=12) (actual time=93.481..188.874 rows=5 loops=1)"
"  ->  Nested Loop Semi Join  (cost=1.00..39526592.69 rows=64257 width=12) (actual time=93.481..188.872 rows=5 loops=1)"
"        ->  Index Scan Backward using san_material_detection_datetime_cb0a0120_uniq on san_material sm  (cost=0.43..5828446.90 rows=7140822 width=12) (actual time=0.067..84.830 rows=57371 loops=1)"
"        ->  Index Scan using san_materialdata_mat_tag on san_materialdata smd  (cost=0.57..17.08 rows=4 width=4) (actual time=0.002..0.002 rows=0 loops=57371)"
"              Index Cond: ((tag_id = 597) AND (material_id = sm.id))"
"              Filter: (NOT stop)"
"Planning time: 0.421 ms"
"Execution time: 188.917 ms"



Код: sql
1.
2.
3.
4.
5.
6.
7.
8.
9.
10.
11.
12.
13.
14.
15.
16.
17.
"Limit  (cost=685867.90..685867.92 rows=5 width=12) (actual time=17.422..17.424 rows=5 loops=1)"
"  ->  Sort  (cost=685867.90..686027.60 rows=63880 width=12) (actual time=17.419..17.419 rows=5 loops=1)"
"        Sort Key: sm.detection_datetime DESC"
"        Sort Method: top-N heapsort  Memory: 25kB"
"        ->  Nested Loop  (cost=683536.81..684806.88 rows=63880 width=12) (actual time=4.862..16.925 rows=1071 loops=1)"
"              ->  HashAggregate  (cost=683536.38..683537.88 rows=150 width=4) (actual time=4.836..5.216 rows=1071 loops=1)"
"                    Group Key: smd.material_id"
"                    ->  Bitmap Heap Scan on san_materialdata smd  (cost=5043.83..682861.85 rows=269814 width=4) (actual time=0.671..3.977 rows=1711 loops=1)"
"                          Recheck Cond: (tag_id = 597)"
"                          Filter: (NOT stop)"
"                          Heap Blocks: exact=1077"
"                          ->  Bitmap Index Scan on san_materialdata_76f094bc  (cost=0.00..4976.38 rows=269841 width=0) (actual time=0.414..0.414 rows=1711 loops=1)"
"                                Index Cond: (tag_id = 597)"
"              ->  Index Scan using san_material_pkey on san_material sm  (cost=0.43..8.45 rows=1 width=12) (actual time=0.010..0.010 rows=1 loops=1071)"
"                    Index Cond: (id = smd.material_id)"
"Planning time: 0.634 ms"
"Execution time: 17.506 ms"
...
Рейтинг: 0 / 0
20.10.2016, 16:11
| Ответить | Цитировать | Написать  
Тяжелый запрос к связанным таблицам
    #39330945
qwwq
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
qwwq
Участник
maxxstormПосле создания индекса (material_id, tag_id) выяснилось, что есть теги, которые редко встречаются и для них план запрос "вдоль индекса не подходит"...
 

Код: sql
1.
2.
3.
4.
5.
6.
7.
  ->  Nested Loop Semi Join  (cost=1.00..39526592.69 rows=64257 width=12) (actual time=93.481..188.872 rows=5 loops=1)
        ->  Index Scan Backward using san_material_detection_datetime_cb0a0120_uniq on san_material sm  (cost=0.43..5828446.90 rows=7140822 width=12) (actual time=0.067..84.830 rows=57371 loops=1)
        ->  Index Scan using san_materialdata_mat_tag on san_materialdata smd  (cost=0.57..17.08 rows=4 width=4) (actual time=0.002..0.002 rows=0 loops=57371)
              Index Cond: ((tag_id = 597) AND (material_id = sm.id))
              Filter: (NOT stop)"
Planning time: 0.421 ms
Execution time: 188.917 ms




получается по 1000 пустых поисков на запись, а не по 20, как ожидалось. т.е. в 50 раз дороже ожидаемого.

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

второй вариант -- вести массивы тагов (tags int[]) материала в той же таблице, где даты. в новом поле.
пользоваться btree_gist смешанным индексом (data,tags) . Никаких джойнов -- все в одном. но при частых апдейтах это будет скорее головной болью, чем решением. хотя рискнуть можно.
все удобства от Бортунова и К. они это всё в свое время понаписали.


по поводу "стопа" -- надо смотреть , зачем он вам. есть много вариаций. условный индекс в т.ч. (WHERE NOT stop), а в составном оно д.б. первым номером. в конце оно бессмысленно.
...
Рейтинг: 0 / 0
20.10.2016, 18:01
| Ответить | Цитировать | Написать  
Тяжелый запрос к связанным таблицам
    #39334327
maxxstorm
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
maxxstorm
Участник
qwwq
получается по 1000 пустых поисков на запись, а не по 20, как ожидалось. т.е. в 50 раз дороже ожидаемого.

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

второй вариант -- вести массивы тагов (tags int[]) материала в той же таблице, где даты. в новом поле.
пользоваться btree_gist смешанным индексом (data,tags) . Никаких джойнов -- все в одном. но при частых апдейтах это будет скорее головной болью, чем решением. хотя рискнуть можно.
все удобства от Бортунова и К. они это всё в свое время понаписали.


по поводу "стопа" -- надо смотреть , зачем он вам. есть много вариаций. условный индекс в т.ч. (WHERE NOT stop), а в составном оно д.б. первым номером. в конце оно бессмысленно.

Спасибо за советы, вы очень помогли. Еще вопрос из той же области. Есть 2 БД одинаковой структуры, тест и бой.
Один и тот же запрос идет по разным планам(таблицы те же):
Код: sql
1.
2.
3.
SELECT "san_materialdata"."id" , material_id
FROM "san_materialdata" 
WHERE (material_id=5858304 and "san_materialdata"."tag_id" = 675)



Тест:
Код: sql
1.
2.
3.
4.
5.
6.
"Index Scan using san_materialdata_eb4b9aaa on san_materialdata  (cost=0.57..1418.62 rows=3 width=8) (actual time=0.049..0.053 rows=3 loops=1)"
"  Index Cond: (material_id = 5858304)"
"  Filter: (tag_id = 675)"
"  Rows Removed by Filter: 73"
"Planning time: 0.105 ms"
"Execution time: 0.074 ms"



Бой:
Код: sql
1.
2.
3.
4.
5.
6.
7.
8.
9.
"Bitmap Heap Scan on san_materialdata  (cost=5262.46..5278.50 rows=4 width=8) (actual time=1321.956..1321.956 rows=0 loops=1)"
"  Recheck Cond: ((material_id = 5858304) AND (tag_id = 675))"
"  ->  BitmapAnd  (cost=5262.46..5262.46 rows=4 width=0) (actual time=1321.950..1321.950 rows=0 loops=1)"
"        ->  Bitmap Index Scan on san_materialdata_eb4b9aaa  (cost=0.00..34.59 rows=1869 width=0) (actual time=0.035..0.035 rows=6 loops=1)"
"              Index Cond: (material_id = 5858304)"
"        ->  Bitmap Index Scan on san_materialdata_76f094bc  (cost=0.00..5227.62 rows=283607 width=0) (actual time=1318.741..1318.741 rows=890668 loops=1)"
"              Index Cond: (tag_id = 675)"
"Planning time: 0.183 ms"
"Execution time: 1322.008 ms"



В чем причина?
...
Рейтинг: 0 / 0
26.10.2016, 13:04
| Ответить | Цитировать | Написать  
Тяжелый запрос к связанным таблицам
    #39334362
Lonepsycho
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Lonepsycho
Участник
maxxstorm,

по всему, у вас данные разные. в тест "actual time=0.049..0.053 rows=3 loops=1", в боевом"actual time=1321.956..1321.956 rows=0 loops=1". с разными данными, их количеством, или распределением по таблице, можно получить разные планы.
...
Рейтинг: 0 / 0
26.10.2016, 13:35
| Ответить | Цитировать | Написать  
Тяжелый запрос к связанным таблицам
    #39334367
maxxstorm
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
maxxstorm
Участник
Lonepsychomaxxstorm,

по всему, у вас данные разные. в тест "actual time=0.049..0.053 rows=3 loops=1", в боевом"actual time=1321.956..1321.956 rows=0 loops=1". с разными данными, их количеством, или распределением по таблице, можно получить разные планы.

На боевом их больше в 1.5 раза. Я понимаю, что проблема решается составным индексом (tag_id, material_id), но все равно интересно знать почему так происходит.
...
Рейтинг: 0 / 0
26.10.2016, 13:39
| Ответить | Цитировать | Написать  
Тяжелый запрос к связанным таблицам
    #39334464
qwwq
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
qwwq
Участник
maxxstormqwwqполучается по 1000 пустых поисков на запись, а не по 20, как ожидалось. т.е. в 50 раз дороже ожидаемого.

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

второй вариант -- вести массивы тагов (tags int[]) материала в той же таблице, где даты. в новом поле.
пользоваться btree_gist смешанным индексом (data,tags) . Никаких джойнов -- все в одном. но при частых апдейтах это будет скорее головной болью, чем решением. хотя рискнуть можно.
все удобства от Бортунова и К. они это всё в свое время понаписали.


по поводу "стопа" -- надо смотреть , зачем он вам. есть много вариаций. условный индекс в т.ч. (WHERE NOT stop), а в составном оно д.б. первым номером. в конце оно бессмысленно.

Спасибо за советы, вы очень помогли. Еще вопрос из той же области. Есть 2 БД одинаковой структуры, тест и бой.
Один и тот же запрос идет по разным планам(таблицы те же):
Код: sql
1.
2.
3.
SELECT "san_materialdata"."id" , material_id
FROM "san_materialdata" 
WHERE (material_id=5858304 and "san_materialdata"."tag_id" = 675)



Тест:
Код: sql
1.
2.
3.
4.
5.
6.
"Index Scan using san_materialdata_eb4b9aaa on san_materialdata  (cost=0.57..1418.62 rows=3 width=8) (actual time=0.049..0.053 rows=3 loops=1)"
"  Index Cond: (material_id = 5858304)"
"  Filter: (tag_id = 675)"
"  Rows Removed by Filter: 73"
"Planning time: 0.105 ms"
"Execution time: 0.074 ms"



Бой:
Код: sql
1.
2.
3.
4.
5.
6.
7.
8.
9.
"Bitmap Heap Scan on san_materialdata  (cost=5262.46..5278.50 rows=4 width=8) (actual time=1321.956..1321.956 rows=0 loops=1)"
"  Recheck Cond: ((material_id = 5858304) AND (tag_id = 675))"
"  ->  BitmapAnd  (cost=5262.46..5262.46 rows=4 width=0) (actual time=1321.950..1321.950 rows=0 loops=1)"
"        ->  Bitmap Index Scan on san_materialdata_eb4b9aaa  (cost=0.00..34.59 rows=1869 width=0) (actual time=0.035..0.035 rows=6 loops=1)"
"              Index Cond: (material_id = 5858304)"
"        ->  Bitmap Index Scan on san_materialdata_76f094bc  (cost=0.00..5227.62 rows=283607 width=0) (actual time=1318.741..1318.741 rows=890668 loops=1)"
"              Index Cond: (tag_id = 675)"
"Planning time: 0.183 ms"
"Execution time: 1322.008 ms"



В чем причина?
причина в cost--based оптимизации и статистиках, на которые она опирается.
например почему то для заданного материал_ид оно ожидает 1869 строк, а находит 6 -- разница более чем в 2 порядка. как вот оно так ? вы давно статистику собирали ? или индекс нажрался мертвых строк ?

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

в пж слишком мало костовых констант -- видимо оптимайзер модельно очень дубовый. костами спихнуть с одного индексного поиска на другой тип индексного же поиска не удастся, думаю, хотя можно попробовать. (а енейблы сосут при 10^9 строк, т.к. "большая" константа пропадает на фоне).
...
Рейтинг: 0 / 0
26.10.2016, 15:00
| Ответить | Цитировать | Написать  
Тяжелый запрос к связанным таблицам
    #39334481
maxxstorm
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
maxxstorm
Участник
qwwqпричина в cost--based оптимизации и статистиках, на которые она опирается.
например почему то для заданного материал_ид оно ожидает 1869 строк, а находит 6 -- разница более чем в 2 порядка. как вот оно так ? вы давно статистику собирали ? или индекс нажрался мертвых строк ?

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

в пж слишком мало костовых констант -- видимо оптимайзер модельно очень дубовый. костами спихнуть с одного индексного поиска на другой тип индексного же поиска не удастся, думаю, хотя можно попробовать. (а енейблы сосут при 10^9 строк, т.к. "большая" константа пропадает на фоне).

Статистику собирал непосредственно перед запросом. Насчет мертвых строк сомневаюсь. Когда они появляются? При делетах?

Если выключить битмапскан, то план хороший:

Код: sql
1.
2.
3.
4.
5.
6.
"Index Scan using san_materialdata_eb4b9aaa on san_materialdata  (cost=0.57..6676.53 rows=4 width=8) (actual time=0.060..0.060 rows=0 loops=1)"
"  Index Cond: (material_id = 5858304)"
"  Filter: (tag_id = 675)"
"  Rows Removed by Filter: 6"
"Planning time: 0.177 ms"
"Execution time: 0.088 ms"
...
Рейтинг: 0 / 0
26.10.2016, 15:12
| Ответить | Цитировать | Написать  
Тяжелый запрос к связанным таблицам
    #39334600
qwwq
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
qwwq
Участник
maxxstormЕсли выключить битмапскан, то план хороший:

Код: sql
1.
2.
3.
4.
5.
6.
"Index Scan using san_materialdata_eb4b9aaa on san_materialdata  (cost=0.57..6676.53 rows=4 width=8) (actual time=0.060..0.060 rows=0 loops=1)"
"  Index Cond: (material_id = 5858304)"
"  Filter: (tag_id = 675)"
"  Rows Removed by Filter: 6"
"Planning time: 0.177 ms"
"Execution time: 0.088 ms"

вот у вас оценка пессимистическая "6676.53" при ожидании 4 строк. она больше пессимистической оценки для битмапа (cost=5262.46..5278.50). а вот откуда она такая большая ?
я бы с потолка насчитал 4 произвольных ценой (если не крутили) по 4 + найти нужную страничку индексов -- чтонть пессимистически странички 4 -- максимум 20--32 по произвольным набегает. откуда это вот угрёбище нагребает 6676.53 -- тайна великая есть. могабыть в статистике что ?
и зачем сравнивает только писси-мяссиские велчичины. "и вот всё у них так."(сс)
...
Рейтинг: 0 / 0
26.10.2016, 16:24
| Ответить | Цитировать | Написать  
Тяжелый запрос к связанным таблицам
    #39334608
qwwq
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
qwwq
Участник
qwwq,

всё время путаюсь. диапазон костов -- это оценка затрат на вывод первой и последней строки соответственно. из 4. а не пессимистич/оптимистич.

см https://habrahabr.ru/post/203320/

вот откуда оно 6000 набрало -- хз
...
Рейтинг: 0 / 0
26.10.2016, 16:34
| Ответить | Цитировать | Написать  
Тяжелый запрос к связанным таблицам
    #39334630
Фотография vyegorov
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
vyegorov
Участник
qwwq,

Оценка строится на `%_cost` параметрах. Основная доля там — обращение к страницам, для индексов это `random_page_cost`.
Учитвыая, что ожидается всего 4 записи и также то, что индекс читается не весь, а "по дереву", то оценка в 6600 довольна высока.
Возможно (я не разбирался в оценке индексных доступов, только последовательных) сюда закладывается также и доступ к 4-м (в худшем случае) блокам из основной таблицы. Но всё равно — это очень много для 4-х записей.

А что выведет `EXPLAIN (analyze, buffers)`?
...
Рейтинг: 0 / 0
26.10.2016, 16:46
| Ответить | Цитировать | Написать  
Тяжелый запрос к связанным таблицам
    #39334633
maxxstorm
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
maxxstorm
Участник
vyegorovqwwq,

Оценка строится на `%_cost` параметрах. Основная доля там — обращение к страницам, для индексов это `random_page_cost`.
Учитвыая, что ожидается всего 4 записи и также то, что индекс читается не весь, а "по дереву", то оценка в 6600 довольна высока.
Возможно (я не разбирался в оценке индексных доступов, только последовательных) сюда закладывается также и доступ к 4-м (в худшем случае) блокам из основной таблицы. Но всё равно — это очень много для 4-х записей.

А что выведет `EXPLAIN (analyze, buffers)`?

Код: sql
1.
2.
3.
4.
5.
6.
7.
8.
9.
10.
11.
12.
13.
"Bitmap Heap Scan on san_materialdata  (cost=5284.17..5300.21 rows=4 width=12) (actual time=1190.771..1190.771 rows=0 loops=1)"
"  Recheck Cond: ((material_id = 5858304) AND (tag_id = 600))"
"  Buffers: shared hit=3085"
"  ->  BitmapAnd  (cost=5284.17..5284.17 rows=4 width=0) (actual time=1190.765..1190.765 rows=0 loops=1)"
"        Buffers: shared hit=3085"
"        ->  Bitmap Index Scan on san_materialdata_eb4b9aaa  (cost=0.00..34.65 rows=1878 width=0) (actual time=0.034..0.034 rows=6 loops=1)"
"              Index Cond: (material_id = 5858304)"
"              Buffers: shared hit=4"
"        ->  Bitmap Index Scan on san_materialdata_76f094bc  (cost=0.00..5249.27 rows=284893 width=0) (actual time=1188.350..1188.350 rows=1045140 loops=1)"
"              Index Cond: (tag_id = 600)"
"              Buffers: shared hit=3081"
"Planning time: 0.187 ms"
"Execution time: 1190.824 ms"
...
Рейтинг: 0 / 0
26.10.2016, 16:50
| Ответить | Цитировать | Написать  
Тяжелый запрос к связанным таблицам
    #39334660
maxxstorm
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
maxxstorm
Участник
Если без скана, то

Код: sql
1.
2.
3.
4.
5.
6.
7.
"Index Scan using san_materialdata_eb4b9aaa on san_materialdata  (cost=0.57..6676.53 rows=4 width=8) (actual time=0.041..0.041 rows=0 loops=1)"
"  Index Cond: (material_id = 5858304)"
"  Filter: (tag_id = 675)"
"  Rows Removed by Filter: 6"
"  Buffers: shared hit=5"
"Planning time: 0.152 ms"
"Execution time: 0.069 ms"
...
Рейтинг: 0 / 0
26.10.2016, 17:13
| Ответить | Цитировать | Написать  
Тяжелый запрос к связанным таблицам
    #39334727
qwwq
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
qwwq
Участник
vyegorovqwwq,

Оценка строится на `%_cost` параметрах. Основная доля там — обращение к страницам, для индексов это `random_page_cost`.
Учитвыая, что ожидается всего 4 записи и также то, что индекс читается не весь, а "по дереву", то оценка в 6600 довольна высока.
Возможно (я не разбирался в оценке индексных доступов, только последовательных) сюда закладывается также и доступ к 4-м (в худшем случае) блокам из основной таблицы. Но всё равно — это очень много для 4-х записей.

А что выведет `EXPLAIN (analyze, buffers)`?
я вот думаю, что ожидается 4 записи после фильтра. м.б. до фильтра ожидается какое--то безумное число ? надо бы пошаманить -- посмотреть что в pg_statistic валяется и как это м.б. связано с той цифирью, которую мы видим как результат накрутки костов для 4--х доступов. думаю -- все таки бага где--то. или мы какой--то инфы о модели не знаем (стотысячмиллионов апдейтов, например)
...
Рейтинг: 0 / 0
26.10.2016, 18:33
| Ответить | Цитировать | Написать  
Тяжелый запрос к связанным таблицам
    #39334734
qwwq
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
qwwq
Участник
о, слайды:

http://pgday.ru/files/papers/17/pgday2015.using-postgresql-statistics.pdf
...
Рейтинг: 0 / 0
26.10.2016, 18:42
| Ответить | Цитировать | Написать  
Тяжелый запрос к связанным таблицам
    #39334897
Фотография Maxim Boguk
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Maxim Boguk
Участник
maxxstormvyegorovqwwq,

Оценка строится на `%_cost` параметрах. Основная доля там — обращение к страницам, для индексов это `random_page_cost`.
Учитвыая, что ожидается всего 4 записи и также то, что индекс читается не весь, а "по дереву", то оценка в 6600 довольна высока.
Возможно (я не разбирался в оценке индексных доступов, только последовательных) сюда закладывается также и доступ к 4-м (в худшем случае) блокам из основной таблицы. Но всё равно — это очень много для 4-х записей.

А что выведет `EXPLAIN (analyze, buffers)`?

Код: sql
1.
2.
3.
4.
5.
6.
7.
8.
9.
10.
11.
12.
13.
"Bitmap Heap Scan on san_materialdata  (cost=5284.17..5300.21 rows=4 width=12) (actual time=1190.771..1190.771 rows=0 loops=1)"
"  Recheck Cond: ((material_id = 5858304) AND (tag_id = 600))"
"  Buffers: shared hit=3085"
"  ->  BitmapAnd  (cost=5284.17..5284.17 rows=4 width=0) (actual time=1190.765..1190.765 rows=0 loops=1)"
"        Buffers: shared hit=3085"
"        ->  Bitmap Index Scan on san_materialdata_eb4b9aaa  (cost=0.00..34.65 rows=1878 width=0) (actual time=0.034..0.034 rows=6 loops=1)"
"              Index Cond: (material_id = 5858304)"
"              Buffers: shared hit=4"
"        ->  Bitmap Index Scan on san_materialdata_76f094bc  (cost=0.00..5249.27 rows=284893 width=0) (actual time=1188.350..1188.350 rows=1045140 loops=1)"
"              Index Cond: (tag_id = 600)"
"              Buffers: shared hit=3081"
"Planning time: 0.187 ms"
"Execution time: 1190.824 ms"



А у вас случайно
1)default_statistics_target на тесте и в бою не отличается?
2)случайно не выставлен руками n_distinct на san_materialdata_eb4b9aaa.material_id в тесте или другое значение statistic?

Что говорит
Код: plsql
1.
explain analyze select * from san_materialdata_eb4b9aaa where material_id = 5858304

в бою и в тесте?
И меняется ли что то после ручного выполнения analyze san_materialdata_eb4b9aaa; в бою и в тесте в результатах explain analyze?

Вероятнее всего не хватает статистки в бою адекватной и надо будет или statistics поднимать для поля (или по всей базе) или n_distinct ему руками прописывать.

--
Maxim Boguk
www.postgresql-consulting.ru
...
Рейтинг: 0 / 0
27.10.2016, 03:31
| Ответить | Цитировать | Написать  
Тяжелый запрос к связанным таблицам
    #39334948
maxxstorm
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
maxxstorm
Участник
Maxim BogukА у вас случайно
1)default_statistics_target на тесте и в бою не отличается?
2)случайно не выставлен руками n_distinct на san_materialdata_eb4b9aaa.material_id в тесте или другое значение statistic?

Что говорит
Код: plsql
1.
explain analyze select * from san_materialdata_eb4b9aaa where material_id = 5858304

в бою и в тесте?
И меняется ли что то после ручного выполнения analyze san_materialdata_eb4b9aaa; в бою и в тесте в результатах explain analyze?

Вероятнее всего не хватает статистки в бою адекватной и надо будет или statistics поднимать для поля (или по всей базе) или n_distinct ему руками прописывать.

--
Maxim Boguk
www.postgresql-consulting.ru

1) default_statistics_target одинаковая, равна 100
2) n_distinct руками не ставился, но он различается, на бою 63880, на тесте 47247
3) Для explain analyze select * from san_materialdata_eb4b9aaa where material_id = 5858304 планы тоже различаются:

Бой:
Код: sql
1.
2.
3.
4.
5.
6.
7.
"Bitmap Heap Scan on san_materialdata  (cost=35.23..7420.48 rows=1892 width=62) (actual time=0.145..0.147 rows=6 loops=1)"
"  Recheck Cond: (material_id = 5858304)"
"  Heap Blocks: exact=1"
"  ->  Bitmap Index Scan on san_materialdata_eb4b9aaa  (cost=0.00..34.76 rows=1892 width=0) (actual time=0.104..0.104 rows=6 loops=1)"
"        Index Cond: (material_id = 5858304)"
"Planning time: 0.545 ms"
"Execution time: 0.171 ms"



Тест:
Код: sql
1.
2.
3.
4.
"Index Scan using san_materialdata_eb4b9aaa on san_materialdata  (cost=0.57..1185.58 rows=1541 width=50) (actual time=0.106..0.144 rows=76 loops=1)"
"  Index Cond: (material_id = 5858304)"
"Planning time: 0.500 ms"
"Execution time: 0.182 ms"



Однако, после сбора статистики, план на бою становится таким же как на тесте, что нельзя сказать о первоначальном запросе.
...
Рейтинг: 0 / 0
27.10.2016, 09:12
| Ответить | Цитировать | Написать  
Тяжелый запрос к связанным таблицам
    #39335147
Alexius
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Alexius
Участник
Код: sql
1.
2.
3.
4.
5.
6.
7.
8.
9.
10.
11.
12.
13.
14.
15.
16.
17.
18.
19.
20.
21.
"Bitmap Heap Scan on san_materialdata  (cost=5284.17..5300.21 rows=4 width=12) (actual time=1190.771..1190.771 rows=0 loops=1)"
"  Recheck Cond: ((material_id = 5858304) AND (tag_id = 600))"
"  Buffers: shared hit=3085"
"  ->  BitmapAnd  (cost=5284.17..5284.17 rows=4 width=0) (actual time=1190.765..1190.765 rows=0 loops=1)"
"        Buffers: shared hit=3085"
"        ->  Bitmap Index Scan on san_materialdata_eb4b9aaa  (cost=0.00..34.65 rows=1878 width=0) (actual time=0.034..0.034 rows=6 loops=1)"
"              Index Cond: (material_id = 5858304)"
"              Buffers: shared hit=4"
"        ->  Bitmap Index Scan on san_materialdata_76f094bc  (cost=0.00..5249.27 rows=284893 width=0) (actual time=1188.350..1188.350 rows=1045140 loops=1)"
"              Index Cond: (tag_id = 600)"
"              Buffers: shared hit=3081"
"Planning time: 0.187 ms"
"Execution time: 1190.824 ms"

"Index Scan using san_materialdata_eb4b9aaa on san_materialdata  (cost=0.57..6676.53 rows=4 width=8) (actual time=0.041..0.041 rows=0 loops=1)"
"  Index Cond: (material_id = 5858304)"
"  Filter: (tag_id = 675)"
"  Rows Removed by Filter: 6"
"  Buffers: shared hit=5"
"Planning time: 0.152 ms"
"Execution time: 0.069 ms"




планировщик считает кост не для 4 строк, а для 1878, поэтому такая оценка большая. что выдает запрос

Код: sql
1.
select null_frac, most_common_vals, most_common_freqs from pg_stats where tablename = 'san_materialdata' and attname = 'material_id';


? скорей всего надо будет добавить статистики по этому полю, чтобы не было ошибки на 2 порядка:
Код: sql
1.
2.
alter table san_materialdata alter column material_id set statistics 1000;
analyze san_materialdata;
...
Рейтинг: 0 / 0
27.10.2016, 11:32
| Ответить | Цитировать | Написать  
Тяжелый запрос к связанным таблицам
    #39335158
maxxstorm
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
maxxstorm
Участник
Alexius
планировщик считает кост не для 4 строк, а для 1878, поэтому такая оценка большая. что выдает запрос

Код: sql
1.
select null_frac, most_common_vals, most_common_freqs from pg_stats where tablename = 'san_materialdata' and attname = 'material_id';


? скорей всего надо будет добавить статистики по этому полю, чтобы не было ошибки на 2 порядка:
Код: sql
1.
2.
alter table san_materialdata alter column material_id set statistics 1000;
analyze san_materialdata;



Вот что выдает:
Код: sql
1.
2.
3.
4.
5.
6.
7.
8.
9.
10.
11.
12.
13.
14.
15.
16.
17.
         0 | {1192860,1130697,705790,500500,599105,1153816,1767790,1907922,533945,1019058,1279798,2098764,2786912,
4065076,4442067,5014040,442362,831508,861475,929361,953667,1111350,1306808,1525637,1590370,1634612,1648336,1722187,
1954306,2043576,2117452,2318435,2464641,2501635,2531126,2807001,2860650,2872008,2967316,2997551,3017516,3215033,
3218870,3385712,3487426,3557502,3610889,3669507,3773322,3846947,4008954,4056740,4151981,4176495,4178926,4263691,
4264794,4366766,5388776,6677228,383076,395561,426592,589781,703157,715245,737659,762380,791073,798228,822590,
911859,917936,945688,984803,1119393,1209657,1308680,1325768,1329760,1358209,1389170,1396118,1417715,1479611,
1543218,1568069,1577566,1666076,1689851,1701960,1703076,1712447,1729870,1735242,1759967,1790674,1810619,1821964,1833650} | 
{0.000566667,0.000533333,0.0004,0.000233333,0.000233333,0.000233333,0.000233333,0.000233333,0.0002,0.0002,0.0002,
0.0002,0.0002,0.0002,0.0002,0.0002,0.000166667,0.000166667,0.000166667,0.000166667,0.000166667,0.000166667,0.000166667,
0.000166667,0.000166667,0.000166667,0.000166667,0.000166667,0.000166667,0.000166667,0.000166667,0.000166667,0.000166667,
0.000166667,0.000166667,0.000166667,0.000166667,0.000166667,0.000166667,0.000166667,0.000166667,0.000166667,0.000166667,
0.000166667,0.000166667,0.000166667,0.000166667,0.000166667,0.000166667,0.000166667,0.000166667,0.000166667,0.000166667,
0.000166667,0.000166667,0.000166667,0.000166667,0.000166667,0.000166667,0.000166667,0.000133333,0.000133333,0.000133333,
0.000133333,0.000133333,0.000133333,0.000133333,0.000133333,0.000133333,0.000133333,0.000133333,0.000133333,0.000133333,
0.000133333,0.000133333,0.000133333,0.000133333,0.000133333,0.000133333,0.000133333,0.000133333,0.000133333,0.000133333,
0.000133333,0.000133333,0.000133333,0.000133333,0.000133333,0.000133333,0.000133333,0.000133333,0.000133333,0.000133333,
0.000133333,0.000133333,0.000133333,0.000133333,0.000133333,0.000133333,0.000133333}
...
Рейтинг: 0 / 0
27.10.2016, 11:37
| Ответить | Цитировать | Написать  
Тяжелый запрос к связанным таблицам
    #39335166
maxxstorm
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
maxxstorm
Участник
Alexiusalter table san_materialdata alter column material_id set statistics 1000;
analyze san_materialdata;

[/src]

Сделал, план запроса стал хорошим:

Код: sql
1.
2.
3.
4.
5.
6.
7.
"Index Scan using san_materialdata_eb4b9aaa on san_materialdata  (cost=0.57..794.32 rows=1 width=8) (actual time=0.013..0.013 rows=0 loops=1)"
"  Index Cond: (material_id = 5858304)"
"  Filter: (tag_id = 630)"
"  Rows Removed by Filter: 6"
"  Buffers: shared hit=5"
"Planning time: 0.085 ms"
"Execution time: 0.025 ms"
...
Рейтинг: 0 / 0
27.10.2016, 11:43
| Ответить | Цитировать | Написать  
Тяжелый запрос к связанным таблицам
    #39343503
maxxstorm
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
maxxstorm
Участник
Еще похожая проблема с запросом
Код: sql
1.
2.
3.
SELECT COUNT(*) AS "__count" FROM "san_material" 
WHERE ("san_material"."detection_datetime" BETWEEN '2016-11-08T00:00:00+03:00'::timestamptz AND '2016-11-08T23:59:59.999999+03:00'::timestamptz 
AND "san_material"."id" IN (SELECT U0."material_id" FROM "san_materialdata" U0 WHERE (U0."tag_id" IN (602) AND U0."stop" = false)))



Выдает план:
Код: sql
1.
2.
3.
4.
5.
6.
7.
8.
9.
10.
11.
12.
"Aggregate  (cost=680181.83..680181.84 rows=1 width=0)"
"  ->  Nested Loop  (cost=671036.96..680176.53 rows=2120 width=0)"
"        ->  HashAggregate  (cost=671036.53..671047.42 rows=1089 width=4)"
"              Group Key: u0.material_id"
"              ->  Bitmap Heap Scan on san_materialdata u0  (cost=4833.89..670420.71 rows=246329 width=4)"
"                    Recheck Cond: (tag_id = 602)"
"                    Filter: (NOT stop)"
"                    ->  Bitmap Index Scan on san_materialdata_76f094bc  (cost=0.00..4772.31 rows=246365 width=0)"
"                          Index Cond: (tag_id = 602)"
"        ->  Index Scan using san_material_pkey on san_material  (cost=0.43..8.37 rows=1 width=4)"
"              Index Cond: (id = u0.material_id)"
"              Filter: ((detection_datetime >= '2016-11-08 00:00:00+03'::timestamp with time zone) AND (detection_datetime <= '2016-11-08 23:59:59.999999+03'::timestamp with time zone))"



Однако, если взять другой тег
Код: sql
1.
2.
3.
SELECT COUNT(*) AS "__count" FROM "san_material" 
WHERE ("san_material"."detection_datetime" BETWEEN '2016-11-08T00:00:00+03:00'::timestamptz AND '2016-11-08T23:59:59.999999+03:00'::timestamptz 
AND "san_material"."id" IN (SELECT U0."material_id" FROM "san_materialdata" U0 WHERE (U0."tag_id" IN (660) AND U0."stop" = false)))



или меньший интервал
Код: sql
1.
2.
3.
explain SELECT COUNT(*) AS "__count" FROM "san_material" 
WHERE ("san_material"."detection_datetime" BETWEEN '2016-11-08T00:00:00+03:00'::timestamptz AND '2016-11-08T12:59:59.999999+03:00'::timestamptz 
AND "san_material"."id" IN (SELECT U0."material_id" FROM "san_materialdata" U0 WHERE (U0."tag_id" IN (602) AND U0."stop" = false)))



то план тот, что нужно:
Код: sql
1.
2.
3.
4.
5.
6.
7.
"Aggregate  (cost=683252.49..683252.50 rows=1 width=0)"
"  ->  Nested Loop Semi Join  (cost=1.00..683247.65 rows=1937 width=0)"
"        ->  Index Scan using san_material_detection_datetime_cb0a0120_uniq on san_material  (cost=0.43..43611.92 rows=79027 width=4)"
"              Index Cond: ((detection_datetime >= '2016-11-08 00:00:00+03'::timestamp with time zone) AND (detection_datetime <= '2016-11-08 12:59:59.999999+03'::timestamp with time zone))"
"        ->  Index Scan using san_materialdata_material_id_32150950_idx on san_materialdata u0  (cost=0.57..8.09 rows=1 width=4)"
"              Index Cond: ((material_id = san_material.id) AND (tag_id = 602))"
"              Filter: (NOT stop)"



Насколкьо страшно будет, если отключить enable_bitmapscan?
Как решить без отключения?
...
Рейтинг: 0 / 0
08.11.2016, 14:23
| Ответить | Цитировать | Написать  
Тяжелый запрос к связанным таблицам
    #39344363
maxxstorm
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
maxxstorm
Участник
А вот для другого тега план такой же, но ожидаемое количество записей на порядок меньше реального:

Код: sql
1.
2.
3.
4.
5.
6.
7.
8.
9.
10.
11.
12.
13.
14.
15.
"Aggregate  (cost=700708.29..700708.30 rows=1 width=4) (actual time=148.255..148.255 rows=1 loops=1)"
"  ->  Nested Loop  (cost=691124.49..700692.41 rows=6352 width=4) (actual time=47.021..148.238 rows=64 loops=1)"
"        ->  HashAggregate  (cost=691124.06..691135.46 rows=1140 width=4) (actual time=46.263..53.141 rows=25458 loops=1)"
"              Group Key: u0.material_id"
"              ->  Bitmap Heap Scan on san_materialdata u0  (cost=5041.54..690483.74 rows=256125 width=4) (actual time=7.506..34.393 rows=35858 loops=1)"
"                    Recheck Cond: (tag_id = 630)"
"                    Heap Blocks: exact=17356"
"                    ->  Bitmap Index Scan on san_materialdata_76f094bc  (cost=0.00..4977.51 rows=256125 width=0) (actual time=4.567..4.567 rows=35858 loops=1)"
"                          Index Cond: (tag_id = 630)"
"        ->  Index Scan using san_material_pkey on san_material  (cost=0.43..8.37 rows=1 width=4) (actual time=0.004..0.004 rows=0 loops=25458)"
"              Index Cond: (id = u0.material_id)"
"              Filter: ((detection_datetime >= '2016-11-07 00:00:00+03'::timestamp with time zone) AND (detection_datetime <= '2016-11-09 23:59:59.999999+03'::timestamp with time zone))"
"              Rows Removed by Filter: 1"
"Planning time: 0.396 ms"
"Execution time: 148.311 ms"
...
Рейтинг: 0 / 0
09.11.2016, 17:20
| Ответить | Цитировать | Написать  
Тяжелый запрос к связанным таблицам
    #39344398
Alexius
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Alexius
Участник
maxxstorm,

я подозреваю что нужно аналогично добавить собираемой статистики по полю tag_id, раз планер ошибается в оценке. по приведенным планам правда не понятно, они все без analyze, кроме последнего.

для тестов отключать enable_* параметры очень полезно чтобы перебрать планы для фиксированного запроса и разобраться. на практике это обычно крайняя мера, когда по-другому совсем никак, что бывает редко.
...
Рейтинг: 0 / 0
09.11.2016, 18:10
| Ответить | Цитировать | Написать  
Тяжелый запрос к связанным таблицам
    #39344649
maxxstorm
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
maxxstorm
Участник
Alexiusmaxxstorm,

я подозреваю что нужно аналогично добавить собираемой статистики по полю tag_id, раз планер ошибается в оценке. по приведенным планам правда не понятно, они все без analyze, кроме последнего.

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

Добавил статистики по полю tag_ig также до 1000.
Отключил enable_bitmapscan. Вот что получается:
Код: sql
1.
2.
3.
4.
5.
6.
7.
8.
9.
"Aggregate  (cost=960100.02..960100.03 rows=1 width=4)"
"  ->  Nested Loop  (cost=950517.60..960081.03 rows=7593 width=4)"
"        ->  HashAggregate  (cost=950517.16..950528.55 rows=1139 width=4)"
"              Group Key: u0.material_id"
"              ->  Index Scan using san_materialdata_76f094bc on san_materialdata u0  (cost=0.57..949882.85 rows=253723 width=4)"
"                    Index Cond: (tag_id = 602)"
"        ->  Index Scan using san_material_pkey on san_material  (cost=0.43..8.38 rows=1 width=4)"
"              Index Cond: (id = u0.material_id)"
"              Filter: ((detection_datetime >= '2016-11-02 00:00:00+03'::timestamp with time zone) AND (detection_datetime <= '2016-11-03 23:59:59.999999+03'::timestamp with time zone))"



Почему он пытается агрегировать?
...
Рейтинг: 0 / 0
10.11.2016, 11:20
| Ответить | Цитировать | Написать  
Тяжелый запрос к связанным таблицам
    #39344651
maxxstorm
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
maxxstorm
Участник
Отключаю enable_hashagg, получается еще интереснее:
Код: sql
1.
2.
3.
4.
5.
6.
7.
8.
9.
10.
"Aggregate  (cost=994835.97..994835.98 rows=1 width=4)"
"  ->  Nested Loop  (cost=983980.20..994816.75 rows=7688 width=4)"
"        ->  Unique  (cost=983979.76..985264.26 rows=1139 width=4)"
"              ->  Sort  (cost=983979.76..984622.01 rows=256900 width=4)"
"                    Sort Key: u0.material_id"
"                    ->  Index Scan using san_materialdata_76f094bc on san_materialdata u0  (cost=0.57..960896.21 rows=256900 width=4)"
"                          Index Cond: (tag_id = 602)"
"        ->  Index Scan using san_material_pkey on san_material  (cost=0.43..8.38 rows=1 width=4)"
"              Index Cond: (id = u0.material_id)"
"              Filter: ((detection_datetime >= '2016-11-02 00:00:00+03'::timestamp with time zone) AND (detection_datetime <= '2016-11-03 23:59:59.999999+03'::timestamp with time zone))"
...
Рейтинг: 0 / 0
10.11.2016, 11:21
| Ответить | Цитировать | Написать  
Тяжелый запрос к связанным таблицам
    #39344685
Фотография vyegorov
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
vyegorov
Участник
maxxstorm,

Надо же не просто план смотреть, а время, сколько занимает исполнение запроса с таким планом, и кол-во буферов.

EXPLAIN (analyze, buffers)
...
Рейтинг: 0 / 0
10.11.2016, 12:02
| Ответить | Цитировать | Написать  
Тяжелый запрос к связанным таблицам
    #39344745
qwwq
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
qwwq
Участник
maxxstorm
Почему он пытается агрегировать?
патамушта каунт -- агрегатная ф--я.



приведите сам запрос еще раз, а то последнее :

Код: plaintext
Sort >  Unique > Aggregate

наводят на мысли о наличии count(distinct ...)
...
Рейтинг: 0 / 0
10.11.2016, 12:49
| Ответить | Цитировать | Написать  
Тяжелый запрос к связанным таблицам
    #39344753
maxxstorm
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
maxxstorm
Участник
qwwqmaxxstormПочему он пытается агрегировать?
патамушта каунт -- агрегатная ф--я.



приведите сам запрос еще раз, а то последнее :

Код: plaintext
Sort >  Unique > Aggregate

наводят на мысли о наличии count(distinct ...)

Код: sql
1.
2.
3.
SELECT COUNT(*) AS "__count" FROM "san_material" 
WHERE ("san_material"."detection_datetime" BETWEEN '2016-11-08T00:00:00+03:00'::timestamptz AND '2016-11-08T23:59:59.999999+03:00'::timestamptz 
AND "san_material"."id" IN (SELECT U0."material_id" FROM "san_materialdata" U0 WHERE (U0."tag_id" IN (602) AND U0."stop" = false)))



Я понимаю, что функция агрегатная, но почему иногда по индексу идет, а иногда битмап хип скан/агрегейт?
Индекс (material_id, tag_id) не уникален, так как значения могут повторяться(несколько одинаковых тегов на один материал). Из-за этого?
...
Рейтинг: 0 / 0
10.11.2016, 12:53
| Ответить | Цитировать | Написать  
Тяжелый запрос к связанным таблицам
    #39358303
maxxstorm
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
maxxstorm
Участник
vyegorovmaxxstorm,

Надо же не просто план смотреть, а время, сколько занимает исполнение запроса с таким планом, и кол-во буферов.

EXPLAIN (analyze, buffers)

Код: sql
1.
2.
3.
4.
5.
6.
7.
8.
9.
10.
11.
12.
13.
14.
15.
16.
17.
18.
19.
20.
21.
"Aggregate  (cost=834296.62..834296.63 rows=1 width=0) (actual time=4362356.963..4362356.963 rows=1 loops=1)"
"  Buffers: shared hit=2006033 read=1258402 dirtied=4612 written=2"
"  ->  Nested Loop  (cost=824440.20..834288.05 rows=3425 width=0) (actual time=398848.672..4362344.126 rows=7631 loops=1)"
"        Buffers: shared hit=2006033 read=1258402 dirtied=4612 written=2"
"        ->  HashAggregate  (cost=824439.77..824451.42 rows=1165 width=4) (actual time=398191.318..399277.184 rows=669380 loops=1)"
"              Group Key: u0.material_id"
"              Buffers: shared hit=37 read=547244 dirtied=25"
"              ->  Bitmap Heap Scan on san_materialdata u0  (cost=6135.79..823677.44 rows=304932 width=4) (actual time=15038.557..396035.338 rows=1150184 loops=1)"
"                    Recheck Cond: (tag_id = 602)"
"                    Heap Blocks: exact=543537"
"                    Buffers: shared hit=37 read=547244 dirtied=25"
"                    ->  Bitmap Index Scan on san_materialdata_76f094bc  (cost=0.00..6059.56 rows=304932 width=0) (actual time=14790.504..14790.504 rows=1150630 loops=1)"
"                          Index Cond: (tag_id = 602)"
"                          Buffers: shared hit=4 read=3740"
"        ->  Index Scan using san_material_pkey on san_material  (cost=0.43..8.43 rows=1 width=4) (actual time=5.917..5.918 rows=0 loops=669380)"
"              Index Cond: (id = u0.material_id)"
"              Filter: ((detection_datetime >= '2016-11-29 00:00:00+03'::timestamp with time zone) AND (detection_datetime <= '2016-11-29 23:59:59.999999+03'::timestamp with time zone))"
"              Rows Removed by Filter: 1"
"              Buffers: shared hit=2005996 read=711158 dirtied=4587 written=2"
"Planning time: 0.454 ms"
"Execution time: 4362370.904 ms"



Статистика по полю material_id 1000, но все равно ошибается планировщик. Почему?
...
Рейтинг: 0 / 0
01.12.2016, 11:19
| Ответить | Цитировать | Написать  
Тяжелый запрос к связанным таблицам
    #39358924
Alexius
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Alexius
Участник
maxxstorm,

покажите результаты выполнения запроса:

Код: sql
1.
select n_distinct from pg_stats where tablename = 'san_materialdata' and attname = 'material_id'



я правильно понял, что в san_materialdata 110М строк и среди них 6М уникальных material_id ?

после того, как добавлялась статистика, analyze san_materialdata делался?
...
Рейтинг: 0 / 0
01.12.2016, 21:10
| Ответить | Цитировать | Написать  
Тяжелый запрос к связанным таблицам
    #39359097
maxxstorm
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
maxxstorm
Участник
Alexiusmaxxstorm,

покажите результаты выполнения запроса:

Код: sql
1.
select n_distinct from pg_stats where tablename = 'san_materialdata' and attname = 'material_id'



я правильно понял, что в san_materialdata 110М строк и среди них 6М уникальных material_id ?

после того, как добавлялась статистика, analyze san_materialdata делался?

Результат выполнения:
3.31129e+06

В san_materialdata порядка 150М строк и уникальных material_id 10М.
analyze san_materialdata делался, в том числе и автоматически каждую ночь делается.
...
Рейтинг: 0 / 0
02.12.2016, 09:32
| Ответить | Цитировать | Написать  
Тяжелый запрос к связанным таблицам
    #39359232
Фотография vyegorov
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
vyegorov
Участник
maxxstorm,

А можно вывод такого запроса глянуть:
Код: sql
1.
SELECT attname,null_frac,n_distinct,correlation FROM pg_stats WHERE tablename='san_materialdata' AND attname IN ('tag_id','material_id');
...
Рейтинг: 0 / 0
02.12.2016, 12:19
| Ответить | Цитировать | Написать  
Тяжелый запрос к связанным таблицам
    #39359234
maxxstorm
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
maxxstorm
Участник
vyegorovmaxxstorm,

А можно вывод такого запроса глянуть:
Код: sql
1.
SELECT attname,null_frac,n_distinct,correlation FROM pg_stats WHERE tablename='san_materialdata' AND attname IN ('tag_id','material_id');



"material_id"; 0;3.31129e+06; 0.719951
"tag_id"; 0;137; -0.0315493
...
Рейтинг: 0 / 0
02.12.2016, 12:20
| Ответить | Цитировать | Написать  
Тяжелый запрос к связанным таблицам
    #39359260
Фотография vyegorov
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
vyegorov
Участник
qwwqприведите сам запрос еще раз, а то последнее :

Код: plaintext
Sort >  Unique > Aggregate

наводят на мысли о наличии count(distinct ...)
Оптимизатор понимает, что ему достаточно выдать уникальные значения и рассматривает `GROUP BY` по подзапросу.
И судя по всему он находит его привлекательным, даже с отключенным HashAgg.
...
Рейтинг: 0 / 0
02.12.2016, 12:51
| Ответить | Цитировать | Написать  
Тяжелый запрос к связанным таблицам
    #39359282
Alexius
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Alexius
Участник
maxxstormРезультат выполнения:
3.31129e+06

В san_materialdata порядка 150М строк и уникальных material_id 10М.
analyze san_materialdata делался, в том числе и автоматически каждую ночь делается.


не сходится что-то оценка планировщика с ожидаемой в строке с HashAggregate. а можно вывод запросов

Код: sql
1.
2.
explain select * from san_materialdata;
explain select * from san_material;



для каждой строки из san_material есть соответствующая запись в san_materialdata ?
...
Рейтинг: 0 / 0
02.12.2016, 13:15
| Ответить | Цитировать | Написать  
Тяжелый запрос к связанным таблицам
    #39359284
Alexius
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Alexius
Участник
Alexius,

и еще раз план последнего запроса на всякий случай (можно без analyze), может n_distinct изменился и поэтому не сходится.
...
Рейтинг: 0 / 0
02.12.2016, 13:17
| Ответить | Цитировать | Написать  
Тяжелый запрос к связанным таблицам
    #39359285
maxxstorm
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
maxxstorm
Участник
Alexius

не сходится что-то оценка планировщика с ожидаемой в строке с HashAggregate. а можно вывод запросов

Код: sql
1.
2.
explain select * from san_materialdata;
explain select * from san_material;



для каждой строки из san_material есть соответствующая запись в san_materialdata ?

"Seq Scan on san_materialdata (cost=0.00..3704419.36 rows=167943136 width=62)"
"Seq Scan on san_material (cost=0.00..2623611.56 rows=12420256 width=1129)"


Не для каждой строки есть запись. Могут быть записи в san_material без тегов(записей в san_materialdata)
...
Рейтинг: 0 / 0
02.12.2016, 13:18
| Ответить | Цитировать | Написать  
Тяжелый запрос к связанным таблицам
    #39359320
maxxstorm
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
maxxstorm
Участник
AlexiusAlexius,

и еще раз план последнего запроса на всякий случай (можно без analyze), может n_distinct изменился и поэтому не сходится.

Сделал еще раз analyze san_materialdata, теперь n_distinct немного поменялся:
Код: sql
1.
2.
"material_id";0;649634;0.726152
"tag_id";0;133;-0.0277582



Старый запрос(за 1 день) идет теперь по индексу:
Код: sql
1.
2.
3.
4.
5.
6.
7.
8.
9.
"Aggregate  (cost=1409740.96..1409740.97 rows=1 width=0)"
"  ->  Nested Loop Semi Join  (cost=6776.83..1409720.14 rows=8327 width=0)"
"        ->  Bitmap Heap Scan on san_material  (cost=6776.26..511318.38 rows=159202 width=4)"
"              Recheck Cond: ((detection_datetime >= '2016-11-29 00:00:00+03'::timestamp with time zone) AND (detection_datetime <= '2016-11-29 23:59:59.999999+03'::timestamp with time zone))"
"              ->  Bitmap Index Scan on san_material_detection_datetime_cb0a0120_uniq  (cost=0.00..6736.46 rows=159202 width=0)"
"                    Index Cond: ((detection_datetime >= '2016-11-29 00:00:00+03'::timestamp with time zone) AND (detection_datetime <= '2016-11-29 23:59:59.999999+03'::timestamp with time zone))"
"        ->  Index Scan using san_materialdata_material_id_32150950_idx on san_materialdata u0  (cost=0.57..10.50 rows=2 width=4)"
"              Index Cond: ((material_id = san_material.id) AND (tag_id = 602))"
"              Filter: (NOT stop)"


однако, если взять интервал за 2 дня, то картина та же:
Код: sql
1.
2.
3.
4.
5.
6.
7.
8.
9.
10.
11.
12.
"Aggregate  (cost=1895258.32..1895258.33 rows=1 width=0)"
"  ->  Nested Loop  (cost=1856884.81..1895216.94 rows=16552 width=0)"
"        ->  HashAggregate  (cost=1856884.38..1856930.13 rows=4575 width=4)"
"              Group Key: u0.material_id"
"              ->  Bitmap Heap Scan on san_materialdata u0  (cost=23855.81..1853927.92 rows=1182584 width=4)"
"                    Recheck Cond: (tag_id = 602)"
"                    Filter: (NOT stop)"
"                    ->  Bitmap Index Scan on san_materialdata_76f094bc  (cost=0.00..23560.16 rows=1182879 width=0)"
"                          Index Cond: (tag_id = 602)"
"        ->  Index Scan using san_material_pkey on san_material  (cost=0.43..8.36 rows=1 width=4)"
"              Index Cond: (id = u0.material_id)"
"              Filter: ((detection_datetime >= '2016-11-28 00:00:00+03'::timestamp with time zone) AND (detection_datetime <= '2016-11-29 23:59:59.999999+03'::timestamp with time zone))"
...
Рейтинг: 0 / 0
02.12.2016, 13:51
| Ответить | Цитировать | Написать  
Тяжелый запрос к связанным таблицам
    #39359401
Фотография vyegorov
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
vyegorov
Участник
maxxstorm,

Ну... n_distinct в 5 раз меньше стал, это много! И теперь расчёты совпадают.

Вы ловите неприятный эффект того, что планировщик не знает о том, что `san_material.material_id` и `san_material.detection_datetime` коррелируют.

Тут несколько вариантов как "вправить мозг":
- `CREATE INDEX CONCURRENTLY i_san_materialdata_tag_n_id ON san_materialdata(tag_id, material_id)` -- чтобы подзапрос быстрее отрабатывал и лучше планировался.
- `CREATE INDEX CONCURRENTLY i_san_material_dtime_id ON san_material(detection_time, id)` -- планировщик сможет точнее оценить кол-во ID-шек в заданном диапазоне дат и не идти по ПК (да и IOS может выйти)
- `CREATE INDEX CONCURRENTLY i_san_material_id_dtime ON san_material(id, detection_time)` -- так корректнее, поскольку диапазоны надо ставить в конце (дата обычно на больше-меньше проверяется, id -- на соответствие).
...
Рейтинг: 0 / 0
02.12.2016, 15:23
| Ответить | Цитировать | Написать  
Тяжелый запрос к связанным таблицам
    #39359439
maxxstorm
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
maxxstorm
Участник
vyegorovmaxxstorm,

Ну... n_distinct в 5 раз меньше стал, это много! И теперь расчёты совпадают.

Вы ловите неприятный эффект того, что планировщик не знает о том, что `san_material.material_id` и `san_material.detection_datetime` коррелируют.

Тут несколько вариантов как "вправить мозг":
- `CREATE INDEX CONCURRENTLY i_san_materialdata_tag_n_id ON san_materialdata(tag_id, material_id)` -- чтобы подзапрос быстрее отрабатывал и лучше планировался.
- `CREATE INDEX CONCURRENTLY i_san_material_dtime_id ON san_material(detection_time, id)` -- планировщик сможет точнее оценить кол-во ID-шек в заданном диапазоне дат и не идти по ПК (да и IOS может выйти)
- `CREATE INDEX CONCURRENTLY i_san_material_id_dtime ON san_material(id, detection_time)` -- так корректнее, поскольку диапазоны надо ставить в конце (дата обычно на больше-меньше проверяется, id -- на соответствие).

Первый индекс уже есть, а вот второй надо попробовать. Однако, щас другая проблема, один и тот же запрос отрабатывает с большой разницей по времени, когда читает с диска и из шаред буфера:
Код: sql
1.
2.
3.
4.
5.
6.
7.
8.
"Aggregate  (cost=16902.06..16902.07 rows=1 width=0) (actual time=22733.112..22733.112 rows=1 loops=1)"
"  Buffers: shared hit=70660 read=10521"
"  ->  Index Only Scan using san_material_detection_datetime_cb0a0120_uniq on san_material  (cost=0.43..16424.47 rows=191035 width=0) (actual time=7.941..22700.582 rows=191577 loops=1)"
"        Index Cond: ((detection_datetime >= '2016-11-17 00:00:00+03'::timestamp with time zone) AND (detection_datetime <= '2016-11-17 23:59:59.999999+03'::timestamp with time zone))"
"        Heap Fetches: 48368"
"        Buffers: shared hit=70660 read=10521"
"Planning time: 0.164 ms"
"Execution time: 22733.139 ms"



Код: sql
1.
2.
3.
4.
5.
6.
7.
8.
"Aggregate  (cost=16902.06..16902.07 rows=1 width=0) (actual time=95.168..95.168 rows=1 loops=1)"
"  Buffers: shared hit=81616"
"  ->  Index Only Scan using san_material_detection_datetime_cb0a0120_uniq on san_material  (cost=0.43..16424.47 rows=191035 width=0) (actual time=0.019..76.548 rows=191577 loops=1)"
"        Index Cond: ((detection_datetime >= '2016-11-17 00:00:00+03'::timestamp with time zone) AND (detection_datetime <= '2016-11-17 23:59:59.999999+03'::timestamp with time zone))"
"        Heap Fetches: 48805"
"        Buffers: shared hit=81616"
"Planning time: 0.107 ms"
"Execution time: 95.191 ms"



Неужели прочитать с диска 10521 блоков занимает 22 секунды?
...
Рейтинг: 0 / 0
02.12.2016, 16:04
| Ответить | Цитировать | Написать  
Тяжелый запрос к связанным таблицам
    #39359458
maxxstorm
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
maxxstorm
Участник
vyegorovmaxxstorm,

Ну... n_distinct в 5 раз меньше стал, это много! И теперь расчёты совпадают.

Вы ловите неприятный эффект того, что планировщик не знает о том, что `san_material.material_id` и `san_material.detection_datetime` коррелируют.

Тут несколько вариантов как "вправить мозг":
- `CREATE INDEX CONCURRENTLY i_san_materialdata_tag_n_id ON san_materialdata(tag_id, material_id)` -- чтобы подзапрос быстрее отрабатывал и лучше планировался.
- `CREATE INDEX CONCURRENTLY i_san_material_dtime_id ON san_material(detection_time, id)` -- планировщик сможет точнее оценить кол-во ID-шек в заданном диапазоне дат и не идти по ПК (да и IOS может выйти)
- `CREATE INDEX CONCURRENTLY i_san_material_id_dtime ON san_material(id, detection_time)` -- так корректнее, поскольку диапазоны надо ставить в конце (дата обычно на больше-меньше проверяется, id -- на соответствие).

Вот здесь время равно ошибка планировщика в HashAggregate 677909 строк вместо ожидаемых 4575:
Код: sql
1.
2.
3.
4.
5.
6.
7.
8.
9.
10.
11.
12.
13.
14.
15.
16.
17.
18.
19.
20.
21.
22.
"Aggregate  (cost=1895258.32..1895258.33 rows=1 width=0) (actual time=5094071.793..5094071.793 rows=1 loops=1)"
"  Buffers: shared hit=2025206 read=1264644 dirtied=1079"
"  ->  Nested Loop  (cost=1856884.81..1895216.94 rows=16552 width=0) (actual time=442399.330..5094045.011 rows=15416 loops=1)"
"        Buffers: shared hit=2025206 read=1264644 dirtied=1079"
"        ->  HashAggregate  (cost=1856884.38..1856930.13 rows=4575 width=4) (actual time=441608.133..442743.481 rows=677909 loops=1)"
"              Group Key: u0.material_id"
"              Buffers: shared hit=7585 read=546810 dirtied=106"
"              ->  Bitmap Heap Scan on san_materialdata u0  (cost=23855.81..1853927.92 rows=1182584 width=4) (actual time=17118.598..439586.215 rows=1164661 loops=1)"
"                    Recheck Cond: (tag_id = 602)"
"                    Filter: (NOT stop)"
"                    Heap Blocks: exact=550599"
"                    Buffers: shared hit=7585 read=546810 dirtied=106"
"                    ->  Bitmap Index Scan on san_materialdata_76f094bc  (cost=0.00..23560.16 rows=1182879 width=0) (actual time=16889.923..16889.923 rows=1164661 loops=1)"
"                          Index Cond: (tag_id = 602)"
"                          Buffers: shared hit=6 read=3790"
"        ->  Index Scan using san_material_pkey on san_material  (cost=0.43..8.36 rows=1 width=4) (actual time=6.857..6.859 rows=0 loops=677909)"
"              Index Cond: (id = u0.material_id)"
"              Filter: ((detection_datetime >= '2016-11-28 00:00:00+03'::timestamp with time zone) AND (detection_datetime <= '2016-11-29 23:59:59.999999+03'::timestamp with time zone))"
"              Rows Removed by Filter: 1"
"              Buffers: shared hit=2017621 read=717834 dirtied=973"
"Planning time: 0.562 ms"
"Execution time: 5094108.176 ms"
...
Рейтинг: 0 / 0
02.12.2016, 16:14
| Ответить | Цитировать | Написать  
Тяжелый запрос к связанным таблицам
    #39359460
Фотография Maxim Boguk
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Maxim Boguk
Участник
maxxstormvyegorovmaxxstorm,

Ну... n_distinct в 5 раз меньше стал, это много! И теперь расчёты совпадают.

Вы ловите неприятный эффект того, что планировщик не знает о том, что `san_material.material_id` и `san_material.detection_datetime` коррелируют.

Тут несколько вариантов как "вправить мозг":
- `CREATE INDEX CONCURRENTLY i_san_materialdata_tag_n_id ON san_materialdata(tag_id, material_id)` -- чтобы подзапрос быстрее отрабатывал и лучше планировался.
- `CREATE INDEX CONCURRENTLY i_san_material_dtime_id ON san_material(detection_time, id)` -- планировщик сможет точнее оценить кол-во ID-шек в заданном диапазоне дат и не идти по ПК (да и IOS может выйти)
- `CREATE INDEX CONCURRENTLY i_san_material_id_dtime ON san_material(id, detection_time)` -- так корректнее, поскольку диапазоны надо ставить в конце (дата обычно на больше-меньше проверяется, id -- на соответствие).

Первый индекс уже есть, а вот второй надо попробовать. Однако, щас другая проблема, один и тот же запрос отрабатывает с большой разницей по времени, когда читает с диска и из шаред буфера:
Код: sql
1.
2.
3.
4.
5.
6.
7.
8.
"Aggregate  (cost=16902.06..16902.07 rows=1 width=0) (actual time=22733.112..22733.112 rows=1 loops=1)"
"  Buffers: shared hit=70660 read=10521"
"  ->  Index Only Scan using san_material_detection_datetime_cb0a0120_uniq on san_material  (cost=0.43..16424.47 rows=191035 width=0) (actual time=7.941..22700.582 rows=191577 loops=1)"
"        Index Cond: ((detection_datetime >= '2016-11-17 00:00:00+03'::timestamp with time zone) AND (detection_datetime <= '2016-11-17 23:59:59.999999+03'::timestamp with time zone))"
"        Heap Fetches: 48368"
"        Buffers: shared hit=70660 read=10521"
"Planning time: 0.164 ms"
"Execution time: 22733.139 ms"



Код: sql
1.
2.
3.
4.
5.
6.
7.
8.
"Aggregate  (cost=16902.06..16902.07 rows=1 width=0) (actual time=95.168..95.168 rows=1 loops=1)"
"  Buffers: shared hit=81616"
"  ->  Index Only Scan using san_material_detection_datetime_cb0a0120_uniq on san_material  (cost=0.43..16424.47 rows=191035 width=0) (actual time=0.019..76.548 rows=191577 loops=1)"
"        Index Cond: ((detection_datetime >= '2016-11-17 00:00:00+03'::timestamp with time zone) AND (detection_datetime <= '2016-11-17 23:59:59.999999+03'::timestamp with time zone))"
"        Heap Fetches: 48805"
"        Buffers: shared hit=81616"
"Planning time: 0.107 ms"
"Execution time: 95.191 ms"



Неужели прочитать с диска 10521 блоков занимает 22 секунды?


Если у вас не ssd а механика то у вас в лучшем случае 15.000RPM диски а скорее всего 10.000RPM... максимум что можно с такого диска иметь это 200-250 случайных чтений в секунду, так что 22 секунды - еще приличный вполне результат (могло быть и в 2-3 раза медленнее легко).
...
Рейтинг: 0 / 0
02.12.2016, 16:15
| Ответить | Цитировать | Написать  
Тяжелый запрос к связанным таблицам
    #39359511
Фотография vyegorov
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
vyegorov
Участник
maxxstormВот здесь время равно ошибка планировщика в HashAggregate 677909 строк вместо ожидаемых 4575:
Код: sql
1.
2.
3.
4.
5.
6.
7.
8.
9.
10.
11.
12.
13.
14.
15.
16.
17.
18.
19.
20.
21.
22.
"Aggregate  (cost=1895258.32..1895258.33 rows=1 width=0) (actual time=5094071.793..5094071.793 rows=1 loops=1)"
"  Buffers: shared hit=2025206 read=1264644 dirtied=1079"
"  ->  Nested Loop  (cost=1856884.81..1895216.94 rows=16552 width=0) (actual time=442399.330..5094045.011 rows=15416 loops=1)"
"        Buffers: shared hit=2025206 read=1264644 dirtied=1079"
"        ->  HashAggregate  (cost=1856884.38..1856930.13 rows=4575 width=4) (actual time=441608.133..442743.481 rows=677909 loops=1)"
"              Group Key: u0.material_id"
"              Buffers: shared hit=7585 read=546810 dirtied=106"
"              ->  Bitmap Heap Scan on san_materialdata u0  (cost=23855.81..1853927.92 rows=1182584 width=4) (actual time=17118.598..439586.215 rows=1164661 loops=1)"
"                    Recheck Cond: (tag_id = 602)"
"                    Filter: (NOT stop)"
"                    Heap Blocks: exact=550599"
"                    Buffers: shared hit=7585 read=546810 dirtied=106"
"                    ->  Bitmap Index Scan on san_materialdata_76f094bc  (cost=0.00..23560.16 rows=1182879 width=0) (actual time=16889.923..16889.923 rows=1164661 loops=1)"
"                          Index Cond: (tag_id = 602)"
"                          Buffers: shared hit=6 read=3790"
"        ->  Index Scan using san_material_pkey on san_material  (cost=0.43..8.36 rows=1 width=4) (actual time=6.857..6.859 rows=0 loops=677909)"
"              Index Cond: (id = u0.material_id)"
"              Filter: ((detection_datetime >= '2016-11-28 00:00:00+03'::timestamp with time zone) AND (detection_datetime <= '2016-11-29 23:59:59.999999+03'::timestamp with time zone))"
"              Rows Removed by Filter: 1"
"              Buffers: shared hit=2017621 read=717834 dirtied=973"
"Planning time: 0.562 ms"
"Execution time: 5094108.176 ms"


А ПЖ не может (пока, надеюсь) увидеть корреляцию.
У `san_materialdata.material_id` 650k уникальных значений. А из таблицы, по предикату `tag_id=602` выбирается 0.7% записей.
Вот база и берёт эти самые 0.7% от 650k. И очень неловко, что по факту попадают... все, и даже чуть больше.

Кстати, вопрос -- почему HashAgg возвращает больше, чем n_distinct?..
...
Рейтинг: 0 / 0
02.12.2016, 16:52
| Ответить | Цитировать | Написать  
Тяжелый запрос к связанным таблицам
    #39359662
Alexius
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Alexius
Участник
maxxstormВот здесь время равно ошибка планировщика в HashAggregate 677909 строк вместо ожидаемых 4575:
Код: sql
1.
2.
3.
4.
5.
6.
7.
8.
9.
10.
11.
12.
13.
14.
15.
16.
17.
18.
19.
20.
21.
22.
"Aggregate  (cost=1895258.32..1895258.33 rows=1 width=0) (actual time=5094071.793..5094071.793 rows=1 loops=1)"
"  Buffers: shared hit=2025206 read=1264644 dirtied=1079"
"  ->  Nested Loop  (cost=1856884.81..1895216.94 rows=16552 width=0) (actual time=442399.330..5094045.011 rows=15416 loops=1)"
"        Buffers: shared hit=2025206 read=1264644 dirtied=1079"
"        ->  HashAggregate  (cost=1856884.38..1856930.13 rows=4575 width=4) (actual time=441608.133..442743.481 rows=677909 loops=1)"
"              Group Key: u0.material_id"
"              Buffers: shared hit=7585 read=546810 dirtied=106"
"              ->  Bitmap Heap Scan on san_materialdata u0  (cost=23855.81..1853927.92 rows=1182584 width=4) (actual time=17118.598..439586.215 rows=1164661 loops=1)"
"                    Recheck Cond: (tag_id = 602)"
"                    Filter: (NOT stop)"
"                    Heap Blocks: exact=550599"
"                    Buffers: shared hit=7585 read=546810 dirtied=106"
"                    ->  Bitmap Index Scan on san_materialdata_76f094bc  (cost=0.00..23560.16 rows=1182879 width=0) (actual time=16889.923..16889.923 rows=1164661 loops=1)"
"                          Index Cond: (tag_id = 602)"
"                          Buffers: shared hit=6 read=3790"
"        ->  Index Scan using san_material_pkey on san_material  (cost=0.43..8.36 rows=1 width=4) (actual time=6.857..6.859 rows=0 loops=677909)"
"              Index Cond: (id = u0.material_id)"
"              Filter: ((detection_datetime >= '2016-11-28 00:00:00+03'::timestamp with time zone) AND (detection_datetime <= '2016-11-29 23:59:59.999999+03'::timestamp with time zone))"
"              Rows Removed by Filter: 1"
"              Buffers: shared hit=2017621 read=717834 dirtied=973"
"Planning time: 0.562 ms"
"Execution time: 5094108.176 ms"




есть 2 варианта как это исправить. проблема в том, что на большой таблице сложно корректно оценить число различных значений поля (n_distinct) в некоторых случаях по небольшой выборке, которую делает analyze.

1) можно увеличить stats target для поля material_id еще больше, до 5000 или максимума 10000. минусы: analyze будет дольше (но это обычно не проблема), планирование запросов с этим полем может быть дольше - это может быть в некоторых случаях критично, если например есть много простых быстрых запросов с этой таблицей.

2) подсчитать действительное число различных material_id в этой таблице

Код: sql
1.
select count(*) from (select distinct material_id from san_materialdata) t


таким запросом скорей всего будет быстрее (если там будет hashaggregate. стоит предварительно убедиться в этом и если нет - то увеличить work_mem в сессии).

далее разделить его на 167943136 или сколько к тому моменту будет строк в таблице. и прописать полученное значение со знаком минус через
alter table san_materialdata alter column material_id set (n_distinct = x);

знак минус тут говорит планировщику что у нас не число различных значений, а отношение числа уникальных значений к общему числу строк. вполне вероятно что это соотношение в будущем не будет сильно меняться и достаточно его таким образом один раз "прибить гвоздями".
...
Рейтинг: 0 / 0
02.12.2016, 20:15
| Ответить | Цитировать | Написать  
Тяжелый запрос к связанным таблицам
    #39360214
maxxstorm
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
maxxstorm
Участник
Maxim BogukЕсли у вас не ssd а механика то у вас в лучшем случае 15.000RPM диски а скорее всего 10.000RPM... максимум что можно с такого диска иметь это 200-250 случайных чтений в секунду, так что 22 секунды - еще приличный вполне результат (могло быть и в 2-3 раза медленнее легко).

Не поверю, что чтобы прочитать 90000 строк из индекса, понадобится 365 секунд:

Код: sql
1.
2.
3.
4.
5.
6.
7.
8.
"Aggregate  (cost=26733.84..26733.85 rows=1 width=0) (actual time=365953.584..365953.584 rows=1 loops=1)"
"  Buffers: shared hit=130844 read=275479 dirtied=15881"
"  ->  Index Only Scan using san_material_detection_datetime_cb0a0120_uniq on san_material  (cost=0.43..26687.58 rows=18503 width=0) (actual time=25.930..365876.681 rows=90156 loops=1)"
"        Index Cond: ((detection_datetime >= '2016-12-04 00:00:00+03'::timestamp with time zone) AND (detection_datetime <= '2016-12-04 23:59:59.999999+03'::timestamp with time zone))"
"        Heap Fetches: 429927"
"        Buffers: shared hit=130844 read=275479 dirtied=15881"
"Planning time: 0.304 ms"
"Execution time: 365953.626 ms"



dirtied это что за блоки?
...
Рейтинг: 0 / 0
05.12.2016, 09:06
| Ответить | Цитировать | Написать  
Тяжелый запрос к связанным таблицам
    #39360234
Фотография vyegorov
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
vyegorov
Участник
maxxstorm,

База читает не по-строчно, а по-блочно.
Было `read=275479` произвольных обращений к диску. Чтобы точно знать время IO операций, можно включить `track_io_timing=on`, тогда в плане будет видно реальное время работы с дисками.

`dirtied` значит, что ваш запрос должен был "подчищать" за другими запросами, проставля hint bits в записях.
...
Рейтинг: 0 / 0
05.12.2016, 10:01
| Ответить | Цитировать | Написать  
Тяжелый запрос к связанным таблицам
    #39360263
qwwq
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
qwwq
Участник
maxxstorm
Не поверю, что чтобы прочитать 90000 строк из индекса, понадобится 365 секунд:


если базе придется читать 90000 строк из маленькой таблички на 90 000--900 000 строк, то она просто забьёт на индексы и прочитает табличку последовательным доступом. т.е. относительно быстро. и отфильтрует.


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

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


к тому же "по индексу", но "с доступом к самим записям" -- это несколько дороже, чем "только по индексу" -- там как бе 2 отдельные физические реляции получаются. и к обеим -- физически доступаться.



зы. скоро обещают x--память, или как её -- скоро про механический доступ можно будет не вспоминать
...
Рейтинг: 0 / 0
05.12.2016, 10:36
| Ответить | Цитировать | Написать  
Тяжелый запрос к связанным таблицам
    #39360264
maxxstorm
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
maxxstorm
Участник
vyegorovmaxxstorm,

База читает не по-строчно, а по-блочно.
Было `read=275479` произвольных обращений к диску. Чтобы точно знать время IO операций, можно включить `track_io_timing=on`, тогда в плане будет видно реальное время работы с дисками.

`dirtied` значит, что ваш запрос должен был "подчищать" за другими запросами, проставля hint bits в записях.

Вот второй раз делаю этот же запрос:

Код: sql
1.
2.
3.
4.
5.
6.
7.
8.
"Aggregate  (cost=26045.35..26045.36 rows=1 width=0) (actual time=96550.311..96550.312 rows=1 loops=1)"
"  Buffers: shared hit=20760 read=72523 dirtied=98"
"  ->  Index Only Scan using san_material_detection_datetime_cb0a0120_uniq on san_material  (cost=0.43..26000.29 rows=18024 width=0) (actual time=28.380..96475.752 rows=90156 loops=1)"
"        Index Cond: ((detection_datetime >= '2016-12-04 00:00:00+03'::timestamp with time zone) AND (detection_datetime <= '2016-12-04 23:59:59.999999+03'::timestamp with time zone))"
"        Heap Fetches: 91691"
"        Buffers: shared hit=20760 read=72523 dirtied=98"
"Planning time: 0.172 ms"
"Execution time: 96550.346 ms"



Откуда щас dirtied взялись? Это данные за вчера и апдейтов по ним не бывает.
...
Рейтинг: 0 / 0
05.12.2016, 10:36
| Ответить | Цитировать | Написать  
Тяжелый запрос к связанным таблицам
    #39360287
maxxstorm
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
maxxstorm
Участник
qwwqmaxxstormНе поверю, что чтобы прочитать 90000 строк из индекса, понадобится 365 секунд:


если базе придется читать 90000 строк из маленькой таблички на 90 000--900 000 строк, то она просто забьёт на индексы и прочитает табличку последовательным доступом. т.е. относительно быстро. и отфильтрует.


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

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


к тому же "по индексу", но "с доступом к самим записям" -- это несколько дороже, чем "только по индексу" -- там как бе 2 отдельные физические реляции получаются. и к обеим -- физически доступаться.



зы. скоро обещают x--память, или как её -- скоро про механический доступ можно будет не вспоминать

Делаю тот же запрос на тестовом сервере:

Код: sql
1.
2.
3.
4.
5.
6.
7.
8.
"Aggregate  (cost=5054.53..5054.54 rows=1 width=0) (actual time=2771.180..2771.180 rows=1 loops=1)"
"  Buffers: shared hit=116282 read=573 dirtied=1688"
"  ->  Index Only Scan using san_material_detection_datetime_cb0a0120_uniq on san_material  (cost=0.43..5040.38 rows=5662 width=0) (actual time=40.119..2763.133 rows=56252 loops=1)"
"        Index Cond: ((detection_datetime >= '2016-12-04 00:00:00+03'::timestamp with time zone) AND (detection_datetime <= '2016-12-04 23:59:59.999999+03'::timestamp with time zone))"
"        Heap Fetches: 108783"
"        Buffers: shared hit=116282 read=573 dirtied=1688"
"Planning time: 0.425 ms"
"Execution time: 2771.216 ms"



Разница в скорости на 2 порядка. Разница в количестве записей 1:1.5, к тому же на тестовом сервере диски старые и медленные.
...
Рейтинг: 0 / 0
05.12.2016, 10:53
| Ответить | Цитировать | Написать  
Тяжелый запрос к связанным таблицам
    #39360319
qwwq
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
qwwq
Участник
maxxstorm
Вот второй раз делаю этот же запрос:

Код: sql
1.
2.
3.
4.
5.
6.
7.
8.
"Aggregate  (cost=26045.35..26045.36 rows=1 width=0) (actual time=96550.311..96550.312 rows=1 loops=1)"
"  Buffers: shared hit=20760 read=72523 dirtied=98"
"  ->  Index Only Scan using san_material_detection_datetime_cb0a0120_uniq on san_material  (cost=0.43..26000.29 rows=18024 width=0) (actual time=28.380..96475.752 rows=90156 loops=1)"
"        Index Cond: ((detection_datetime >= '2016-12-04 00:00:00+03'::timestamp with time zone) AND (detection_datetime <= '2016-12-04 23:59:59.999999+03'::timestamp with time zone))"
"        Heap Fetches: 91691"
"        Buffers: shared hit=20760 read=72523 dirtied=98"
"Planning time: 0.172 ms"
"Execution time: 96550.346 ms"





maxxstorm<>
Делаю тот же запрос на тестовом сервере:

Код: sql
1.
2.
3.
4.
5.
6.
7.
8.
"Aggregate  (cost=5054.53..5054.54 rows=1 width=0) (actual time=2771.180..2771.180 rows=1 loops=1)"
"  Buffers: shared hit=116282 read=573 dirtied=1688"
"  ->  Index Only Scan using san_material_detection_datetime_cb0a0120_uniq on san_material  (cost=0.43..5040.38 rows=5662 width=0) (actual time=40.119..2763.133 rows=56252 loops=1)"
"        Index Cond: ((detection_datetime >= '2016-12-04 00:00:00+03'::timestamp with time zone) AND (detection_datetime <= '2016-12-04 23:59:59.999999+03'::timestamp with time zone))"
"        Heap Fetches: 108783"
"        Buffers: shared hit=116282 read=573 dirtied=1688"
"Planning time: 0.425 ms"
"Execution time: 2771.216 ms"



Разница в скорости на 2 порядка. Разница в количестве записей 1:1.5, к тому же на тестовом сервере диски старые и медленные.

читать ещё не научили ?
...
Рейтинг: 0 / 0
05.12.2016, 11:13
| Ответить | Цитировать | Написать  
Тяжелый запрос к связанным таблицам
    #39360470
maxxstorm
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
maxxstorm
Участник
qwwqmaxxstormВот второй раз делаю этот же запрос:

Код: sql
1.
2.
3.
4.
5.
6.
7.
8.
"Aggregate  (cost=26045.35..26045.36 rows=1 width=0) (actual time=96550.311..96550.312 rows=1 loops=1)"
"  Buffers: shared hit=20760 read=72523 dirtied=98"
"  ->  Index Only Scan using san_material_detection_datetime_cb0a0120_uniq on san_material  (cost=0.43..26000.29 rows=18024 width=0) (actual time=28.380..96475.752 rows=90156 loops=1)"
"        Index Cond: ((detection_datetime >= '2016-12-04 00:00:00+03'::timestamp with time zone) AND (detection_datetime <= '2016-12-04 23:59:59.999999+03'::timestamp with time zone))"
"        Heap Fetches: 91691"
"        Buffers: shared hit=20760 read=72523 dirtied=98"
"Planning time: 0.172 ms"
"Execution time: 96550.346 ms"





maxxstorm<>
Делаю тот же запрос на тестовом сервере:

Код: sql
1.
2.
3.
4.
5.
6.
7.
8.
"Aggregate  (cost=5054.53..5054.54 rows=1 width=0) (actual time=2771.180..2771.180 rows=1 loops=1)"
"  Buffers: shared hit=116282 read=573 dirtied=1688"
"  ->  Index Only Scan using san_material_detection_datetime_cb0a0120_uniq on san_material  (cost=0.43..5040.38 rows=5662 width=0) (actual time=40.119..2763.133 rows=56252 loops=1)"
"        Index Cond: ((detection_datetime >= '2016-12-04 00:00:00+03'::timestamp with time zone) AND (detection_datetime <= '2016-12-04 23:59:59.999999+03'::timestamp with time zone))"
"        Heap Fetches: 108783"
"        Buffers: shared hit=116282 read=573 dirtied=1688"
"Planning time: 0.425 ms"
"Execution time: 2771.216 ms"



Разница в скорости на 2 порядка. Разница в количестве записей 1:1.5, к тому же на тестовом сервере диски старые и медленные.

читать ещё не научили ?

Туплю чот. Как я понимаю, отца русской демократии спасет только SSD?
...
Рейтинг: 0 / 0
05.12.2016, 14:01
| Ответить | Цитировать | Написать  
Тяжелый запрос к связанным таблицам
    #39360578
Фотография vyegorov
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
vyegorov
Участник
maxxstorm,

Ну... SSD оно всегда хорошо. Но у меня есть мысль, что у вас могут быть распухшие таблицы и/или индексы.
Там много UPDATE/DELETE операций?

Можно сделать идентичный существующему индекс с другим именем и сравнить размеры.
Можно таблицы "пожать", и индексы тогда точно надо будет перестроить.
...
Рейтинг: 0 / 0
05.12.2016, 15:33
| Ответить | Цитировать | Написать  
Тяжелый запрос к связанным таблицам
    #39360587
maxxstorm
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
maxxstorm
Участник
vyegorovmaxxstorm,

Ну... SSD оно всегда хорошо. Но у меня есть мысль, что у вас могут быть распухшие таблицы и/или индексы.
Там много UPDATE/DELETE операций?

Можно сделать идентичный существующему индекс с другим именем и сравнить размеры.
Можно таблицы "пожать", и индексы тогда точно надо будет перестроить.

2-3млн инсертов + 200к делетов в сутки
...
Рейтинг: 0 / 0
05.12.2016, 15:38
| Ответить | Цитировать | Написать  
Тяжелый запрос к связанным таблицам
    #39360593
Фотография vyegorov
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
vyegorov
Участник
maxxstorm,

А настройки autovcauum-а наверно умолчательные, да?
Код: sql
1.
SELECT name,setting FROM pg_settings WHERE name ~ 'vacuum';
...
Рейтинг: 0 / 0
05.12.2016, 15:41
| Ответить | Цитировать | Написать  
Тяжелый запрос к связанным таблицам
    #39360594
maxxstorm
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
maxxstorm
Участник
vyegorovmaxxstorm,

А настройки autovcauum-а наверно умолчательные, да?
Код: sql
1.
SELECT name,setting FROM pg_settings WHERE name ~ 'vacuum';



Настойки по умолчанию, но почему то автовакуум не отрабатывает. Мы запускаем вручную vacuum analyze.

Код: sql
1.
2.
3.
4.
5.
6.
7.
8.
9.
10.
11.
12.
13.
14.
15.
16.
17.
18.
19.
20.
21.
22.
23.
"autovacuum";"on"
"autovacuum_analyze_scale_factor";"0.1"
"autovacuum_analyze_threshold";"50"
"autovacuum_freeze_max_age";"200000000"
"autovacuum_max_workers";"3"
"autovacuum_multixact_freeze_max_age";"400000000"
"autovacuum_naptime";"60"
"autovacuum_vacuum_cost_delay";"20"
"autovacuum_vacuum_cost_limit";"-1"
"autovacuum_vacuum_scale_factor";"0.2"
"autovacuum_vacuum_threshold";"50"
"autovacuum_work_mem";"-1"
"log_autovacuum_min_duration";"-1"
"vacuum_cost_delay";"0"
"vacuum_cost_limit";"200"
"vacuum_cost_page_dirty";"20"
"vacuum_cost_page_hit";"1"
"vacuum_cost_page_miss";"10"
"vacuum_defer_cleanup_age";"0"
"vacuum_freeze_min_age";"50000000"
"vacuum_freeze_table_age";"150000000"
"vacuum_multixact_freeze_min_age";"5000000"
"vacuum_multixact_freeze_table_age";"150000000"
...
Рейтинг: 0 / 0
05.12.2016, 15:42
| Ответить | Цитировать | Написать  
Тяжелый запрос к связанным таблицам
    #39360597
maxxstorm
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
maxxstorm
Участник
Код: sql
1.
2.
3.
4.
5.
6.
7.
SELECT name,setting FROM pg_settings WHERE name ~ 'vacuum';

select relname, last_vacuum, last_analyze,last_autovacuum,last_autoanalyze

from pg_stat_all_tables

where relname in ('san_materialdata', 'san_material');



Код: sql
1.
2.
"san_materialdata";"2016-12-02 12:57:49.200485+03";"2016-12-02 13:02:19.212248+03";"";"2016-11-16 19:02:07.448167+03"
"san_material";"2016-12-02 11:35:05.015388+03";"2016-12-02 11:51:44.486291+03";"2016-11-29 21:52:19.955824+03";"2016-12-05 15:29:49.978115+03"
...
Рейтинг: 0 / 0
05.12.2016, 15:45
| Ответить | Цитировать | Написать  
Тяжелый запрос к связанным таблицам
    #39360701
qwwq
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
qwwq
Участник
maxxstorm2-3млн инсертов + 200к делетов в сутки

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

но пока физ--смысл вашей кухни мне не настолько ясен, чтобы тут что--то советовать наверняка. -- партицирование всё сильно усложнит планировщику. а тем более -- sql--деву.
...
Рейтинг: 0 / 0
05.12.2016, 17:06
| Ответить | Цитировать | Написать  
Тяжелый запрос к связанным таблицам
    #39376691
maxxstorm
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
maxxstorm
Участник
Сравниваю с аналогичным запросом из другой бд(размер 50гб):

Код: sql
1.
2.
3.
4.
5.
6.
7.
8.
9.
10.
11.
explain
(analyze, buffers, verbose)
select count(id) from sources_article

"Aggregate  (cost=584214.84..584214.85 rows=1 width=4) (actual time=8779.156..8779.156 rows=1 loops=1)"
"  Output: count(id)"
"  Buffers: shared hit=18937 read=428234 dirtied=148 written=114"
"  ->  Seq Scan on public.sources_article  (cost=0.00..556806.07 rows=10963507 width=4) (actual time=0.036..7306.261 rows=10981230 loops=1)"
"        Output: id"
"        Buffers: shared hit=18937 read=428234 dirtied=148 written=114"
"Total runtime: 8779.237 ms"



9 секунд и 428234 обращенй к диску.
Делаю аналочичный запрос в свою таблицу(размер бд 150гб):

Код: sql
1.
2.
3.
4.
5.
6.
7.
8.
9.
10.
11.
12.
13.
explain
(analyze, buffers, verbose)
select count(id) from san_material;

 Aggregate  (cost=1271243.88..1271243.89 rows=1 width=4) (actual time=192019.742..192019.743 rows=1 loops=1)
   Output: count(id)
   Buffers: shared hit=6809984 read=72450 dirtied=4851
   ->  Index Only Scan using san_material_pkey on public.san_material  (cost=0.56..1227437.79 rows=17522436 width=4) (actual time=0.123..189988.281 rows=15647414 loops=1)
         Output: id
         Heap Fetches: 322492
         Buffers: shared hit=6809984 read=72450 dirtied=4851
 Planning time: 0.115 ms
 Execution time: 192019.800 ms



Почему кост в 25 раз больше? Ведь количество записей одного порядка(11 и 15 млн соответственно)
...
Рейтинг: 0 / 0
28.12.2016, 11:41
| Ответить | Цитировать | Написать  
Тяжелый запрос к связанным таблицам
    #39376725
qwwq
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
qwwq
Участник
maxxstorm,

1. покажите
Код: sql
1.
2.
3.
4.
5.
6.
select * from pg_settings 
where 
	name ilike '%cost%'
	and name NOT ilike'%vacuum%'
order by name	;


с обеих; сравните попунктно.

и по мелочи :
2. при расчете коста использовано 17 и 11 лямов -- по ожиданиям, а не по факту. (мелочь, а поправочка)
3. операции разные. (хотя и не настолько)
...
Рейтинг: 0 / 0
28.12.2016, 12:03
| Ответить | Цитировать | Написать  
Тяжелый запрос к связанным таблицам
    #39376731
maxxstorm
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
maxxstorm
Участник
qwwqmaxxstorm,

1. покажите
Код: sql
1.
2.
3.
4.
5.
6.
select * from pg_settings 
where 
	name ilike '%cost%'
	and name NOT ilike'%vacuum%'
order by name	;


с обеих; сравните попунктно.

и по мелочи :
2. при расчете коста использовано 17 и 11 лямов -- по ожиданиям, а не по факту. (мелочь, а поправочка)
3. операции разные. (хотя и не настолько)

Абсолютно одинаковые дефолтные значения

Код: sql
1.
2.
3.
4.
5.
"cpu_index_tuple_cost";"0.005"
"cpu_operator_cost";"0.0025"
"cpu_tuple_cost";"0.01"
"random_page_cost";"4"
"seq_page_cost";"1"



Разные операция в смысле seq scan и index scan?
...
Рейтинг: 0 / 0
28.12.2016, 12:07
| Ответить | Цитировать | Написать  
Тяжелый запрос к связанным таблицам
    #39376744
qwwq
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
qwwq
Участник
maxxstormРазные операция в смысле seq scan и index scan?а что, по буквам названия совпадают, чоль ?

хотя почему подавлено сравнение со сек--сканом для вас -- не понял. думаю у вас какие--то енейблы отключены (что дает разовую прибавку к косту в 10 ярдов)

посмотрите
Код: sql
1.
2.
3.
4.
5.
select * from pg_settings 
where 
	name ilike 'enable%'
order by name	;
...
Рейтинг: 0 / 0
28.12.2016, 12:19
| Ответить | Цитировать | Написать  
Тяжелый запрос к связанным таблицам
    #39376750
Фотография vyegorov
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
vyegorov
Участник
maxxstorm,

Львиную долю в цене составляет обращение к буферам. Если размер базы в 3 раза больше, то это имеет значение.

Для маленькой базы у вас:
Код: sql
1.
2.
3.
  ->  Seq Scan on public.sources_article  (cost=0.00..556806.07 rows=10963507 width=4) (actual time=0.036..7306.261 rows=10981230 loops=1)
        Output: id
        Buffers: shared hit=18937 read=428234 dirtied=148 written=114


Для большой:
Код: sql
1.
2.
3.
4.
   ->  Index Only Scan using san_material_pkey on public.san_material  (cost=0.56..1227437.79 rows=17522436 width=4) (actual time=0.123..189988.281 rows=15647414 loops=1)
         Output: id
         Heap Fetches: 322492
         Buffers: shared hit=6809984 read=72450 dirtied=4851


Кол-во обращений надо считать общее, а не только `read`. И общее кол-во обращений во 2-м случае на порядок больше, ибо
это проход по индексу, произвольный доступ. Да ещё с переодическим "подглядыванием" в кучу.
...
Рейтинг: 0 / 0
28.12.2016, 12:22
| Ответить | Цитировать | Написать  
Тяжелый запрос к связанным таблицам
    #39376767
qwwq
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
qwwq
Участник
vyegorov,

там 2 вопроса --
1. почему оно секвенс скан отбросило (который видимо задизейблен)

2. зачем для IOS БЕЗ УСЛОВИЙ (т.е. по всем листам == по всем блокам индекса) рендом доступ ? ожидают очень плохую карту ? (можно ожидания где--то увидеть?). или индекс принципиально всегда так фрагментирован, что обязательно поблочный рендом доступ ? //и никогда и никак -- не прочитать одним куском
...
Рейтинг: 0 / 0
28.12.2016, 12:33
| Ответить | Цитировать | Написать  
Тяжелый запрос к связанным таблицам
    #39376785
maxxstorm
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
maxxstorm
Участник
qwwqпосмотрите
Код: sql
1.
2.
3.
4.
5.
select * from pg_settings 
where 
	name ilike 'enable%'
order by name	;



Все настройки одинаковые:

Код: sql
1.
2.
3.
4.
5.
6.
7.
8.
9.
10.
11.
"enable_bitmapscan";"on"
"enable_hashagg";"on"
"enable_hashjoin";"on"
"enable_indexonlyscan";"on"
"enable_indexscan";"on"
"enable_material";"on"
"enable_mergejoin";"on"
"enable_nestloop";"on"
"enable_seqscan";"on"
"enable_sort";"on"
"enable_tidscan";"on"
...
Рейтинг: 0 / 0
28.12.2016, 12:43
| Ответить | Цитировать | Написать  
Тяжелый запрос к связанным таблицам
    #39376789
maxxstorm
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
maxxstorm
Участник
Сделал на своей БД

Код: sql
1.
2.
3.
4.
5.
6.
7.
8.
9.
10.
11.
12.
13.
14.
15.
16.
17.
set enable_indexscan=off;
SET
analize=> set enable_indexonlyscan=off;
SET
analize=> explain
(analyze, buffers, verbose)
select count(id) from san_material;
                                                                 QUERY PLAN
--------------------------------------------------------------------------------------------------------------------------------------------
 Aggregate  (cost=4390342.45..4390342.46 rows=1 width=4) (actual time=593738.626..593738.627 rows=1 loops=1)
   Output: count(id)
   Buffers: shared hit=512144 read=3659168 dirtied=373
   ->  Seq Scan on public.san_material  (cost=0.00..4346536.36 rows=17522436 width=4) (actual time=0.046..591029.866 rows=15650113 loops=1)
         Output: id
         Buffers: shared hit=512144 read=3659168 dirtied=373
 Planning time: 0.171 ms
 Execution time: 593738.760 ms



4 миллиона обращений против 400к для первой
...
Рейтинг: 0 / 0
28.12.2016, 12:46
| Ответить | Цитировать | Написать  
Тяжелый запрос к связанным таблицам
    #39376794
Фотография vyegorov
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
vyegorov
Участник
qwwq,

Вопросы интересные.

1. Тут надо у @maxxstorm спрость что происходит. Я бы пробовал включать-выключать разные `enabled_%` опции и сравнивать косты.
Ещё было бы интересно увидеть размер таблицы и индексов в блоках и записях (relpages и reltuples) из `pg_class`, а также кол-во удалённых записей в `pg_stat_user_tables`.

2. Тут я не уверен, надо код смотреть или же гуглить. Мне думается, что ПЖ не умеет делать IndexFastFullScan (в терминах О), он читает листья в порядке индекса, следуя по ссылкам в служебной зоне страниц -- это и приводит к произвольному доступу.
...
Рейтинг: 0 / 0
28.12.2016, 12:50
| Ответить | Цитировать | Написать  
Тяжелый запрос к связанным таблицам
    #39376798
Фотография vyegorov
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
vyegorov
Участник
maxxstorm,

А много изменений в таблице? Может она у вас банально распухла?
...
Рейтинг: 0 / 0
28.12.2016, 12:52
| Ответить | Цитировать | Написать  
Тяжелый запрос к связанным таблицам
    #39376801
maxxstorm
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
maxxstorm
Участник
vyegorovmaxxstorm,

А много изменений в таблице? Может она у вас банально распухла?

апдейты редко бывают, автоваккум включен вроде, отрабатывает каждый день.
Как определить, что распухла?
...
Рейтинг: 0 / 0
28.12.2016, 12:53
| Ответить | Цитировать | Написать  
Тяжелый запрос к связанным таблицам
    #39376814
qwwq
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
qwwq
Участник
vyegorov,

там разница в буферах секскана -- тоже порядок. (при разнице ожиданий строк около 1,7)
видимо очень пустые блоки приходится читать в количествах.
...
Рейтинг: 0 / 0
28.12.2016, 13:00
| Ответить | Цитировать | Написать  
Тяжелый запрос к связанным таблицам
    #39376824
maxxstorm
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
maxxstorm
Участник
qwwqvyegorov,

там разница в буферах секскана -- тоже порядок. (при разнице ожиданий строк около 1,7)
видимо очень пустые блоки приходится читать в количествах.

vacuum full поможет?
...
Рейтинг: 0 / 0
28.12.2016, 13:11
| Ответить | Цитировать | Написать  
Тяжелый запрос к связанным таблицам
    #39376825
Фотография vyegorov
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
vyegorov
Участник
maxxstorm,

Для начала, посмотрите на вывод запроса (в `psql`):
Код: sql
1.
select * from pg_stat_user_tables where relname='san_material'\x\g\x



Затем посмотреть на вывод такого запроса (осторожно, читает всю таблицу):
Код: sql
1.
2.
CREATE EXTENSION IF NOT EXISTS pgstattuple;
select * from pgstattuple('san_material'::regclass)\x\g\x
...
Рейтинг: 0 / 0
28.12.2016, 13:12
| Ответить | Цитировать | Написать  
Тяжелый запрос к связанным таблицам
    #39376828
Фотография vyegorov
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
vyegorov
Участник
maxxstormvacuum full поможет?
Надо выяснить для начала что там такое. Если распухла, то:
поможет

надо пересмотреть настройки вакуума для конкретно этой таблицы также и на будущее
...
Рейтинг: 0 / 0
28.12.2016, 13:15
| Ответить | Цитировать | Написать  
Тяжелый запрос к связанным таблицам
    #39376853
maxxstorm
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
maxxstorm
Участник
vyegorovmaxxstorm,

Для начала, посмотрите на вывод запроса (в `psql`):
Код: sql
1.
select * from pg_stat_user_tables where relname='san_material'\x\g\x



Затем посмотреть на вывод такого запроса (осторожно, читает всю таблицу):
Код: sql
1.
2.
CREATE EXTENSION IF NOT EXISTS pgstattuple;
select * from pgstattuple('san_material'::regclass)\x\g\x



Первый:
relid | 16599
Код: sql
1.
2.
3.
4.
5.
6.
7.
8.
9.
10.
11.
12.
13.
14.
15.
16.
17.
18.
19.
20.
21.
schemaname          | public
relname             | san_material
seq_scan            | 133
seq_tup_read        | 1694578689
idx_scan            | 1325059781
idx_tup_fetch       | 1383377265
n_tup_ins           | 960156
n_tup_upd           | 3277970
n_tup_del           | 0
n_tup_hot_upd       | 800119
n_live_tup          | 14382456
n_dead_tup          | 18314
n_mod_since_analyze | 0
last_vacuum         | 2016-12-19 13:15:19.236529+03
last_autovacuum     | 2016-12-28 12:29:56.007004+03
last_analyze        | 2016-12-17 10:31:25.368694+03
last_autoanalyze    | 2016-12-28 12:56:29.958981+03
vacuum_count        | 2
autovacuum_count    | 177
analyze_count       | 3
autoanalyze_count   | 75



Второй:
Код: sql
1.
2.
3.
4.
5.
6.
7.
8.
9.
table_len          | 18474541056
tuple_count        | 14451005
tuple_len          | 16874253779
tuple_percent      | 91.34
dead_tuple_count   | 4161
dead_tuple_len     | 3025179
dead_tuple_percent | 0.02
free_space         | 1412980056
free_percent       | 7.65
...
Рейтинг: 0 / 0
28.12.2016, 13:35
| Ответить | Цитировать | Написать  
Тяжелый запрос к связанным таблицам
    #39376928
Фотография vyegorov
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
vyegorov
Участник
maxxstormСравниваю с аналогичным запросом из другой бд(размер 50гб):

Код: sql
1.
2.
3.
4.
5.
6.
7.
8.
9.
10.
11.
explain
(analyze, buffers, verbose)
select count(id) from sources_article

"Aggregate  (cost=584214.84..584214.85 rows=1 width=4) (actual time=8779.156..8779.156 rows=1 loops=1)"
"  Output: count(id)"
"  Buffers: shared hit=18937 read=428234 dirtied=148 written=114"
"  ->  Seq Scan on public.sources_article  (cost=0.00..556806.07 rows=10963507 width=4) (actual time=0.036..7306.261 rows=10981230 loops=1)"
"        Output: id"
"        Buffers: shared hit=18937 read=428234 dirtied=148 written=114"
"Total runtime: 8779.237 ms"



9 секунд и 428234 обращенй к диску.
Делаю аналочичный запрос в свою таблицу(размер бд 150гб):

Код: sql
1.
2.
3.
4.
5.
6.
7.
8.
9.
10.
11.
12.
13.
explain
(analyze, buffers, verbose)
select count(id) from san_material;

 Aggregate  (cost=1271243.88..1271243.89 rows=1 width=4) (actual time=192019.742..192019.743 rows=1 loops=1)
   Output: count(id)
   Buffers: shared hit=6809984 read=72450 dirtied=4851
   ->  Index Only Scan using san_material_pkey on public.san_material  (cost=0.56..1227437.79 rows=17522436 width=4) (actual time=0.123..189988.281 rows=15647414 loops=1)
         Output: id
         Heap Fetches: 322492
         Buffers: shared hit=6809984 read=72450 dirtied=4851
 Planning time: 0.115 ms
 Execution time: 192019.800 ms



Почему кост в 25 раз больше? Ведь количество записей одного порядка(11 и 15 млн соответственно)

А почему мы литры (sources_article) с километрами (san_material) сравниваем?..
...
Рейтинг: 0 / 0
28.12.2016, 14:37
| Ответить | Цитировать | Написать  
Тяжелый запрос к связанным таблицам
    #39376931
maxxstorm
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
maxxstorm
Участник
vyegorovА почему мы литры (sources_article) с километрами (san_material) сравниваем?..

Примерно одинаковые таблицы, одна и та же сущность. Просто удивила такая разница в скорости.
...
Рейтинг: 0 / 0
28.12.2016, 14:39
| Ответить | Цитировать | Написать  
Тяжелый запрос к связанным таблицам
    #39376942
Фотография Maxim Boguk
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Maxim Boguk
Участник
maxxstormvyegorovА почему мы литры (sources_article) с километрами (san_material) сравниваем?..

Примерно одинаковые таблицы, одна и та же сущность. Просто удивила такая разница в скорости.

Я бы track_io_timing в базе бы включил для начала.
С очень высокой вероятностью у вас время уходит на случайный ввод-вывод на IOS.

--
Maxim Boguk
www.postgresql-consulting.ru
...
Рейтинг: 0 / 0
28.12.2016, 14:53
| Ответить | Цитировать | Написать  
Тяжелый запрос к связанным таблицам
    #39376959
maxxstorm
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
maxxstorm
Участник
Maxim BogukЯ бы track_io_timing в базе бы включил для начала.
С очень высокой вероятностью у вас время уходит на случайный ввод-вывод на IOS.

--
Maxim Boguk
www.postgresql-consulting.ru

Включил:

Код: sql
1.
2.
3.
4.
5.
6.
7.
8.
9.
10.
11.
Aggregate  (cost=437042.55..437042.56 rows=1 width=4) (actual time=15401.903..15401.903 rows=1 loops=1)
   Output: count(id)
   Buffers: shared hit=5536251 read=14816
   I/O Timings: read=10339.508
   ->  Index Only Scan using san_material_pkey on public.san_material  (cost=0.43..401125.11 rows=14366978 width=4) (actual time=0.047..13713.557 rows=14451005 loops=1)
         Output: id
         Heap Fetches: 0
         Buffers: shared hit=5536251 read=14816
         I/O Timings: read=10339.508
 Planning time: 0.402 ms
 Execution time: 15401.989 ms
...
Рейтинг: 0 / 0
28.12.2016, 15:05
| Ответить | Цитировать | Написать  
Тяжелый запрос к связанным таблицам
    #39376995
qwwq
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
qwwq
Участник
maxxstormvyegorovА почему мы литры (sources_article) с километрами (san_material) сравниваем?..

Примерно одинаковые таблицы, одна и та же сущность. Просто удивила такая разница в скорости.
сущность могабыть одна, а вот ширина записи может разниться на порядок. особенно -- если где--то полно дроппед колумн с данными нагенерячено упор[от]ным трудом "оптимизатора--исследователя". и кол--во записей / на блок, как следствие, может отличаццо примерно в то же число раз.
...
Рейтинг: 0 / 0
28.12.2016, 15:43
| Ответить | Цитировать | Написать  
Тяжелый запрос к связанным таблицам
    #39377030
Фотография Maxim Boguk
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Maxim Boguk
Участник
maxxstormMaxim BogukЯ бы track_io_timing в базе бы включил для начала.
С очень высокой вероятностью у вас время уходит на случайный ввод-вывод на IOS.

--
Maxim Boguk
www.postgresql-consulting.ru

Включил:

Код: sql
1.
2.
3.
4.
5.
6.
7.
8.
9.
10.
11.
Aggregate  (cost=437042.55..437042.56 rows=1 width=4) (actual time=15401.903..15401.903 rows=1 loops=1)
   Output: count(id)
   Buffers: shared hit=5536251 read=14816
   I/O Timings: read=10339.508
   ->  Index Only Scan using san_material_pkey on public.san_material  (cost=0.43..401125.11 rows=14366978 width=4) (actual time=0.047..13713.557 rows=14451005 loops=1)
         Output: id
         Heap Fetches: 0
         Buffers: shared hit=5536251 read=14816
         I/O Timings: read=10339.508
 Planning time: 0.402 ms
 Execution time: 15401.989 ms



Ну вот вам и ответ.
Как я и говорил - время на IO уходит.
Скорее всего настройки seq_page_cost/random_page_cost/effective_cache_size не соответствуют реальности имеющейся дисковой системы.

А какой размер таблицы и размер индекса (san_material_pkey) у вас?
...
Рейтинг: 0 / 0
28.12.2016, 16:20
| Ответить | Цитировать | Написать  
Тяжелый запрос к связанным таблицам
    #39377045
maxxstorm
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
maxxstorm
Участник
Maxim BogukА какой размер таблицы и размер индекса (san_material_pkey) у вас?

Таблица 17гб, индекс 363мб
...
Рейтинг: 0 / 0
28.12.2016, 16:34
| Ответить | Цитировать | Написать  
Тяжелый запрос к связанным таблицам
    #39377077
qwwq
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
qwwq
Участник
Maxim BogukНу вот вам и ответ.


как бы помяхше , мммм...
на что именно ответ ?

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


 
как грицца -- два дебила -- это сила.
продолжайте, очень интересно
простите, если кого обидел, ага
...
Рейтинг: 0 / 0
28.12.2016, 17:01
| Ответить | Цитировать | Написать  
Тяжелый запрос к связанным таблицам
    #39377090
Фотография vyegorov
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
vyegorov
Участник
maxxstorm,

`effective_cache_size` какой у вас? и также какое кол-во RAM-а, шареных?
...
Рейтинг: 0 / 0
28.12.2016, 17:17
| Ответить | Цитировать | Написать  
Тяжелый запрос к связанным таблицам
    #39377091
maxxstorm
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
maxxstorm
Участник
vyegorovmaxxstorm,

`effective_cache_size` какой у вас? и также какое кол-во RAM-а, шареных?

effective_cache_size 15GB
RAM 32GB
shared_buffers 16GB
...
Рейтинг: 0 / 0
28.12.2016, 17:18
| Ответить | Цитировать | Написать  
Тяжелый запрос к связанным таблицам
    #39377125
Фотография vyegorov
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
vyegorov
Участник
maxxstorm,

А покажите вывод таких вот команд (для сравнения):
Код: sql
1.
2.
3.
4.
5.
6.
7.
8.
9.
10.
11.
12.
13.
RESET ALL;
SELECT name,setting,unit FROM pg_settings WHERE name ~ '^enable_|cache_size|cost$|d_buffers';
SELECT relname,relpages,reltuples::numeric,relkind
  FROM pg_class WHERE oid='accounts'::regclass AND relkind='r'
UNION ALL
SELECT relname,relpages,reltuples::numeric,relkind
  FROM pg_class WHERE oid IN (SELECT indexrelid FROM pg_index WHERE indrelid='accounts'::regclass) AND relkind='i';
EXPLAIN (analyze, buffers) SELECT count(id) FROM san_material;
SET enable_indexonlyscan TO off;
EXPLAIN (analyze, buffers) SELECT count(id) FROM san_material;
SET enable_indexscan TO off;
SET enable_bitmapscan TO off;
EXPLAIN (analyze, buffers) SELECT count(id) FROM san_material;
...
Рейтинг: 0 / 0
28.12.2016, 17:49
| Ответить | Цитировать | Написать  
Тяжелый запрос к связанным таблицам
    #39377392
maxxstorm
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
maxxstorm
Участник
vyegorovmaxxstorm,

А покажите вывод таких вот команд (для сравнения):
Код: sql
1.
2.
3.
4.
5.
6.
7.
8.
9.
10.
11.
12.
13.
RESET ALL;
SELECT name,setting,unit FROM pg_settings WHERE name ~ '^enable_|cache_size|cost$|d_buffers';
SELECT relname,relpages,reltuples::numeric,relkind
  FROM pg_class WHERE oid='san_material'::regclass AND relkind='r'
UNION ALL
SELECT relname,relpages,reltuples::numeric,relkind
  FROM pg_class WHERE oid IN (SELECT indexrelid FROM pg_index WHERE indrelid='san_material'::regclass) AND relkind='i';
EXPLAIN (analyze, buffers) SELECT count(id) FROM san_material;
SET enable_indexonlyscan TO off;
EXPLAIN (analyze, buffers) SELECT count(id) FROM san_material;
SET enable_indexscan TO off;
SET enable_bitmapscan TO off;
EXPLAIN (analyze, buffers) SELECT count(id) FROM san_material;



Код: sql
1.
2.
3.
4.
5.
6.
7.
8.
9.
10.
11.
12.
13.
14.
15.
16.
17.
18.
19.
20.
         name         | setting | unit 
----------------------+---------+------
 cpu_index_tuple_cost | 0.005   | 
 cpu_operator_cost    | 0.0025  | 
 cpu_tuple_cost       | 0.01    | 
 effective_cache_size | 1966080 | 8kB
 enable_bitmapscan    | on      | 
 enable_hashagg       | on      | 
 enable_hashjoin      | on      | 
 enable_indexonlyscan | on      | 
 enable_indexscan     | on      | 
 enable_material      | on      | 
 enable_mergejoin     | on      | 
 enable_nestloop      | on      | 
 enable_seqscan       | on      | 
 enable_sort          | on      | 
 enable_tidscan       | on      | 
 random_page_cost     | 4       | 
 seq_page_cost        | 1       | 
 shared_buffers       | 1280000 | 8kB



Код: sql
1.
2.
3.
4.
5.
6.
7.
8.
9.
10.
11.
                    relname                    | relpages | reltuples | relkind 
-----------------------------------------------+----------+-----------+---------
 san_material                                  |  2255193 |  14380600 | r
 san_material_elastic_sync_88524c38_uniq       |    43144 |  14451000 | i
 san_material_url_7862cf55_like                |   218468 |  14451000 | i
 san_material_url_key                          |   216152 |  14451000 | i
 san_material_detection_datetime_cb0a0120_uniq |    47959 |  14451000 | i
 san_material_9d090a40                         |    46624 |  14451000 | i
 san_material_id_2792252d_idx                  |    66012 |  14451000 | i
 san_material_pkey                             |    46405 |  14451000 | i
 san_material_source_id_id_ebe9d39e_uniq       |    46406 |  14451000 | i



Код: sql
1.
2.
3.
4.
5.
6.
7.
Aggregate  (cost=437281.72..437281.73 rows=1 width=4) (actual time=5312.353..5312.354 rows=1 loops=1)
   Buffers: shared hit=5551157
   ->  Index Only Scan using san_material_pkey on san_material  (cost=0.43..401330.11 rows=14380645 width=4) (actual time=0.141..3677.507 rows=14451005 loops=1)
         Heap Fetches: 0
         Buffers: shared hit=5551157
 Planning time: 0.743 ms
 Execution time: 5312.422 ms




Код: sql
1.
2.
3.
4.
5.
6.
Aggregate  (cost=2434951.06..2434951.07 rows=1 width=4) (actual time=625352.078..625352.078 rows=1 loops=1)
   Buffers: shared hit=1067818 read=1187375
   ->  Seq Scan on san_material  (cost=0.00..2398999.45 rows=14380645 width=4) (actual time=0.049..622995.201 rows=14451005 loops=1)
         Buffers: shared hit=1067818 read=1187375
 Planning time: 0.247 ms
 Execution time: 625352.169 ms




Код: sql
1.
2.
3.
4.
5.
6.
Aggregate  (cost=2434951.06..2434951.07 rows=1 width=4) (actual time=656661.732..656661.732 rows=1 loops=1)
   Buffers: shared hit=1067850 read=1187343
   ->  Seq Scan on san_material  (cost=0.00..2398999.45 rows=14380645 width=4) (actual time=0.137..654287.227 rows=14451005 loops=1)
         Buffers: shared hit=1067850 read=1187343
 Planning time: 0.277 ms
 Execution time: 656661.831 ms
...
Рейтинг: 0 / 0
29.12.2016, 09:01
| Ответить | Цитировать | Написать  
Тяжелый запрос к связанным таблицам
    #39377402
Фотография Maxim Boguk
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Maxim Boguk
Участник
maxxstorm,

Значит база правильно IOS выбирает у вас (он быстрее даже когда часть данных с диска читается).
seq scan сильно медленее у вас.

А для запроса с public.sources_article - это на том же оборудовании делалось? Или это другой физический сервер?
Так как сейчас мне кажется что сервер где "Сравниваю с аналогичным запросом из другой бд(размер 50гб):" он сильно другой по конфигурации.

--
Maxim Boguk
www.postgresql-consulting.ru
...
Рейтинг: 0 / 0
29.12.2016, 09:24
| Ответить | Цитировать | Написать  
Тяжелый запрос к связанным таблицам
    #39377425
qwwq
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
qwwq
Участник
Maxim Bogukmaxxstorm,

А для запроса с public.sources_article - это на том же оборудовании делалось? Или это другой физический сервер?
то, что сек-скан сосет -- показано выше на отдизейбленном индекс скане.

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

видно, что буферов на "моём" кампутере больше почти на порядок (чем на эталонном).
т.е. или записей на блок сильно меньше т.е. блоки пустые (что не так, если я правильно прочитал вывод), или записи (без тостов) сильно (почти на порядок) разной ширины. -- в итоге на в 1,7 раз отличающееся (по планам) число записей планируется в 20 раз больше костов (т.е., очевидно, блоков, т.к. ничего больше в последовательном чтении нет, а агрегат без дистинкта)

-- какие--то такие выводы о том, почему "таблички одинаковы" а косты (абстрактные, на момент планирования) разные
...
Рейтинг: 0 / 0
29.12.2016, 10:24
| Ответить | Цитировать | Написать  
Тяжелый запрос к связанным таблицам
    #39377549
Фотография vyegorov
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
vyegorov
Участник
maxxstorm,

Для полноты картины, приведите выхлоп тех же запросов для таблицы `sources_article`.
Я подозреваю, что эта таблице будет "уже", т.е. не будет разности в 50 раз между размером таблицы и её ПК.
...
Рейтинг: 0 / 0
29.12.2016, 12:41
| Ответить | Цитировать | Написать  
Тяжелый запрос к связанным таблицам
    #39377562
maxxstorm
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
maxxstorm
Участник
Вы были правы, таблица не та)
Очень похожи названия, смотрю в структуру одной, а зпрос делаю из другой

Код: sql
1.
2.
3.
4.
5.
6.
7.
8.
9.
10.
11.
12.
explain
(analyze, buffers, verbose)
select count(id) from sources_articlecontent

"Aggregate  (cost=258767.61..258767.62 rows=1 width=4) (actual time=19426.299..19426.299 rows=1 loops=1)"
"  Output: count(id)"
"  Buffers: shared hit=2621382 read=425920 dirtied=731 written=304"
"  ->  Index Only Scan using sources_articlecontent_pkey on public.sources_articlecontent  (cost=0.43..242531.79 rows=6494328 width=4) (actual time=196.300..18450.895 rows=6854658 loops=1)"
"        Output: id"
"        Heap Fetches: 855996"
"        Buffers: shared hit=2621382 read=425920 dirtied=731 written=304"
"Total runtime: 19426.390 ms"
...
Рейтинг: 0 / 0
29.12.2016, 12:48
| Ответить | Цитировать | Написать  
142 сообщений из 142, показаны все 6 страниц
  все  
Форумы / PostgreSQL [игнор отключен] [закрыт для гостей] / Тяжелый запрос к связанным таблицам
Найденые пользователи ...
Разблокировать пользователей ...
Читали тему (0):
Читали форум (0):
Пользователи онлайн (0):
Польз. соглашение
NoSQL.ru       Новости, Чат       Фотоальбом: Участники, Встречи
Закрыть
start [/forum/search_topic.php?author=%D0%BF%D0%B5%D1%80%D0%B5%D1%81%D1%82%D0%B0%D0%B2%D1%8C&author_mode=last_posts&do_search=1]:
 0ms
get settings:
 9ms
get forum list:
 12ms
get settings:
 10ms
get forum list:
 14ms
check forum access:
 4ms
check topic access:
 4ms
track hit:
 64ms
get topic data:
 12ms
get forum data:
 2ms
get page messages:
 145ms
get tp. blocked users:
 2ms
others: 1870ms
total:  2148ms
созд. / загр.: 2148ms
x
x
Закрыть


Просмотр
0 / 0
Close
Debug Console [Select Text]