Я некоторое время работал над тем, чтобы выяснить, как заставить планирование запросов действовать немного умнее, но безуспешно. Я возился с work_mem и друзьями, много запускал vacumm analyze
и пытался изменить запрос с помощью order by
. Я включил 3 прогона одного и того же запроса с разными смещениями. У меня сложилось впечатление, что этот запрос не так эффективен, как мог бы быть. Какие-нибудь мысли?
На всякий случай, если это не бросается в глаза — единственное изменение между следующими запросами — это offset
bloomapi=# explain analyze SELECT * FROM npis WHERE provider_last_name_legal_name = 'THOMPSON' offset 250 limit 10;
QUERY PLAN
---------------------------------------------------------------------------------------------------------------------------------------------------------------------
Limit (cost=965.13..998.97 rows=10 width=2589) (actual time=568.458..577.507 rows=10 loops=1)
-> Bitmap Heap Scan on npis (cost=119.15..20382.11 rows=5988 width=2589) (actual time=58.140..577.027 rows=260 loops=1)
Recheck Cond: ((provider_last_name_legal_name)::text = 'THOMPSON'::text)
-> Bitmap Index Scan on npis_temp_provider_last_name_legal_name_idx1 (cost=0.00..117.65 rows=5988 width=0) (actual time=36.819..36.819 rows=5423 loops=1)
Index Cond: ((provider_last_name_legal_name)::text = 'THOMPSON'::text)
Total runtime: 578.301 ms
(6 rows)
bloomapi=# explain analyze SELECT * FROM npis WHERE provider_last_name_legal_name = 'THOMPSON' offset 100 limit 10;
QUERY PLAN
---------------------------------------------------------------------------------------------------------------------------------------------------------------------
Limit (cost=395.81..435.40 rows=10 width=2589) (actual time=0.397..0.440 rows=10 loops=1)
-> Index Scan using npis_temp_provider_last_name_legal_name_idx1 on npis (cost=0.00..23701.38 rows=5988 width=2589) (actual time=0.063..0.293 rows=110 loops=1)
Index Cond: ((provider_last_name_legal_name)::text = 'THOMPSON'::text)
Total runtime: 0.952 ms
(4 rows)
bloomapi=# explain analyze SELECT * FROM npis WHERE provider_last_name_legal_name = 'THOMPSON' offset 4100 limit 10;
QUERY PLAN
-------------------------------------------------------------------------------------------------------------------------------------------------------------------
Limit (cost=13993.25..14027.09 rows=10 width=2589) (actual time=9356.723..9400.021 rows=10 loops=1)
-> Bitmap Heap Scan on npis (cost=119.15..20382.11 rows=5988 width=2589) (actual time=2.968..9393.327 rows=4110 loops=1)
Recheck Cond: ((provider_last_name_legal_name)::text = 'THOMPSON'::text)
-> Bitmap Index Scan on npis_temp_provider_last_name_legal_name_idx1 (cost=0.00..117.65 rows=5988 width=0) (actual time=1.943..1.943 rows=5423 loops=1)
Index Cond: ((provider_last_name_legal_name)::text = 'THOMPSON'::text)
Total runtime: 9400.426 ms
(6 rows)
Некоторые важные примечания:
- Я очистил разделяемую память в системе перед выполнением первого запроса, поэтому на некоторое фактическое время первого запроса, вероятно, повлияла загрузка индекса.
- данные широкие и разреженные - 329 столбцов, многие из которых представляют собой пустые символы (30)
- данные практически доступны только для чтения — они обновляются еще 15 000 строк раз в неделю.
- производительность этих запросов была на самом деле выше для тех же запросов, когда были настройки БД по умолчанию, поставляемые с Ubuntu ppa (в данный момент у меня нет этих планов запросов, но я мог бы покопаться в них, если в противном случае ничего очевидного не выскочит). Параметры, которые были изменены по сравнению со значениями по умолчанию: shared_buffers = 256 МБ, Effective_cache_size = 512 МБ, checkpoint_segments = 64, checkpoint_completion_target = 0,9, default_statistics_target = 500
- фактические данные около 4 миллионов строк / 1,29 ГБ для таблицы отдельно, provider_last_name_legal_name индексируется btree - размер индекса составляет 95 МБ. около 3/4 строк имеют ненулевое значение в этом столбце, а вся таблица имеет 488 тыс. различных значений.
random_page_cost
на более низкое значение (~ 1,5)? Кстати: какие у вас настройки дляwork_mem
Плюс:effective_cache_size = 512M
кажется довольно низким; ваша таблица размером 1,3 ГБ должна (почти) соответствовать ядру, по крайней мере, индексу. - person wildplasser   schedule 15.09.2013provider_last_name_legal_name
? Наконец: размер 1.2G/4Mrows:= 300 байт байт/строка, что кажется немного большим. Кстати: как уместить 329 столбцов в 300 байт? - person wildplasser   schedule 15.09.2013LIMIT
. После этого: проверка согласованности и, возможно, хранилище данных. - person wildplasser   schedule 18.09.2013