ReSQL.ru
     
powered by simpleCommunicator - 2.0.54     © 2025 Programmizd 02
Мобильная версия    Контакт    Правила    FAQ    Помощь
Целевая тема:
Создать новую тему:
Автор:
Закрыть
Цитировать
Доб. в избранное | Игнор. тему | Прикреп. тему | Пометить прочит. / непрочит. | Фильтр
Форумы / PostgreSQL [игнор отключен] [закрыт для гостей] / Тяжелый запрос к связанным таблицам
25 сообщений из 142, страница 2 из 6
  2  все
Тяжелый запрос к связанным таблицам
    #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
| Ответить | Цитировать | Написать  
25 сообщений из 142, страница 2 из 6
  2  все
Форумы / PostgreSQL [игнор отключен] [закрыт для гостей] / Тяжелый запрос к связанным таблицам
Найденые пользователи ...
Разблокировать пользователей ...
Читали тему (0):
Читали форум (0):
Пользователи онлайн (0):
Польз. соглашение
NoSQL.ru       Новости, Чат       Фотоальбом: Участники, Встречи
Закрыть
start [/forum/topic.php?fid=53&msg=39329953&tid=1996781]:
 0ms
get settings:
 10ms
get forum list:
 14ms
check forum access:
 4ms
check topic access:
 4ms
track hit:
 96ms
get topic data:
 9ms
get forum data:
 2ms
get page messages:
 53ms
get tp. blocked users:
 1ms
others: 375ms
total:  568ms
созд. / загр.: 568ms
x
x
Закрыть


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