[pgbr-geral] update com trigger
Olá pessoal, Queria colocar a seguinte dúvida, eu quero criar uma trigger que execute sempre que são inseridos novos registos numa tabela. A função desta será executar um update preenchendo dois campos com base em distâncias (st_distance). A minha dúvida é o seguinte, quando o update for executado vai ser sempre sobre todos os registos da tabela ou apenas dos criados naquele momento? Obrigado -- Com os melhores cumprimentos, Pedro Costa Geógrafo Especializado em Sistemas de Informação Geográfica e Ordenamento do Território ___ pgbr-geral mailing list pgbr-geral@listas.postgresql.org.br https://listas.postgresql.org.br/cgi-bin/mailman/listinfo/pgbr-geral
[pgbr-geral] Brutal queda de performance atualizando de 8.2 para 8.4
Pessoal,
Estava com alguns problemas de lock e decidi atualizar minha versão do
PostgreSQL da 8.2 para a 8.4, onde muitos avanços foram feitos nesse campo.
Contudo, para minha surpresa, a performance caiu drasticamente. O curioso é
que as máquinas físicas são idênticas fisicamente e mantive exatamente os
mesmos parâmetros de configuração para o PostgreSQL. Para se ter uma ideia
da queda, vou mostrar a saída do EXPLAIN ANALYZE para a mesma consulta nos
diferentes bancos.
*Consulta no PostgreSQL 8.2*
Sort (cost=1029.63..1029.71 rows=33 width=42) (actual
time=3895.353..3895.361 rows=67 loops=1)
Sort Key:
acs_object__name(apm_package__parent_id(public.forums_forums.package_id)),
public.forums_forums.name
- Nested Loop (cost=39.87..1028.80 rows=33 width=42) (actual
time=743.868..3832.291 rows=67 loops=1)
- Bitmap Heap Scan on forums_forums (cost=39.87..464.01 rows=33
width=42) (actual time=260.795..1754.423 rows=67 loops=1)
Recheck Cond: ((package_id = ANY
('{0,840191,1486834,626929,520062,1101742,1160464,1161067,2750196,3500360,133998,3673774,3676596,368
6932,4860207,5986896,10050612,10157702,4645,93855,3186091,601355,22297512,6552691,21650654,8265465,23731964,33752302,15316177}'::integer[]))
AND (en
abled_p = 't'::bpchar))
Filter: (subplan)
- Bitmap Index Scan on forums_forums_pkg_enable_idx
(cost=0.00..39.86 rows=66 width=0) (actual time=56.270..56.270 rows=191
loops=
1)
Index Cond: ((package_id = ANY
('{0,840191,1486834,626929,520062,1101742,1160464,1161067,2750196,3500360,133998,3673774,3676596
,3686932,4860207,5986896,10050612,10157702,4645,93855,3186091,601355,22297512,6552691,21650654,8265465,23731964,33752302,15316177}'::integer[]))
AND
(enabled_p = 't'::bpchar))
SubPlan
- Nested Loop (cost=0.00..21724.67 rows=4088 width=0)
(actual time=25.336..25.336 rows=1 loops=67)
- Nested Loop (cost=0.00..14348.63 rows=9292
width=4) (actual time=16.059..17.942 rows=12 loops=67)
Join Filter: ((pdm.privilege)::text =
(p.privilege)::text)
- Seq Scan on acs_privilege_descendant_map
pdm (cost=0.00..9.23 rows=4 width=16) (actual time=0.103..0.103 rows=1 lo
ops=67)
Filter: ((descendant)::text =
'read_private_data'::text)
- Nested Loop (cost=0.00..3038.60
rows=43700 width=17) (actual time=15.022..17.830 rows=16 loops=67)
- Index Scan using
acs_obj_ctx_idx_object_id_idx on acs_object_context_index c
(cost=0.00..17.37 rows=48 width
=4) (actual time=10.316..11.088 rows=3 loops=67)
Index Cond: (object_id = $0)
- Index Scan using
acs_permissions_object_id_idx on acs_permissions p (cost=0.00..62.32
rows=50 width=21) (actual time=1.726..1.995 rows=5 loops=226)
Index Cond: (c.ancestor_id =
p.object_id)
- Index Scan using party_approved_member_map_pk on
party_approved_member_map pamm (cost=0.00..0.78 rows=1 width=4) (actual
time=0.637..0.637 rows=0 loops=776)
Index Cond: ((pamm.party_id = p.grantee_id)
AND (pamm.member_id = 3443))
- Index Scan using acs_objects_pk on acs_objects
(cost=0.00..8.82 rows=1 width=4) (actual time=16.205..16.207 rows=1
loops=67)
Index Cond: (acs_objects.object_id =
public.forums_forums.forum_id)
SubPlan
- Index Scan using site_nodes_object_id_idx on site_nodes
(cost=0.00..8.27 rows=1 width=4) (actual time=0.581..0.582 rows=1 loops=67)
Index Cond: (object_id = $0)
Total runtime: *3895.939* ms
(27 registros)
*Consulta no PostgreSQL 8.4*
*
*
Sort (cost=3616469.97..3616470.15 rows=72 width=39) (actual
time=95704.475..95704.486 rows=67 loops=1)
Sort Key:
(acs_object__name(apm_package__parent_id(public.forums_forums.package_id))),
public.forums_forums.name
Sort Method: quicksort Memory: 35kB
- Nested Loop Semi Join (cost=0.00..3616467.75 rows=72 width=39)
(actual time=196.064..95703.983 rows=67 loops=1)
Join Filter: (public.forums_forums.package_id = c.object_id)
- Nested Loop (cost=0.00..663.18 rows=72 width=39) (actual
time=0.042..2.175 rows=67 loops=1)
- Seq Scan on forums_forums (cost=0.00..31.16 rows=72
width=39) (actual time=0.017..0.899 rows=67 loops=1)
Filter: ((enabled_p = 't'::bpchar) AND (package_id =
ANY
('{0,840191,1486834,626929,520062,1101742,1160464,1161067,2750196,3500360,133998,3673774,3676596,3686932,4860207,5986896,10050612,10157702,4645,93855,3186091,601355,22297512,6552691,21650654,8265465,23731964,33752302,15316177}'::integer[])))
- Index Scan using acs_objects_pk on acs_objects
(cost=0.00..8.77 rows=1 width=4) (actual time=0.014..0.016 rows=1 loops=67)
Re: [pgbr-geral] update com trigger
Le 2011-N-12 11h18, Pedro Costa a écrit : A minha dúvida é o seguinte, quando o update for executado vai ser sempre sobre todos os registos da tabela ou apenas dos criados naquele momento? Depende de como o codificares! Já viste os exemplos no manual? -- skype:leandro.gfc.dutra?chat Yahoo!: ymsgr:sendIM?lgcdutra +55 (61) 3546 7191 gTalk: xmpp:leand...@jabber.org +55 (11) 9406 7191ICQ/AIM: aim:GoIM?screenname=61287803 BRAZIL GMT-3 MSN: msnim:chat?contact=lean...@dutra.fastmail.fm ___ pgbr-geral mailing list pgbr-geral@listas.postgresql.org.br https://listas.postgresql.org.br/cgi-bin/mailman/listinfo/pgbr-geral
Re: [pgbr-geral] Brutal queda de performance atualizando de 8.2 para 8.4
Le 2011-N-12 11h38, Eduardo Santos a écrit : Estava com alguns problemas de lock Que problemas? Normalmente, a questão não é versão mas programação do aplicativo… decidi atualizar minha versão do PostgreSQL da 8.2 para a 8.4 Por que para uma versão tão antiga? Já estamos na 9.1, que avançou bem mais em relação à 8.4 que a 8.4 em relação à 8.2. Contudo, para minha surpresa, a performance caiu drasticamente. O curioso é que as máquinas físicas são idênticas fisicamente e mantive exatamente os mesmos parâmetros de configuração para o PostgreSQL. Essas não são as únicas variáveis. Só de cabeça, sem gastar muito fosfato, vêm sistema operacional, sistemas de arquivos e configuração dos mesmos; e coleta de estatísticas. Como os planos de execução foram bem diferentes, eu apostaria em diferenças ou na estrutura dos dados; ou no texto da consulta; ou na massa de dados; ou na configuração do PostgreSQL ou na coleta de estatísticas, todos pontos a verificar urgentemente, e informar aqui. -- skype:leandro.gfc.dutra?chat Yahoo!: ymsgr:sendIM?lgcdutra +55 (61) 3546 7191 gTalk: xmpp:leand...@jabber.org +55 (11) 9406 7191ICQ/AIM: aim:GoIM?screenname=61287803 BRAZIL GMT-3 MSN: msnim:chat?contact=lean...@dutra.fastmail.fm ___ pgbr-geral mailing list pgbr-geral@listas.postgresql.org.br https://listas.postgresql.org.br/cgi-bin/mailman/listinfo/pgbr-geral
Re: [pgbr-geral] Brutal queda de performance atualizando de 8.2 para 8.4
Olá Dutra, Obrigado pela resposta. Seguem comentários. Em 12 de novembro de 2011 12:14, Leandro Guimarães Faria Corcete DUTRA l...@dutras.org escreveu: Le 2011-N-12 11h38, Eduardo Santos a écrit : Estava com alguns problemas de lock Que problemas? Normalmente, a questão não é versão mas programação do aplicativo… É um problema que até a versão 8.1 gerava deadlock no PostgreSQL: uma função PL/pgSQL, chamada em uma transação, que chama uma outra função PL/pgSQL em outra transação. Um ciclo de 5 ou 6 funções chamadas dentro de transações. O problema maior não é esse, contudo. A questão é que a sequência de funções pode ser chamada várias vezes dentro da aplicação. Assim, enquanto a tabela está sendo chamada por uma consulta, ela está travada pela transação. No meio disso tudo, existe uma outra chamada para a transação, que só vai ser executada após a primeira trava ser liberada. Difícil de explicar dada a complexidade da chamada, mas o fato é que temos muitos locks em sequência da mesa linha na mesma tabela. Como essa é a parte que mais evoluiu, achei quem um upgrade ia me ajudar. Sim, o aplicativo poderia realizar uma chamada mais atômica de uma única função PL/pgSQL e resolver o problema, mas estou adiando isso até não ter mais jeito. O trabalho de reescrita seria simplesmente construir o sistema novamente, e não posso me dar a esse luxo enquanto o sistema está lento para os usuários. decidi atualizar minha versão do PostgreSQL da 8.2 para a 8.4 Por que para uma versão tão antiga? Já estamos na 9.1, que avançou bem mais em relação à 8.4 que a 8.4 em relação à 8.2. Problemas de retro-compatibilidade que ainda não tive tempo de resolver. Como removeram as cláusulas add_missing_from e regex_flavour na versão 9.0, ainda não posso atualizar. Também é algo que pode ser resolvido, com tempo que ainda não tenho. A ideia era resolver após o sistema ficar mais rápido. Contudo, para minha surpresa, a performance caiu drasticamente. O curioso é que as máquinas físicas são idênticas fisicamente e mantive exatamente os mesmos parâmetros de configuração para o PostgreSQL. Essas não são as únicas variáveis. Só de cabeça, sem gastar muito fosfato, vêm sistema operacional, sistemas de arquivos e configuração dos mesmos; e coleta de estatísticas. Essa é a questão: as máquinas são fisicamente iguais, com os mesmos softwares instalados e mesmas configurações de SO. Fiz questão de checar duas vezes para garantir um ambiente replicado. Até o disco é o mesmo. Como os planos de execução foram bem diferentes, eu apostaria em diferenças ou na estrutura dos dados; ou no texto da consulta; ou na massa de dados; ou na configuração do PostgreSQL ou na coleta de estatísticas, todos pontos a verificar urgentemente, e informar aqui. Essa foi a ideia que eu tive. Me parece que houve muitas mudanças no otimizados. Consegui achar duas referências: http://postgresql.1045698.n5.nabble.com/query-taking-much-longer-since-Postgres-8-4-upgrade-td3787736.html http://postgresql.1045698.n5.nabble.com/A-query-become-very-slow-after-upgrade-from-8-1-10-to-8-4-5-td3246107.html Nenhuma das duas foi conclusiva, contudo. O fato é que o otimizador escolhe um nested loop com um semi join na versão do 8.4, enquanto no 8.1 a opção escolhida é primeiro ordenar utilizando um bitmap heap scan. Infelizmente meus conhecimentos de otimização do otimizador (desculpe o pleonasmo) não me permitiram resolver o problema ainda. Alguma ajuda com dicas de configuração seria bem-vinda. Mais uma vez, obrigado pela resposta em pleno Sabadão! :) -- Eduardo Santos Analista de Sistemas http://eduardosan.wordpress.com http://twitter.com/eduardosan ___ pgbr-geral mailing list pgbr-geral@listas.postgresql.org.br https://listas.postgresql.org.br/cgi-bin/mailman/listinfo/pgbr-geral
Re: [pgbr-geral] Brutal queda de performance atualizando de 8.2 para 8.4
2011/11/12 Eduardo Santos eduardo.edusan...@gmail.com: Pessoal, Estava com alguns problemas de lock e decidi atualizar minha versão do PostgreSQL da 8.2 para a 8.4, onde muitos avanços foram feitos nesse campo. Contudo, para minha surpresa, a performance caiu drasticamente. O curioso é que as máquinas físicas são idênticas fisicamente e mantive exatamente os mesmos parâmetros de configuração para o PostgreSQL. Para se ter uma ideia da queda, vou mostrar a saída do EXPLAIN ANALYZE para a mesma consulta nos diferentes bancos. Consulta no PostgreSQL 8.2 Sort (cost=1029.63..1029.71 rows=33 width=42) (actual time=3895.353..3895.361 rows=67 loops=1) corte ,/corte Total runtime: 3895.939 ms (27 registros) Consulta no PostgreSQL 8.4 Sort (cost=3616469.97..3616470.15 rows=72 width=39) (actual time=95704.475..95704.486 rows=67 loops=1) corte /corte Total runtime: 95704.632 ms (26 registros) As diferenças entre as decisões tomadas pelo otimizador são tão absurdas que não sei nem por onde começar. Será que alguém pode me dar uma luz? -- Pelos números apresentados tudo indica que suas estatísticas estavam atualizadas o que levou o planejador a optar por caminhos ineficientes. Veja por exemplo: Nested Loop (cost=0.00..982907.73 rows=397639583 width=4) (actual time=0.102..1117.157 rows=1396734 loops=67) ele estimou que existiriam 397.639.583 linhas quando, na realidade, existiam 1.396.734 linhas. Rode novamente sua consulta com as estatísticas atualizadas e avalie o resultado. A versão que você está utilizando é a 8.4.9? Osvaldo ___ pgbr-geral mailing list pgbr-geral@listas.postgresql.org.br https://listas.postgresql.org.br/cgi-bin/mailman/listinfo/pgbr-geral
Re: [pgbr-geral] Brutal queda de performance atualizando de 8.2 para 8.4
Olá Osvaldo, Obrigado pela resposta. Seguem comentários: Pelos números apresentados tudo indica que suas estatísticas estavam atualizadas o que levou o planejador a optar por caminhos ineficientes. Veja por exemplo: Nested Loop (cost=0.00..982907.73 rows=397639583 width=4) (actual time=0.102..1117.157 rows=1396734 loops=67) ele estimou que existiriam 397.639.583 linhas quando, na realidade, existiam 1.396.734 linhas. Rode novamente sua consulta com as estatísticas atualizadas e avalie o resultado. A versão que você está utilizando é a 8.4.9? Eu executei o vacuum full com analyze, rodei novamente a consulta e não obtive resultados muito diferentes. A versão é a 8.4.9 sim. Seria necessário executar o vacuum mesmo com o autovacuum ligado? Também não entendi como ele conseguiu ter resultados tão diferentes entre duas versão do PostgreSQL, mas aconteceu. Euler, Seguem respostas: 8.4.oque? 8.4.9 Você executou a EXPLAIN ANALYZE várias vezes para se certificar de que a diferença de tempo não é por causa de uma partida a frio da versão 8.4? Sim, essa foi a primeira coisa que pensei. Executei várias vezes e os resultados não mudam muito. Qual é a consulta? Desculpe, esqueci de anexar a consulta. É uma consulta realmente feia, mas que não chegava a ser um desastre no banco: select forums_forums.package_id, acs_object__name(apm_package__parent_id(forums_forums.package_id)) as parent_name, (select site_node__url(site_nodes.node_id) from site_nodes where site_nodes.object_id = forums_forums.package_id) as url, forums_forums.forum_id, forums_forums.name, case when last_post (cast(current_timestamp as date)- 1) then 't' else 'f' end as new_p from forums_forums_enabled forums_forums, acs_objects where acs_objects.object_id = forums_forums.forum_id and forums_forums.package_id in (0,840191,1486834,626929,520062,1101742,1160464,1161067,2750196,3500360,133998,3673774,3676596,3686932,4860207,5986896,10050612,10157702,4645,93855,3186091,601355,22297512,6552691,21650654,8265465,23731964,33752302,15316177) and exists ( select 1 from acs_object_party_privilege_map ppm where ppm.object_id = forums_forums.package_id and ppm.party_id = '3443' and ppm.privilege = 'read_private_data' ) order by parent_name, forums_forums.name Uma das coisas que me chamaram atenção foi a estimativa do índice acs_obj_ctx_idx_ancestor_idx. Qual a definição da tabela acs_object_context_index? A definição é a seguinte: Tabela public.acs_object_context_index Coluna | Tipo | Modificadores ---+-+--- object_id | integer | não nulo ancestor_id | integer | não nulo n_generations | integer | não nulo Índices: acs_object_context_index_pk PRIMARY KEY, btree (object_id, ancestor_id) acs_obj_ctx_idx_ancestor_idx btree (ancestor_id) acs_obj_ctx_idx_object_id_idx btree (object_id) Restrições de verificação: acs_obj_context_idx_n_gen_ck CHECK (n_generations = 0) Restrições de chave estrangeira: acs_obj_context_idx_anc_id_fk FOREIGN KEY (ancestor_id) REFERENCES acs_objects(object_id) acs_obj_context_idx_obj_id_fk FOREIGN KEY (object_id) REFERENCES acs_objects(object_id) Ela funciona como uma tabela intermediária para facilitar a contagem da quantidade de filhos que determinado objeto tem. É uma tentativa de evitar a contagem num subselect, o que tornaria a consulta ainda mais lenta. Sabe me dizer se houve alguma mudança significativa no otimizados entre as versões 8.2 e 8.4? -- Eduardo Santos Analista de Sistemas http://eduardosan.wordpress.com http://twitter.com/eduardosan ___ pgbr-geral mailing list pgbr-geral@listas.postgresql.org.br https://listas.postgresql.org.br/cgi-bin/mailman/listinfo/pgbr-geral
Re: [pgbr-geral] Brutal queda de performance atualizando de 8.2 para 8.4
On 12-11-2011 13:05, Eduardo Santos wrote: Desculpe, esqueci de anexar a consulta. É uma consulta realmente feia, mas que não chegava a ser um desastre no banco: Um comentário: o PostgreSQL não se dá muito bem com listas grandes no IN. Os valores vem de outra consulta, se sim, talvez seja prudente substituir esses valores por uma junção. Tabela public.acs_object_context_index Parece-me que está tabela e/ou índice acs_obj_ctx_idx_ancestor_idx está(ão) inchado(s). Faça um VACUUM nessa tabela e um REINDEX no índice mencionado. Verifique se o plano muda. Ela funciona como uma tabela intermediária para facilitar a contagem da quantidade de filhos que determinado objeto tem. É uma tentativa de evitar a contagem num subselect, o que tornaria a consulta ainda mais lenta. Você chegou a testar essa estratégia? Otimização precoce pode ser pior que nenhuma otimização. Sabe me dizer se houve alguma mudança significativa no otimizados entre as versões 8.2 e 8.4? Várias mudanças. Vide notas de lançamento da 8.3 e 8.4. Outra coisa que notei foi que a cardinalidade (aka número de tuplas) das tabelas envolvidas parece ter tamanho diferentes entre as versões. Os bancos de dados são idênticos? Teste as várias estratégias apontadas e apresente os novos planos de consulta. -- Euler Taveira de Oliveira - Timbira http://www.timbira.com.br/ PostgreSQL: Consultoria, Desenvolvimento, Suporte 24x7 e Treinamento ___ pgbr-geral mailing list pgbr-geral@listas.postgresql.org.br https://listas.postgresql.org.br/cgi-bin/mailman/listinfo/pgbr-geral
Re: [pgbr-geral] Postgres Embarcado Existe?
Pessoal, pensa no seguinte... Você dá um CD com seu aplicativo a uma pessoa. Você não vai estar lá pra instalar pra ele, e ele não sabe nada de instalação, principalmente de postgres. Por isso a necessidade de enviar um CD com uma instalação totalmente automatica. Meu aplicativo consigo fazer com InnoSetup, mas o Postgres ainda estou estudando pra ver se é possivel. Só isso. Não é nada de outro mundo, é porque eu gosto do Postgres, acho que seria ótimo usa-lo em aplicações de pequenissimo porte (desktop) mas gostaria que isso fosse transparente ao usuario. Já peguei algumas aplicações em CD com Delphi e MySQL, e achei que poderia fazer o mesmo com postgres, já que o adotei como meu banco padrão. é isso :) Mas as questões postadas são interessante :) -Mensagem Original- From: Bruno Silva Sent: Friday, November 11, 2011 11:22 AM To: Comunidade PostgreSQL Brasileira Subject: Re: [pgbr-geral] Postgres Embarcado Existe? Ainda não entendi porque você precisa embarcar o Postgres, você vai usar o aplicativo em terminais diferentes e ter bases descentralizadas? Só quer fazer um instalador silencioso? É um hardware diferente ou são terminais normais? Bem observado, por que embarcado mesmo? Se não me engano a instalação do Postgres (for Window$) permite fazer no modo silencioso. Bruno E. A. Silva. Analista de Sistemas. ___ pgbr-geral mailing list pgbr-geral@listas.postgresql.org.br https://listas.postgresql.org.br/cgi-bin/mailman/listinfo/pgbr-geral ___ pgbr-geral mailing list pgbr-geral@listas.postgresql.org.br https://listas.postgresql.org.br/cgi-bin/mailman/listinfo/pgbr-geral
Re: [pgbr-geral] neofito em postgis solicita ajuda para começar os trabalhos
Pode contar com minha ajuda. Também atuo neste universo de Geoprocessamento com PostGIS. Não sei muito, mas posso colaborar. Luis Fernando Em 11 de novembro de 2011 22:20, antonio borba antonio.borba2...@gmail.comescreveu: Bom, como disse sou neófito nesse terreno, chaves naturais?, não suporta unicidade?, vou ficando com vcs para aprender. Quanto a natureza do trabalho, é o seguinte, trabalho com geoprocessamento a um bom tempo, mas sempre usuário de ferramentas para georreferenciamento, análise de imagens ... essas coisas. estou querendo adentrar a área de modelagem e implementação de sistema para gerenciamento de bases de dados geográficas no intuito de realizar a gestão de políticas voltadas para agricultura familiar, com os enfoques socioeconômicos e ambiental utilizando Postgresql+postgis+gvSIG, de forma a permitir a qualificação do gerenciamento da informação, do planejamento e tomada de decisão. Mas voltando pra terra do binário,estou agora buscando instalar o Postgresql+postgis no ubuntu, em casa, para poder brincar, inicialmente, com modelagem usando brmodelo, se não me engano é o nome do programa, e entender, estudar o postgre etc... Esse é o desafio e estou contando com a comunidade. Antonio ___ pgbr-geral mailing list pgbr-geral@listas.postgresql.org.br https://listas.postgresql.org.br/cgi-bin/mailman/listinfo/pgbr-geral ___ pgbr-geral mailing list pgbr-geral@listas.postgresql.org.br https://listas.postgresql.org.br/cgi-bin/mailman/listinfo/pgbr-geral
