Por favor, estou com um problema em uma consulta do meu Banco de Dados e
preciso da ajuda de vocês.
- Ambiente:
PostgreSQL 9.3.6
S.O. Debian 3.2.63
Realizo replicação com Slony.
Estrutura do Banco:
CREATE TABLE cpf_resumo
(
cpfid integer NOT NULL,
cpf character varying(14),
nome character varying(250),
nascimento date,
nome_mae character varying(250),
cidade character varying(120),
estado character varying(2),
cep character varying(10),
regiao_fiscal text,
data_atualizacao timestamp without time zone,
CONSTRAINT _cpf_resumo_new_pkey PRIMARY KEY (cpfid)
);
CREATE INDEX idx_cpf_resumo_nome_nascimento
ON cpf_resumo
USING btree
(fnc_retira_preposicao(nome) COLLATE pg_catalog."default"
varchar_pattern_ops, nascimento);
CREATE INDEX idx_cpf_resumo_regiao_fiscal
ON cpf_resumo
USING btree
(fnc_retira_preposicao(nome) COLLATE pg_catalog."default"
varchar_pattern_ops, regiao_fiscal COLLATE pg_catalog."default"
varchar_pattern_ops);
CREATE INDEX idx_cpf_resumo_endereco
ON cpf_resumo
USING btree
(fnc_retira_preposicao(nome) COLLATE pg_catalog."default"
varchar_pattern_ops, estado COLLATE pg_catalog."default"
varchar_pattern_ops, cidade COLLATE pg_catalog."default"
varchar_pattern_ops);
Quantidade de Tuplas na tabela:
185 Milhões
*Problema:*
- O planejador parou de utilizar o Index para realizar o SELECT, porém
apenas no Banco Master, no Slave realiza com Index, vejam o EXPLAIN:
Query:
EXPLAIN ANALYZE
SELECT
regiao_fiscal, COUNT(*) AS Quantidade
FROM
cpf_resumo
WHERE
fnc_retira_preposicao(nome) LIKE fnc_retira_preposicao('JOSE
ROBERTO % SILVA%')
GROUP BY
regiao_fiscal
ORDER BY
regiao_fiscal;
*EXPLAIN MASTER:*
SELECT regiao_fiscal, COUNT(*) AS Quantidade
FROM cpf_resumo WHERE fnc_retira_preposicao(nome) LIKE
fnc_retira_preposicao('JOSE ROBERTO % SILVA%') GROUP BY regiao_fiscal ORDER
BY regiao_fiscal;
Sort (cost=107979.55..107979.55 rows=1 width=2) (actual
time=123115.588..123115.594 rows=10 loops=1)
Sort Key: regiao_fiscal
Sort Method: quicksort Memory: 25kB
-> HashAggregate (cost=107979.53..107979.54 rows=1 width=2) (actual
time=123115.557..123115.565 rows=10 loops=1)
->* Bitmap Heap Scan *on cpf_resumo (cost=1069.81..107886.60
rows=18585 width=2) (actual time=128.899..123089.087 rows=14805 loops=1)
Filter: ((fnc_retira_preposicao(nome))::text ~~ 'JOSE ROBERTO
% SILVA%'::text)
Rows Removed by Filter: 9438155
-> Bitmap Index Scan on idx_cpf_resumo_regiao_fiscal
(cost=0.00..1065.16 rows=50209 width=0) (actual time=114.421..114.421
rows=189685 loops=1)
Index Cond: (((fnc_retira_preposicao(nome))::text ~>=~
'JOSE ROBERTO '::text) AND ((fnc_retira_preposicao(nome))::text ~<~ 'JOSE
ROBERTO!'::text))
Total runtime:* 123 115.676 ms*
EXPLAIN SLAVE:
SELECT regiao_fiscal, COUNT(*) AS Quantidade
FROM cpf_resumo WHERE fnc_retira_preposicao(nome) LIKE
fnc_retira_preposicao('JOSE ROBERTO % SILVA%') GROUP BY regiao_fiscal ORDER
BY regiao_fiscal;
Sort (cost=99.59..99.60 rows=1 width=2) (actual time=3538.941..3538.947
rows=10 loops=1)
Sort Key: regiao_fiscal
Sort Method: quicksort Memory: 25kB
-> HashAggregate (cost=99.57..99.58 rows=1 width=2) (actual
time=3538.909..3538.915 rows=10 loops=1)
-> * Index Scan using idx_cpf_resumo_regiao_fiscal* on cpf_resumo
(cost=1.07..5.34 rows=18846 width=2) (actual time=0.348..3509.038
rows=14805 loops=1)
Index Cond: (((fnc_retira_preposicao(nome))::text ~>=~ 'JOSE
ROBERTO '::text) AND ((fnc_retira_preposicao(nome))::text ~<~ 'JOSE
ROBERTO!'::text))
Filter: ((fnc_retira_preposicao(nome))::text ~~ 'JOSE ROBERTO
% SILVA%'::text)
Rows Removed by Filter: 174880
Total runtime: *3 539.004 ms*
Pesquisando encontre [1] onde falam para utilizar o *random_page_cost = 1 *e
de fator quando utilizo este parâmetro o planejador passa a utilizar o
Index, igual ao Slave.
*Dúvidas*
- Por que o planejador parou de utilizar o Index?
- Se eu colocar esse parâmetro na transação da aplicação, tenho algum
impacto negativo?
Obrigado por enquanto.
[1]
http://pglearner.blogspot.com.br/2015/01/learning-bitmap-index-scan-recheck-cond.html
--
*______________________________________**Renan Catalani Fuentes de Campos*
*Linkedin: br.linkedin.com/in/renanfuentes/
<http://br.linkedin.com/in/renanfuentes/>*
*Skype:** renan_fuentes*
*Telefone: (19) 9 9717-9845*
_______________________________________________
pgbr-geral mailing list
[email protected]
https://listas.postgresql.org.br/cgi-bin/mailman/listinfo/pgbr-geral
