Плохая производительность для большого запроса соединения в PostgreSQL 8.4.4, несмотря на достаточно памяти для полного кеширования

Вопрос

Как сделать запрос, описанный в этом посте, быстрее, в частности, сделав PostgreSQL с использованием доступной оперативной памяти? - Обратите внимание, что я попытался соответствующим образом настроить effective_cache_size и shared_buffers. Увидеть ниже.

Задний план

Я должен регулярно присоединяться к таблице с ~ 260 миллионами строк (coreg_master) с новыми входящими данными. Я разбил таблицу на разделы, чтобы каждый раздел поместился в ОЗУ. Конечно, у меня тоже есть соответствующие индексы. Однако при объединении секционированных таблиц отдельно с другими (гораздо меньшими) таблицами он выполняет полностью случайный ввод-вывод на диске. Это происходит из-за сканирования индекса вложенного цикла в большой таблице, что очень медленно, поскольку у нас нет хорошей настройки диска.

Я ожидал, что вместо этого он будет использовать всю доступную оперативную память для кеширования большой многораздельной таблицы, что, как я понимаю, должно выполняться самим ядром / файловой системой Linux. Но он все равно не загружает таблицу в оперативную память, хотя и поместится. Я думаю, это потому, что шаблон доступа не является последовательным и, следовательно, не запускает кеширование? Я понятия не имею. План запроса и параметры конфигурации ниже.

Структура таблицы

Это один из разделов coreg_master, моей большой таблицы. Секционированные таблицы называются coreg_a, coreg_b и т. Д.

\d coreg_a
                                   Table "public.coreg_a"
   Column    |       Type        |                         Modifiers                         
-------------+-------------------+-----------------------------------------------------------
 id          | integer           | not null default nextval('coreg_master_id_seq'::regclass)
 first_name  | character varying | 
 last_name   | character varying | 
 phone       | character varying | 
 city        | character varying | 
 zip         | integer           | 
 address     | character varying | 
 dob         | date              | 
 ip          | character varying | 
 source      | character varying | 
 gender      | character varying | 
 state       | character varying | 
 record_date | date              | 
 email       | character varying | 
Indexes:
    "coreg_a_name" btree (lower(first_name::text), lower(last_name::text))
Check constraints:
    "coreg_a_first_name_check" CHECK (first_name::text >= 'a'::text AND first_name::text < 'b'::text)
Inherits: coreg_master

Ниже приведен раздел таблицы appendable_24, пример таблицы, соединенной с coreg_master. Он также разбит на разделы так же, как coreg_master, поэтому на самом деле coreg_a объединяется с appendable_24_a и т. Д. По одному.

\d appendable_24_a
       Table "public.appendable_24_a"
   Column   |       Type        | Modifiers 
------------+-------------------+-----------
 line_num   | integer           | not null
 first_name | character varying | 
 last_name  | character varying | 
 address    | character varying | 
 state      | character varying | 
 zip        | integer           | 
Indexes:
    "appendable_24_a_name_index" btree (lower(first_name::text), lower(last_name::text))
Check constraints:
    "appendable_24_a_first_name_check" CHECK (first_name::text >= 'a'::text AND first_name::text < 'b'::text)
Inherits: appendable_24

Запрос и EXPLAIN ANALYZE

Ниже приведен результат анализа объяснения для наименьшего из объединений (таблицы разбиваются на разделы в соответствии с первой буквой столбца first_name), так как это не требует времени. Однако план запроса один и тот же для всех объединений в каждом разделе, поэтому он также должен быть репрезентативным для более крупных объединений (обратите внимание, я сделал ANALYZE таблицы, а общее время фактически составило 20 секунд, но здесь быстрее, так как результат был кэширован):

explain analyze SELECT
          coreg_x.phone,
          coreg_x.email,
          coreg_x.record_date,
          appendable_24_x.line_num
        FROM appendable_24_x INNER JOIN coreg_x ON
          lower(appendable_24_x.first_name) = lower(coreg_x.first_name) AND
          lower(appendable_24_x.last_name) = lower(coreg_x.last_name) AND
          (coreg_x.phone IS NOT NULL OR coreg_x.email IS NOT NULL) AND
          similarity(lower(appendable_24_x.address), lower(coreg_x.address)) > 0.7
      ; 
                                                                                       QUE
RY PLAN 

------------------------------------------------------------------------------------------
------------------------------------------------------------------------------------------
-----
 Nested Loop  (cost=0.01..640.49 rows=875 width=39) (actual time=9.990..53.839 rows=29 loo
ps=1)
   Join Filter: (similarity(lower((appendable_24_x.address)::text), lower((coreg_x.address
)::text)) > 0.7::double precision)
   ->  Seq Scan on appendable_24_x  (cost=0.00..1.80 rows=80 width=34) (actual time=0.009.
.0.111 rows=80 loops=1)
   ->  Index Scan using coreg_x_name on coreg_x  (cost=0.01..7.95 rows=1 width=64) (actual
 time=0.024..0.137 rows=44 loops=80)
         Index Cond: ((lower((coreg_x.first_name)::text) = lower((appendable_24_x.first_na
me)::text)) AND (lower((coreg_x.last_name)::text) = lower((appendable_24_x.last_name)::tex
t)))
         Filter: ((coreg_x.phone IS NOT NULL) OR (coreg_x.email IS NOT NULL))
 Total runtime: 53.950 ms
(7 rows)

Некоторая статистика, параметры конфигурации и другие данные

• Версия PostgreSQL: 8.4.4
• ОС: CentOS версии 5.5 (окончательная)
• Файловая система: ext3
• Общий объем доступной оперативной памяти: 8 ГБ
• shared_buffers = 2 ГБ
• Effective_cache_size = 7200 МБ
• полная конфигурация среды выполнения через show all: http://pastie.org/1159746
• размер самой большой секционированной таблицы coreg (coreg_j): ~ 4900 МБ
• соответствующее количество строк: ~ 32 миллиона
• соответствующий размер индекса (first_name, last_name): ~ 1000MB
• размер второй секционированной таблицы (appendable_24_j): ~ 1800 КБ
• строк в appendable_24_j: ~ 25000