ReSQL.ru
     
powered by simpleCommunicator - 2.0.60     © 2026 Programmizd 02
Мобильная версия    Контакт    Правила    FAQ    Помощь
Целевая тема:
Создать новую тему:
Автор:
Закрыть
Цитировать
Доб. в избранное | Игнор. тему | Прикреп. тему | Пометить прочит. / непрочит. | Фильтр
Форумы / PostgreSQL [игнор отключен] [закрыт для гостей] / Большая таблица, оптимизация
9 сообщений из 9, страница 1 из 1
Большая таблица, оптимизация
    #35642934
golden13
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
golden13
Гость
Здравствуйте,
Помогите с оптимизацией
Есть очень большая таблица (115 млн. записей) :
Код: plaintext
1.
2.
3.
4.
5.
6.
7.
8.
9.
10.
11.
12.
13.
14.
15.
16.
CREATE TABLE res_pages_temp
(
  page_id bigint NOT NULL,
  resource_id bigint NOT NULL,
  ex double precision,
  word_id bigint NOT NULL,
  inp double precision,
  "name" character varying( 255 ),
  crc bigint,
  "encoding" smallint,
  lang integer,
  "type" integer,
  url character varying( 1000 ),
  links_count bigint
)
WITH (OIDS=FALSE);

С одним индексом:
Код: plaintext
1.
2.
3.
4.
5.
CREATE INDEX res_pages_temp_w_r
  ON res_pages_temp
  USING btree
  (word_id, resource_id);
ALTER TABLE res_pages_temp CLUSTER ON res_pages_temp_w_r;


Приложение перелопачивает всю таблицу, делая однотипные запросы:
Код: plaintext
1.
2.
3.
4.
SELECT page_id, resource_id, ex, InP 
FROM res_pages_temp 
WHERE word_id=....
ORDER BY resource_id LIMIT  10000  OFFSET .....;

На некоторых word_id наблюдается замедление. Вот например:
Код: plaintext
1.
2.
3.
4.
5.
6.
7.
8.
9.
10.
11.
12.
13.
14.
15.
SET enable_bitmapscan TO on;
EXPLAIN ANALYZE SELECT page_id, resource_id, ex, InP 
FROM res_pages_temp 
WHERE word_id= 109681 
ORDER BY resource_id LIMIT  10000  OFFSET  20000 ;

"Limit  (cost=36754.09..36754.09 rows=1 width=32) (actual time=21565.082..21572.821 rows=2396 loops=1)"
"  ->  Sort  (cost=36730.85..36754.09 rows=9297 width=32) (actual time=21518.123..21546.956 rows=22396 loops=1)"
"        Sort Key: resource_id"
"        Sort Method:  quicksort  Memory: 2087kB"
"        ->  Bitmap Heap Scan on res_pages_temp  (cost=234.07..36118.06 rows=9297 width=32) (actual time=38.360..21466.504 rows=22396 loops=1)"
"              Recheck Cond: (word_id = 109681)"
"              ->  Bitmap Index Scan on res_pages_temp_w_r  (cost=0.00..231.74 rows=9297 width=0) (actual time=27.328..27.328 rows=22396 loops=1)"
"                    Index Cond: (word_id = 109681)"
"Total runtime: 21575.663 ms"

Если enable_bitmapscan отключить, то получаем следующее:
Код: plaintext
1.
2.
3.
4.
5.
6.
7.
8.
9.
10.
SET enable_bitmapscan TO off;
EXPLAIN ANALYZE SELECT page_id, resource_id, ex, InP 
FROM res_pages_temp 
WHERE word_id= 109681  
ORDER BY resource_id LIMIT  10000  OFFSET  20000 ;

"Limit  (cost=37467.10..37467.10 rows=1 width=32) (actual time=332.435..374.405 rows=2396 loops=1)"
"  ->  Index Scan using res_pages_temp_w_r on res_pages_temp  (cost=0.00..37467.10 rows=9297 width=32) (actual time=0.101..348.959 rows=22396 loops=1)"
"        Index Cond: (word_id = 109681)"
"Total runtime: 377.018 ms"

Вопросы:
1. Видно, что планировщик ошибается. Но так ли это всегда? То-есть буду ли я прав, если буду выполнять SET enable_bitmapscan TO off в приложении принудительно, для всех запросов такого типа?
2. Как можно всё убыстрить? Просто, таких запросов приложение выполняет очень много подряд и общее время выполнения приложения, мягко говоря, не очень маленькое.
3. Когда выполняю данные запросы с Explain Analyze в PgAdmin часто скорость их выполнения скачет. Причём то она такая как приведена выше, то по 400ms как с включённым enable_bitmapscan так и с выключенным, с чем это может быть связанно?
4. Не страшно ли, что в таблице отсуствует primary key ?


Из таблицы данные только читаются. Никаких инсертов и прочего. Постгрес в этот момент стоит и отдыхает. Только данное приложение работает.
После создания таблицы делался VACUUM FULL ANALYZE и REINDEX

Заранее спасибо за помощь.
...
Рейтинг: 0 / 0
10.11.2008, 03:16
| Ответить | Цитировать | Написать  
Большая таблица, оптимизация
    #35642942
Vladimir Sitnikov
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Vladimir Sitnikov
Гость
Чему равно значение work_mem?
Предлагаю поднять до 20'480 и посмотреть как будет работать bitmapscan ( 115млн записей/8 = 14 375 000 байт для идеального bitmap scan'а).

чем меньше неиспользуемых индексов в таблице, тем лучше -- базе проще работать.
...
Рейтинг: 0 / 0
10.11.2008, 04:58
| Ответить | Цитировать | Написать  
Большая таблица, оптимизация
    #35643259
golden13
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
golden13
Гость
Vladimir Sitnikov,

shared_buffers = 24MB
temp_buffers = 8MB
work_mem = 10MB
maintenance_work_mem = 16MB
max_fsm_pages = 153600
max_fsm_relations = 1000


Postgres 8.3
Железо не самое слабое: AMD Athlon 64 X2 Dual Core 4200+, RAM 2GB, винты SATA, 3 штуки по 80 GB
OS: Ubuntu 8.04
...
Рейтинг: 0 / 0
10.11.2008, 10:46
| Ответить | Цитировать | Написать  
Большая таблица, оптимизация
    #35643463
Vladimir Sitnikov
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Vladimir Sitnikov
Гость
golden13work_mem = 10MB
По-моему, увеличение до 20Мб должно помочь. Дело в том, что bitmap scan выполняется по-разному в зависимости от того помещается ли scan в work_mem или нет.
...
Рейтинг: 0 / 0
10.11.2008, 11:43
| Ответить | Цитировать | Написать  
Большая таблица, оптимизация
    #35643600
golden13
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
golden13
Гость
Vladimir Sitnikov,

После установки work_mem = 20MB

Код: plaintext
1.
2.
3.
4.
5.
6.
7.
8.
9.
10.
11.
12.
13.
14.
15.
SET enable_bitmapscan TO on;
EXPLAIN ANALYZE SELECT page_id, resource_id, ex, InP 
FROM res_pages_temp 
WHERE word_id= 109681 
ORDER BY resource_id LIMIT  10000  OFFSET  20000 ;

"Limit  (cost=36754.09..36754.09 rows=1 width=32) (actual time=488.605..496.318 rows=2396 loops=1)"
"  ->  Sort  (cost=36730.85..36754.09 rows=9297 width=32) (actual time=442.888..471.443 rows=22396 loops=1)"
"        Sort Key: resource_id"
"        Sort Method:  quicksort  Memory: 2087kB"
"        ->  Bitmap Heap Scan on res_pages_temp  (cost=234.07..36118.06 rows=9297 width=32) (actual time=25.232..393.907 rows=22396 loops=1)"
"              Recheck Cond: (word_id = 109681)"
"              ->  Bitmap Index Scan on res_pages_temp_w_r  (cost=0.00..231.74 rows=9297 width=0) (actual time=14.402..14.402 rows=22396 loops=1)"
"                    Index Cond: (word_id = 109681)"
"Total runtime: 499.126 ms"

Уже похоже на правду.
Что можно ещё сделать ?
...
Рейтинг: 0 / 0
10.11.2008, 12:24
| Ответить | Цитировать | Написать  
Большая таблица, оптимизация
    #35643696
golden13
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
golden13
Гость
Однако, для запроса с другим word_id всё по прежнему :(
Понятно, что тут больше rows.
Код: plaintext
1.
2.
3.
4.
5.
6.
7.
8.
9.
10.
11.
12.
13.
14.
15.
SET enable_bitmapscan TO off;
EXPLAIN ANALYZE SELECT page_id, resource_id, ex, InP 
FROM res_pages_temp 
WHERE word_id= 107142 
ORDER BY resource_id LIMIT  10000  OFFSET  20000 ;

"Limit  (cost=36754.09..36754.09 rows=1 width=32) (actual time=61561.836..61597.752 rows=10000 loops=1)"
"  ->  Sort  (cost=36730.85..36754.09 rows=9297 width=32) (actual time=61513.819..61554.489 rows=30000 loops=1)"
"        Sort Key: resource_id"
"        Sort Method:  top-N heapsort  Memory: 3134kB"
"        ->  Bitmap Heap Scan on res_pages_temp  (cost=234.07..36118.06 rows=9297 width=32) (actual time=215.831..61199.668 rows=100140 loops=1)"
"              Recheck Cond: (word_id = 107142)"
"              ->  Bitmap Index Scan on res_pages_temp_w_r  (cost=0.00..231.74 rows=9297 width=0) (actual time=116.868..116.868 rows=100140 loops=1)"
"                    Index Cond: (word_id = 107142)"
"Total runtime: 61608.807 ms"

Код: plaintext
1.
2.
3.
4.
5.
6.
7.
8.
9.
10.
SET enable_bitmapscan TO off;
EXPLAIN ANALYZE SELECT page_id, resource_id, ex, InP 
FROM res_pages_temp 
WHERE word_id= 107142 
ORDER BY resource_id LIMIT  10000  OFFSET  20000 ;

"Limit  (cost=37467.10..37467.10 rows=1 width=32) (actual time=325.255..497.085 rows=10000 loops=1)"
"  ->  Index Scan using res_pages_temp_w_r on res_pages_temp  (cost=0.00..37467.10 rows=9297 width=32) (actual time=0.107..455.815 rows=30000 loops=1)"
"        Index Cond: (word_id = 107142)"
"Total runtime: 507.775 ms"

Вопросы всё теже. Что можете посоветовать? Может для такой большой таблицы имеет смысл использовать Partitioning?
...
Рейтинг: 0 / 0
10.11.2008, 12:50
| Ответить | Цитировать | Написать  
Большая таблица, оптимизация
    #35644281
LeXa NalBat
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
LeXa NalBat
Участник
golden13-> Bitmap Heap Scan on res_pages_temp (actual time=..21466.504 rows=22396)
-> Bitmap Index Scan on res_pages_temp_w_r (actual time=..27.328 rows=22396)поиск по индексу проходит быстро, а последующий выбор строк из таблицы тормозит. может быть из-за того, что эти 20 000 строк находятся на разных страницах.

попробуйте кластеризовать таблицу res_pages_temp по индексу res_pages_temp_w_r.

golden13Если enable_bitmapscan отключить, то получаем следующее:
"Total runtime: 377.018 ms"

3. Когда выполняю данные запросы с Explain Analyze в PgAdmin часто скорость их выполнения скачет. Причём то она такая как приведена выше, то по 400ms как с включённым enable_bitmapscan так и с выключенным, с чем это может быть связанно?может быть время выполнения первого запроса с некоторым ограничением word_id=12345 (не важно по какому плану) медленное, а последующие с этим же word_id=12345 быстрые из-за кэширования страниц жесткого диска в память постгресом или операционной системой.

golden131. Видно, что планировщик ошибается. Но так ли это всегда? То-есть буду ли я прав, если буду выполнять SET enable_bitmapscan TO off в приложении принудительно, для всех запросов такого типа?имхо, можно это сделать, для этого и нужен set enable_*.

golden132. Как можно всё убыстрить? Просто, таких запросов приложение выполняет очень много подряд и общее время выполнения приложения, мягко говоря, не очень маленькое.как я уже написал, попробуйте кластеризовтаь таблицу.

можно убрать offset, обрабатывать в приложении наборы большего объема, чем по 10 000 строк. потому что при выборке с offset-ом постгрес все равно читает пропускаемые строки из индекса и из таблицы, только не возвращает их. получается одна и та же работа делается несколько раз.

golden134. Не страшно ли, что в таблице отсуствует primary key ?этого мы не боимся! :)
...
Рейтинг: 0 / 0
10.11.2008, 15:32
| Ответить | Цитировать | Написать  
Большая таблица, оптимизация
    #35645049
Vladimir Sitnikov
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Vladimir Sitnikov
Гость
LeXa NalBatgolden13-> Bitmap Heap Scan on res_pages_temp (actual time=..21466.504 rows=22396)
-> Bitmap Index Scan on res_pages_temp_w_r (actual time=..27.328 rows=22396)поиск по индексу проходит быстро, а последующий выбор строк из таблицы тормозит. может быть из-за того, что эти 20 000 строк находятся на разных страницах.Как раз здесь это предположение неверно.
В обоих случаях (и index scan и index bitmap scan для word_id=109681 прочитали по 22396 записей). Проблемным местом было именно recheck (обратите внимание, что и для index scan'а пришлось проверить эти 22396 записей по таблице на предмет их существования)

LeXa NalBatпопробуйте кластеризовать таблицу res_pages_temp по индексу res_pages_temp_w_r.+1 Очень правильная мысль.

golden13Причём то она такая как приведена выше, то по 400ms как с включённым enable_bitmapscan так и с выключенным, с чем это может быть связанно?может, на сервере крутится ещё что-нибудь кроме Pg? Покажите результат команды free

LeXa NalBatgolden131. Видно, что планировщик ошибается. Но так ли это всегда? То-есть буду ли я прав, если буду выполнять SET enable_bitmapscan TO off в приложении принудительно, для всех запросов такого типа?имхо, можно это сделать, для этого и нужен set enable_*.set enable_* нужно для того, чтобы можно было узнать насколько сильно планировщик ошибается и зарепортить багу. Если довольствоваться включением/выключением, то никакого прогресса не будет.

А попробуйте, пожалуйста, такой запрос:
Код: plaintext
1.
2.
3.
4.
5.
6.
SET enable_bitmapscan TO off;

EXPLAIN ANALYZE SELECT page_id, resource_id, ex, InP 
FROM res_pages_temp 
WHERE word_id= 107142 
ORDER BY resource_id LIMIT  10000  OFFSET  90000 ;
...
Рейтинг: 0 / 0
10.11.2008, 20:00
| Ответить | Цитировать | Написать  
Большая таблица, оптимизация
    #35645323
gray hemp
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
gray hemp
Участник
golden13, здравствуйте.

Загвоздка в использовании OFFSET с большим значением. Старайтесь такого избегать. Проблема и решение для вашей ситуации описаны тут .
...
Рейтинг: 0 / 0
11.11.2008, 00:03
| Ответить | Цитировать | Написать  
9 сообщений из 9, страница 1 из 1
Форумы / PostgreSQL [игнор отключен] [закрыт для гостей] / Большая таблица, оптимизация
Найденые пользователи ...
Разблокировать пользователей ...
Читали тему (0):
Читали форум (0):
Пользователи онлайн (0):
x
x
Закрыть


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