> 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.
O que diz
SHOW random_page_cost;
Em cada servidor, antes de fazer o seu SET e antes do explain analyze?
>
> *Dúvidas*
> - Por que o planejador parou de utilizar o Index?
Pode ser uma diferença de configuração.
Pode ser que você esteja usando o Slony mas as tabelas são diferentes em
cada servidor, o Slony permite esse tipo de coisas, por exemplo, a
tabela no servidor de destino tem mais linhas que na tabela de origem,
ou mesmo outras colunas.
> - Se eu colocar esse parâmetro na transação da aplicação, tenho algum
> impacto negativo?
Se você testar direitinho, não. O parâmetro só é usado dentro do
contexto da sua transação. Normalmente não há impacto em outros lugares.
Mas eu estudaria a real causa. Esse parâmetro só se modifica normalmente
quando se tem discos SSD. E me parece que você tem servidores diferentes
em vários aspectos aí.
[]s
Flavio Gurgel
_______________________________________________
pgbr-geral mailing list
[email protected]
https://listas.postgresql.org.br/cgi-bin/mailman/listinfo/pgbr-geral
