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


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