|
OK, lá vai. Postgres.conf Parâmetro Padrão ServLento ServRápido max_connections 100 70 100 shared_buffers 32MB 1000MB 32MB work_mem 1MB 64MB 1MB maintenance_work_mem 16MB 256MB 16MB max_fsm_pages 204800 1000000 204800 max_fsm_relations 1000 2000 2000 checkpoint_segments 3 3 10 enable_seqscan on off off enable_tidscan on off off effective_cache_size 128MB 256MB 128MB Consulta usada tanto no ServLento quanto no ServRapido EXPLAIN ANALYSE SELECT Count("NotaFiscal"."CodigoInternoNota")::Integer As Qtde, Sum("NotaFiscal"."TotalLiquidoNota")::Numeric(15,4) As SubTotal, Sum("NotaFiscal"."TotalNota")::Numeric(15,4) As TotalNota, "NotaFiscal"."CodigoEmpresaNota"::Integer As CodigoEmpresaNota, --Número da impressora já gravada na tabela de nota fiscal "NotaFiscal"."NumeroImpressoraNota"::Varchar(10) As NumeroImpressora, --Retorna o numero de serie da empressora, a partir da impressora gravada na tabela de nota fiscal /*(Select "NumeroSerieECF" From "ECF" Where "CodigoEmpresaECF" = "NotaFiscal"."CodigoEmpresaNota" AND "CodigoECF" = "NotaFiscal"."NumeroImpressoraNota") As "NumeroSerieImpressora",*/ (Sum("NotaFiscal"."TotalLiquidoNota")::Numeric(15,4) / Count("NotaFiscal"."CodigoInternoNota")::Integer)::Numeric(15,4) As XMediaSubTotal, (Sum("NotaFiscal"."TotalNota")::Numeric(15,4) / Count("NotaFiscal"."CodigoInternoNota")::Integer)::Numeric(15,4) As XMediaTotal FROM "NotaFiscal" LEFT JOIN "Operacao" ON "NotaFiscal"."CodigoOperacaoEstoqueNota" = "Operacao"."CodigoInternoOperacoes" WHERE "Operacao"."TipoOperacoes" = 'V' AND "NotaFiscal"."ModuloOrigemNota" = 'TS-Fature' AND "NotaFiscal"."NumeroImpressoraNota" between '0001' and '9999' AND "NotaFiscal"."DataEmissaoNota" between '2009-05-01' and '2009-05-30' AND "NotaFiscal"."SituacaoNota" is null GROUP BY CodigoEmpresaNota,NumeroImpressora ORDER BY NumeroImpressora,CodigoEmpresaNota ------------------------------ Resultado do explain analyse no ServLento "GroupAggregate (cost=2696.13..2696.22 rows=1 width=54) (actual time=638899.183..639115.314 rows=35 loops=1)" " -> Sort (cost=2696.13..2696.13 rows=1 width=54) (actual time=638896.568..638945.504 rows=97965 loops=1)" " Sort Key: "NotaFiscal"."NumeroImpressoraNota", "NotaFiscal"."CodigoEmpresaNota"" " -> Nested Loop (cost=2345.56..2696.12 rows=1 width=54) (actual time=559508.579..638638.195 rows=97965 loops=1)" " -> Index Scan using "Operacao_CodigoInterno_PK" on "Operacao" (cost=0.00..12.39 rows=1 width=4) (actual time=5.206..5.218 rows=1 loops=1)" " Filter: (("TipoOperacoes")::text = 'V'::text)" " -> Bitmap Heap Scan on "NotaFiscal" (cost=2345.56..2683.72 rows=1 width=58) (actual time=559503.357..638516.721 rows=97965 loops=1)" " Recheck Cond: ((("NotaFiscal"."NumeroImpressoraNota")::text >= '0001'::text) AND (("NotaFiscal"."NumeroImpressoraNota")::text <= '9999'::text) AND ("NotaFiscal"."CodigoOperacaoEstoqueNota" = "Operacao"."CodigoInternoOperacoes"))" " Filter: ((("ModuloOrigemNota")::text = 'TS-Fature'::text) AND ("DataEmissaoNota" >= '2009-05-01'::date) AND ("DataEmissaoNota" <= '2009-05-30'::date) AND ("SituacaoNota" IS NULL))" " -> BitmapAnd (cost=2345.56..2345.56 rows=85 width=0) (actual time=559003.403..559003.403 rows=0 loops=1)" " -> Bitmap Index Scan on "NotaFiscal_Impressora_Intervencao_Cupom_I" (cost=0.00..1079.62 rows=16956 width=0) (actual time=205223.259..205223.259 rows=3374443 loops=1)" " Index Cond: ((("NumeroImpressoraNota")::text >= '0001'::text) AND (("NumeroImpressoraNota")::text <= '9999'::text))" " -> Bitmap Index Scan on "NotaFiscal_CodigoOperacaoEstoque_I" (cost=0.00..1265.69 rows=16956 width=0) (actual time=353598.739..353598.739 rows=747641 loops=1)" " Index Cond: ("NotaFiscal"."CodigoOperacaoEstoqueNota" = "Operacao"."CodigoInternoOperacoes")" "Total runtime: 639124.536 ms" --------------------------------------------- Resultado do explain analyse no ServRapido "GroupAggregate (cost=971.00..971.08 rows=1 width=54) (actual time=1153.322..1356.063 rows=35 loops=1)" " -> Sort (cost=971.00..971.00 rows=1 width=54) (actual time=1151.227..1242.719 rows=89221 loops=1)" " Sort Key: "NotaFiscal"."NumeroImpressoraNota", "NotaFiscal"."CodigoEmpresaNota"" " -> Nested Loop (cost=640.78..970.99 rows=1 width=54) (actual time=453.271..841.699 rows=89221 loops=1)" " -> Index Scan using "Operacao_CodigoInterno_PK" on "Operacao" (cost=0.00..12.36 rows=1 width=4) (actual time=0.063..0.070 rows=1 loops=1)" " Filter: (("TipoOperacoes")::text = 'V'::text)" " -> Bitmap Heap Scan on "NotaFiscal" (cost=640.78..958.62 rows=1 width=58) (actual time=453.201..793.978 rows=89221 loops=1)" " Recheck Cond: (("NotaFiscal"."CodigoOperacaoEstoqueNota" = "Operacao"."CodigoInternoOperacoes") AND ("NotaFiscal"."DataEmissaoNota" >= '2009-05-01'::date) AND ("NotaFiscal"."DataEmissaoNota" <= '2009-05-30'::date))" " Filter: ((("ModuloOrigemNota")::text = 'TS-Fature'::text) AND (("NumeroImpressoraNota")::text >= '0001'::text) AND (("NumeroImpressoraNota")::text <= '9999'::text) AND ("SituacaoNota" IS NULL))" " -> BitmapAnd (cost=640.78..640.78 rows=80 width=0) (actual time=451.452..451.452 rows=0 loops=1)" " -> Bitmap Index Scan on "NotaFiscal_CodigoOperacaoEstoque_I" (cost=0.00..300.31 rows=15961 width=0) (actual time=420.393..420.393 rows=539101 loops=1)" " Index Cond: ("NotaFiscal"."CodigoOperacaoEstoqueNota" = "Operacao"."CodigoInternoOperacoes")" " -> Bitmap Index Scan on "NotaFiscal_DataEmissao_I" (cost=0.00..340.21 rows=15961 width=0) (actual time=22.197..22.197 rows=89882 loops=1)" " Index Cond: (("DataEmissaoNota" >= '2009-05-01'::date) AND ("DataEmissaoNota" <= '2009-05-30'::date))" "Total runtime: 1357.815 ms" Euler Taveira de Oliveira escreveu: Sergio Santi escreveu:Os planos de execução são muitíssimo diferentes e comparando o postgres.conf de cada um, respectivamente, temos as seguintes diferenças principais: -- Sergio Medeiros Santi |
_______________________________________________ pgbr-geral mailing list [email protected] https://listas.postgresql.org.br/cgi-bin/mailman/listinfo/pgbr-geral
