Pergunta
Como posso tornar a consulta descrita neste post mais rápida, em particular fazendo o PostgreSQL usando a RAM disponível? - Note que eu tentei configurar o effective_cache_size e shared_buffers apropriadamente. Veja abaixo.
Plano de fundo
Eu tenho que juntar regularmente uma tabela de ~ 260 milhões de linhas (coreg_master) com novos dados que chegam. Eu particionei a tabela para permitir que cada partição se encaixasse na RAM. Eu tenho índices apropriados configurados também, é claro. No entanto, ao unir as tabelas particionadas separadamente contra as outras (muito menores) tabelas, ele está fazendo IO totalmente aleatório no disco. Isso se deve a uma varredura de índice de loop aninhado na tabela grande, o que é muito lento, já que não temos uma grande configuração de disco.
Eu esperaria que ele usasse toda a RAM disponível para armazenar em cache a grande tabela particionada, o que eu entendo que deveria ser feito pelo próprio kernel Linux / sistema de arquivos. Mas ainda não carrega a tabela na memória RAM, embora ela se encaixe. Eu acho que é porque o padrão de acesso não é seqüencial e, portanto, não aciona o cache? Eu não faço ideia. Plano de consulta e parâmetros de configuração abaixo.
Estrutura da tabela
Esta é uma das partições do coreg_master, minha grande mesa. As tabelas particionadas são nomeadas coreg_a, coreg_b, etc.
\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
Segue-se uma partição da tabela appendable_24, um exemplo da tabela a ser juntada ao coreg_master. Ele também é particionado da mesma forma que o coreg_master, então, na verdade, o coreg_a é associado ao appendable_24_a, etc, um de cada vez.
\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
Consulta e amp; EXPLAIN ANALYZE
Abaixo está a saída de explain analyze para a menor das junções (as tabelas são particionadas de acordo com a primeira letra da coluna first_name), já que não leva mais tempo. No entanto, o plano de consulta é o mesmo para todas as junções em cada partição, por isso deve ser representativo das junções maiores também (observe, eu fiz ANALYZE das tabelas e o tempo total foi realmente 20 segundos, mas mais rápido aqui como o resultado foi armazenado em cache):
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)
Algumas estatísticas, parâmetros de configuração e outros dados
- Versão do PostgreSQL: 8.4.4
- OS: CentOS release 5.5 (Final)
- Sistema de arquivos: ext3
- RAM total disponível: 8 GB
- shared_buffers = 2 GB
- effective_cache_size = 7200MB
- configuração completa do tempo de execução por meio do
show all : link
- maior tamanho da tabela de coreg_j particionada (~ 4900MB
- número correspondente de linhas: ~ 32 milhões
- tamanho do índice correspondente (first_name, last_name): ~ 1000MB
- tamanho da segunda tabela particionada (appendable_24_j): ~ 1800kB
- linhas em appendable_24_j: ~ 25.000