>com a execução caindo para 765 ms quando em cache. Me surpreende o fato de os >join alterados não serem na tabela compras, e agora o plano usar o índice >desejado em compras. Não caberia aqui alguma melhoria no algorítimo do >otimizador? > > >O uso de índices com LEFT JOIN é um problema conhecido, não apenas no >Postgres. > > >Veja uma melhoria em relação a isto nos release notes do 9.1 em E.7.3.1.1. >Performance: http://www.postgresql.org/docs/9.2/static/release-9-1.html > > > > >>De qualquer forma, obrigado a todos pela ajuda. Foi esclarecedor pra mim e >>pra lista. >> > > > >Cara, faz o que o Euler falou, manda o EXPLAIN ANALYZE. Vai permitir uma >avaliação mais consistente. > > >Algumas observações até aqui: > > >* Vale sempre a pena lembrar, antes de tudo rode um ANALYZE em todas as >tabelas envolvidas. Sei que você já deve ter feito isso, mas eu mesmo esqueço >de vez em quando... >* Muito LEFT JOIN em cascata é realmente um problema. Você pode trocar os mais >caros por um UNION, onde você fazer um INNER JOIN em um e no outro no outro >faz um WHERE NOT EXISTS. >* Você pode ter uma NFE sem ter uma compra e uma venda?
não. Tem que ter um ou outro. >* Uma informação relevante é o volume de registros entre as tabelas envolvidas; sim... nfe: 600.000 - vendas: 520.000 - compras: 80.000 >* Algumas tabelas aparecem mais de uma vez. Parecem ser tabelas pequenas, mas >a modelagem ficou um pouco obscura para mim. - Sim. Isso por que a tabela Terceiros recebe clientes, fornecedores, funcionarios, etc... >* A tabela COMPRAS tem mais de 10 índices, é isso mesmo? Verificou se todos >estão sendo utilizados? - Não verifiquei. Vou fazer isso. Mas de qualquer forma, isso interferiria apenas na performance de gravação, ne? >* Eu gosto um pouco de usar o EXPLAIN do PGADMIN III. Em alguns momentos a >visualização gráfica torna mais fácil de ver os gargalos dentro de um EXPLAIN >muito grande. > > >Por enquanto é isso. Vamos aguardar o EXPLAIN ANALYZE, ok? Segue abaixo o explain analyse. Porém a view foi corrigida, e melhorou em muito o desempenho, mudando apenas dois joins entre nfe e terceiros e nfe e naturezas_operacao (de left para join apenas). Feito isso, o plano passou a usar o índice de compras e vendas. Segue também abaixo o explain analyse sem a correção. Corrigido: FROM nfe LEFT JOIN compras c ON c.id = nfe.id_compra LEFT JOIN vendas v ON v.id = nfe.id_venda JOIN terceiros t1 ON nfe.id_terceiro = t1.id JOIN naturezas_operacoes nop1 ON nfe.id_nat_operacao = nop1.id JOIN nfe_filas f ON f.id = nfe.id_fila LEFT JOIN terceiros t2 ON t2.id = nfe.id_func_emissao LEFT JOIN terceiros t3 ON t3.id = nfe.id_func_cancelamento LEFT JOIN romaneios r1 ON r1.id = v.id_romaneio LEFT JOIN romaneios r2 ON r2.id = c.id_romaneio_devolucao LEFT JOIN nfe_contingencia ctg ON ctg.id = nfe.id_contingencia; "QUERY PLAN" "Sort (cost=427805.46..428592.64 rows=314870 width=631) (actual time=1937.467..1937.469 rows=9 loops=1)" " Sort Key: nfe.numero_nf, nfe.id" " Sort Method: quicksort Memory: 34kB" " -> Hash Left Join (cost=83218.34..307649.71 rows=314870 width=512) (actual time=1221.194..1937.393 rows=9 loops=1)" " Hash Cond: (c.id_romaneio_devolucao = r2.id)" " -> Hash Left Join (cost=81360.37..214851.30 rows=314870 width=500) (actual time=1207.026..1922.560 rows=9 loops=1)" " Hash Cond: (v.id_romaneio = r1.id)" " -> Hash Left Join (cost=79502.40..205533.11 rows=314870 width=488) (actual time=1192.595..1908.102 rows=9 loops=1)" " Hash Cond: (nfe.id_contingencia = ctg.id)" " -> Hash Left Join (cost=79501.35..204351.30 rows=314870 width=486) (actual time=1192.570..1908.061 rows=9 loops=1)" " Hash Cond: (nfe.id_func_cancelamento = t3.id)" " -> Hash Left Join (cost=78700.38..200004.89 rows=314870 width=464) (actual time=1183.818..1899.292 rows=9 loops=1)" " Hash Cond: (nfe.id_func_emissao = t2.id)" " -> Hash Join (cost=77899.40..193116.21 rows=314870 width=442) (actual time=1174.509..1889.956 rows=9 loops=1)" " Hash Cond: (nfe.id_nat_operacao = nop1.id)" " -> Nested Loop (cost=77897.91..188785.26 rows=314870 width=418) (actual time=1174.481..1889.910 rows=9 loops=1)" " -> Seq Scan on nfe_filas f (cost=0.00..1.04 rows=1 width=11) (actual time=0.009..0.012 rows=1 loops=1)" " Filter: (id = 1)" " -> Hash Join (cost=77897.91..185635.52 rows=314870 width=409) (actual time=1174.467..1889.880 rows=9 loops=1)" " Hash Cond: (nfe.id_terceiro = t1.id)" " -> Hash Left Join (cost=77096.93..178143.56 rows=314870 width=364) (actual time=1164.561..1879.949 rows=9 loops=1)" " Hash Cond: (nfe.id_compra = c.id)" " -> Hash Right Join (cost=70969.88..167268.78 rows=314870 width=346) (actual time=1061.846..1777.210 rows=9 loops=1)" " Hash Cond: (v.id = nfe.id_venda)" " -> Seq Scan on vendas v (cost=0.00..63212.78 rows=750478 width=27) (actual time=0.006..713.378 rows=749035 loops=1)" " -> Hash (cost=53504.00..53504.00 rows=314870 width=327) (actual time=600.734..600.734 rows=9 loops=1)" " Buckets: 16384 Batches: 4 Memory Usage: 2kB" " -> Seq Scan on nfe (cost=0.00..53504.00 rows=314870 width=327) (actual time=572.135..572.196 rows=9 loops=1)" " Filter: ((((dh_cancelamento IS NULL) AND (dh_envio_email IS NULL) AND (NOT nao_enviar_email)) OR ((dh_cancelamento IS NOT NULL) AND (dh_envio_email_canc IS NULL) AND (NOT nao_enviar_email_canc))) AND ((COALESCE(email, ''::character varying))::text <> ''::text) AND (id_fila = 1) AND (id_empresa = 1) AND (status_retorno = '1'::bpchar))" " -> Hash (cost=5131.47..5131.47 rows=79647 width=26) (actual time=102.677..102.677 rows=79612 loops=1)" " Buckets: 8192 Batches: 1 Memory Usage: 4826kB" " -> Seq Scan on compras c (cost=0.00..5131.47 rows=79647 width=26) (actual time=0.007..78.981 rows=79612 loops=1)" " -> Hash (cost=617.10..617.10 rows=14710 width=49) (actual time=9.889..9.889 rows=14147 loops=1)" " Buckets: 2048 Batches: 1 Memory Usage: 1148kB" " -> Seq Scan on terceiros t1 (cost=0.00..617.10 rows=14710 width=49) (actual time=0.007..4.913 rows=14147 loops=1)" " -> Hash (cost=1.22..1.22 rows=22 width=32) (actual time=0.019..0.019 rows=22 loops=1)" " Buckets: 1024 Batches: 1 Memory Usage: 2kB" " -> Seq Scan on naturezas_operacoes nop1 (cost=0.00..1.22 rows=22 width=32) (actual time=0.005..0.006 rows=22 loops=1)" " -> Hash (cost=617.10..617.10 rows=14710 width=30) (actual time=9.280..9.280 rows=14147 loops=1)" " Buckets: 2048 Batches: 1 Memory Usage: 881kB" " -> Seq Scan on terceiros t2 (cost=0.00..617.10 rows=14710 width=30) (actual time=0.016..4.451 rows=14147 loops=1)" " -> Hash (cost=617.10..617.10 rows=14710 width=30) (actual time=8.728..8.728 rows=14147 loops=1)" " Buckets: 2048 Batches: 1 Memory Usage: 881kB" " -> Seq Scan on terceiros t3 (cost=0.00..617.10 rows=14710 width=30) (actual time=0.003..3.706 rows=14147 loops=1)" " -> Hash (cost=1.02..1.02 rows=2 width=6) (actual time=0.007..0.007 rows=2 loops=1)" " Buckets: 1024 Batches: 1 Memory Usage: 1kB" " -> Seq Scan on nfe_contingencia ctg (cost=0.00..1.02 rows=2 width=6) (actual time=0.004..0.004 rows=2 loops=1)" " -> Hash (cost=1630.21..1630.21 rows=18221 width=20) (actual time=14.409..14.409 rows=18105 loops=1)" " Buckets: 2048 Batches: 1 Memory Usage: 990kB" " -> Seq Scan on romaneios r1 (cost=0.00..1630.21 rows=18221 width=20) (actual time=0.006..9.114 rows=18105 loops=1)" " -> Hash (cost=1630.21..1630.21 rows=18221 width=20) (actual time=13.951..13.951 rows=18105 loops=1)" " Buckets: 2048 Batches: 1 Memory Usage: 990kB" " -> Seq Scan on romaneios r2 (cost=0.00..1630.21 rows=18221 width=20) (actual time=0.006..8.590 rows=18105 loops=1)" "Total runtime: 1937.782 ms" Não Corrigido: FROM nfe LEFT JOIN compras c ON c.id = nfe.id_compra LEFT JOIN vendas v ON v.id = nfe.id_venda JOIN terceiros t1 ON nfe.id_terceiro = t1.id JOIN naturezas_operacoes nop1 ON nfe.id_nat_operacao = nop1.id JOIN nfe_filas f ON f.id = nfe.id_fila LEFT JOIN terceiros t2 ON t2.id = nfe.id_func_emissao LEFT JOIN terceiros t3 ON t3.id = nfe.id_func_cancelamento LEFT JOIN romaneios r1 ON r1.id = v.id_romaneio LEFT JOIN romaneios r2 ON r2.id = c.id_romaneio_devolucao LEFT JOIN nfe_contingencia ctg ON ctg.id = nfe.id_contingencia; "QUERY PLAN" "Sort (cost=427805.46..428592.64 rows=314870 width=631) (actual time=1918.337..1918.338 rows=9 loops=1)" " Sort Key: nfe.numero_nf, nfe.id" " Sort Method: quicksort Memory: 34kB" " -> Hash Left Join (cost=83218.34..307649.71 rows=314870 width=512) (actual time=1202.969..1918.262 rows=9 loops=1)" " Hash Cond: (c.id_romaneio_devolucao = r2.id)" " -> Hash Left Join (cost=81360.37..214851.30 rows=314870 width=500) (actual time=1188.784..1903.430 rows=9 loops=1)" " Hash Cond: (v.id_romaneio = r1.id)" " -> Hash Left Join (cost=79502.40..205533.11 rows=314870 width=488) (actual time=1174.373..1888.990 rows=9 loops=1)" " Hash Cond: (nfe.id_contingencia = ctg.id)" " -> Hash Left Join (cost=79501.35..204351.30 rows=314870 width=486) (actual time=1174.348..1888.955 rows=9 loops=1)" " Hash Cond: (nfe.id_func_cancelamento = t3.id)" " -> Hash Left Join (cost=78700.38..200004.89 rows=314870 width=464) (actual time=1165.583..1880.173 rows=9 loops=1)" " Hash Cond: (nfe.id_func_emissao = t2.id)" " -> Hash Join (cost=77899.40..193116.21 rows=314870 width=442) (actual time=1156.312..1870.876 rows=9 loops=1)" " Hash Cond: (nfe.id_nat_operacao = nop1.id)" " -> Nested Loop (cost=77897.91..188785.26 rows=314870 width=418) (actual time=1156.284..1870.833 rows=9 loops=1)" " -> Seq Scan on nfe_filas f (cost=0.00..1.04 rows=1 width=11) (actual time=0.006..0.010 rows=1 loops=1)" " Filter: (id = 1)" " -> Hash Join (cost=77897.91..185635.52 rows=314870 width=409) (actual time=1156.273..1870.806 rows=9 loops=1)" " Hash Cond: (nfe.id_terceiro = t1.id)" " -> Hash Left Join (cost=77096.93..178143.56 rows=314870 width=364) (actual time=1146.380..1860.889 rows=9 loops=1)" " Hash Cond: (nfe.id_compra = c.id)" " -> Hash Right Join (cost=70969.88..167268.78 rows=314870 width=346) (actual time=1043.505..1757.987 rows=9 loops=1)" " Hash Cond: (v.id = nfe.id_venda)" " -> Seq Scan on vendas v (cost=0.00..63212.78 rows=750478 width=27) (actual time=0.005..709.855 rows=749035 loops=1)" " -> Hash (cost=53504.00..53504.00 rows=314870 width=327) (actual time=643.833..643.833 rows=9 loops=1)" " Buckets: 16384 Batches: 4 Memory Usage: 2kB" " -> Seq Scan on nfe (cost=0.00..53504.00 rows=314870 width=327) (actual time=571.343..571.408 rows=9 loops=1)" " Filter: ((((dh_cancelamento IS NULL) AND (dh_envio_email IS NULL) AND (NOT nao_enviar_email)) OR ((dh_cancelamento IS NOT NULL) AND (dh_envio_email_canc IS NULL) AND (NOT nao_enviar_email_canc))) AND ((COALESCE(email, ''::character varying))::text <> ''::text) AND (id_fila = 1) AND (id_empresa = 1) AND (status_retorno = '1'::bpchar))" " -> Hash (cost=5131.47..5131.47 rows=79647 width=26) (actual time=102.839..102.839 rows=79612 loops=1)" " Buckets: 8192 Batches: 1 Memory Usage: 4826kB" " -> Seq Scan on compras c (cost=0.00..5131.47 rows=79647 width=26) (actual time=0.006..79.206 rows=79612 loops=1)" " -> Hash (cost=617.10..617.10 rows=14710 width=49) (actual time=9.875..9.875 rows=14147 loops=1)" " Buckets: 2048 Batches: 1 Memory Usage: 1148kB" " -> Seq Scan on terceiros t1 (cost=0.00..617.10 rows=14710 width=49) (actual time=0.007..4.848 rows=14147 loops=1)" " -> Hash (cost=1.22..1.22 rows=22 width=32) (actual time=0.019..0.019 rows=22 loops=1)" " Buckets: 1024 Batches: 1 Memory Usage: 2kB" " -> Seq Scan on naturezas_operacoes nop1 (cost=0.00..1.22 rows=22 width=32) (actual time=0.005..0.007 rows=22 loops=1)" " -> Hash (cost=617.10..617.10 rows=14710 width=30) (actual time=9.244..9.244 rows=14147 loops=1)" " Buckets: 2048 Batches: 1 Memory Usage: 881kB" " -> Seq Scan on terceiros t2 (cost=0.00..617.10 rows=14710 width=30) (actual time=0.007..4.377 rows=14147 loops=1)" " -> Hash (cost=617.10..617.10 rows=14710 width=30) (actual time=8.744..8.744 rows=14147 loops=1)" " Buckets: 2048 Batches: 1 Memory Usage: 881kB" " -> Seq Scan on terceiros t3 (cost=0.00..617.10 rows=14710 width=30) (actual time=0.004..3.872 rows=14147 loops=1)" " -> Hash (cost=1.02..1.02 rows=2 width=6) (actual time=0.008..0.008 rows=2 loops=1)" " Buckets: 1024 Batches: 1 Memory Usage: 1kB" " -> Seq Scan on nfe_contingencia ctg (cost=0.00..1.02 rows=2 width=6) (actual time=0.004..0.004 rows=2 loops=1)" " -> Hash (cost=1630.21..1630.21 rows=18221 width=20) (actual time=14.388..14.388 rows=18105 loops=1)" " Buckets: 2048 Batches: 1 Memory Usage: 990kB" " -> Seq Scan on romaneios r1 (cost=0.00..1630.21 rows=18221 width=20) (actual time=0.006..9.059 rows=18105 loops=1)" " -> Hash (cost=1630.21..1630.21 rows=18221 width=20) (actual time=13.965..13.965 rows=18105 loops=1)" " Buckets: 2048 Batches: 1 Memory Usage: 990kB" " -> Seq Scan on romaneios r2 (cost=0.00..1630.21 rows=18221 width=20) (actual time=0.006..8.569 rows=18105 loops=1)" "Total runtime: 1918.651 ms" _______________________________________________ pgbr-geral mailing list [email protected] https://listas.postgresql.org.br/cgi-bin/mailman/listinfo/pgbr-geral
