Re: Better Hardware, worst Results

Citando Rod Taylor <pg@rbt.ca>:
> Please send an explain analyze from both.
I'm sendin three explains. In the first the Dell machine didn't use existing
indexes, so I turn enable_seqscan off (this is the second explain). The total
cost decreased, but the total time not. The third explain refers to the cheaper
(and faster) machine. The last thing is the query itself.


 Nested Loop  (cost=9008.68..13596.97 rows=1 width=317) (actual
time=9272.803..65287.304 rows=2604 loops=1)
   ->  Hash Join  (cost=9008.68..13590.91 rows=1 width=319) (actual
time=9243.294..10560.330 rows=2604 loops=1)
         Hash Cond: ("outer".cd_tipo_pagamento = "inner".cd_tipo_pagamento)
         ->  Hash Join  (cost=9007.59..13589.81 rows=1 width=317) (actual
time=9243.149..10529.765 rows=2604 loops=1)
               Hash Cond: ("outer".cd_condicao = "inner".cd_condicao)
               ->  Nested Loop  (cost=9006.46..13588.62 rows=8 width=315)
(actual time=9243.083..10497.385 rows=2604 loops=1)
                     ->  Merge Join  (cost=9006.46..13540.44 rows=8 width=290)
(actual time=9242.962..10405.245 rows=2604 loops=1)
                           Merge Cond: ("outer".cd_pessoa = "inner".cd_pessoa)
                           ->  Nested Loop Left Join  (cost=4658.37..9183.72
rows=375 width=286) (actual time=9210.101..10327.003 rows=23392 loops=1)
                                 ->  Merge Left Join  (cost=4658.37..6924.15
rows=375 width=274) (actual time=9209.952..9981.475 rows=23392 loops=1)
                                       Merge Cond: ("outer".cd_pessoa =
"inner".cd_pessoa)
                                       ->  Merge Left Join
(cost=3366.00..5629.19 rows=375 width=255) (actual time=9158.705..9832.781
rows=23392 loops=1)
                                             Merge Cond: ("outer".cd_pessoa =
"inner".cd_pessoa)
                                             ->  Nested Loop Left Join
(cost=2073.63..4334.24 rows=375 width=236) (actual time=8679.698..9152.213
rows=23392 loops=
1)
                                                   ->  Merge Left Join
(cost=2073.63..2075.94 rows=375 width=44) (actual time=8679.557..8826.898
rows=23392 loops=1
)
                                                         Merge Cond:
("outer".cd_pessoa = "inner".cd_pessoa)
                                                         ->  Sort
(cost=1727.15..1728.09 rows=375 width=40) (actual time=8580.391..8611.842
rows=23392 loops=1)
                                                               Sort Key:
p.cd_pessoa
                                                               ->  Seq Scan on
pessoa p  (cost=0.00..1711.12 rows=375 width=40) (actual time=0.371..8247.028
rows=50
412 loops=1)
                                                                     Filter:
(cliente_liberado(cd_pessoa) = 1)
                                                         ->  Sort
(cost=346.47..346.69 rows=85 width=8) (actual time=99.121..120.706 rows=16470
loops=1)
                                                               Sort Key:
e.cd_pessoa
                                                               ->  Seq Scan on
endereco e  (cost=0.00..343.75 rows=85 width=8) (actual time=0.070..30.558
rows=16858
 loops=1)
                                                                     Filter:
(id_tipo_endereco = 2)
                                                   ->  Index Scan using
pk_pessoa_juridica on pessoa_juridica pj  (cost=0.00..6.01 rows=1 width=196)
(actual time=0.
007..0.008 rows=1 loops=23392)
                                                         Index Cond:
(pj.cd_pessoa = "outer".cd_pessoa)
                                             ->  Sort  (cost=1292.37..1293.18
rows=325 width=23) (actual time=478.963..522.701 rows=33659 loops=1)
                                                   Sort Key: t.cd_pessoa
                                                   ->  Seq Scan on telefone t
(cost=0.00..1278.81 rows=325 width=23) (actual time=0.039..120.256 rows=59572
loops=1
)
                                                         Filter: (id_principal =
1::smallint)
                                       ->  Sort  (cost=1292.37..1293.18 rows=325
width=23) (actual time=51.205..53.662 rows=3422 loops=1)
                                             Sort Key: tf.cd_pessoa
                                             ->  Seq Scan on telefone tf
(cost=0.00..1278.81 rows=325 width=23) (actual time=0.024..43.192 rows=3885
loops=1)
                                                   Filter: (id_tipo =
4::smallint)
                                 ->  Index Scan using pk_cep on cep c
(cost=0.00..6.01 rows=1 width=20) (actual time=0.007..0.009 rows=1 loops=23392)
                                       Index Cond: (c.cd_cep = "outer".cd_cep)
                           ->  Sort  (cost=4348.08..4351.89 rows=1524 width=4)
(actual time=13.182..18.069 rows=2619 loops=1)
                                 Sort Key: cgv.cd_pessoa
                                 ->  Index Scan using
idx_cliente_grupo_vendedor_cd_vendedor on cliente_grupo_vendedor cgv
(cost=0.00..4267.51 rows=1524 width=4) (
actual time=0.114..8.986 rows=2619 loops=1)
                                       Index Cond: (cd_vendedor = 577)
                     ->  Index Scan using pk_cliente_financeiro on
cliente_financeiro cf  (cost=0.00..6.01 rows=1 width=25) (actual
time=0.018..0.021 rows=1 loops=2
604)
                           Index Cond: ("outer".cd_pessoa = cf.cd_pessoa)
               ->  Hash  (cost=1.11..1.11 rows=11 width=6) (actual
time=0.029..0.029 rows=0 loops=1)
                     ->  Seq Scan on condicao_pagamento cp  (cost=0.00..1.11
rows=11 width=6) (actual time=0.006..0.024 rows=11 loops=1)
         ->  Hash  (cost=1.07..1.07 rows=7 width=6) (actual time=0.114..0.114
rows=0 loops=1)
               ->  Seq Scan on tipo_pagamento tp  (cost=0.00..1.07 rows=7
width=6) (actual time=0.095..0.106 rows=7 loops=1)
   ->  Index Scan using pk_cliente on cliente cl  (cost=0.00..6.01 rows=1
width=10) (actual time=0.013..0.017 rows=1 loops=2604)
         Index Cond: ("outer".cd_pessoa = cl.cd_pessoa)
 Total runtime: 65298.215 ms
(49 registros)


*************************

 Nested Loop  (cost=5155.51..19320.20 rows=1 width=317) (actual
time=480.311..62530.121 rows=2604 loops=1)
   ->  Nested Loop  (cost=5155.51..19314.14 rows=1 width=319) (actual
time=445.146..7385.369 rows=2604 loops=1)
         ->  Hash Join  (cost=5155.51..19309.45 rows=1 width=317) (actual
time=429.995..7307.799 rows=2604 loops=1)
               Hash Cond: ("outer".cd_tipo_pagamento =
"inner".cd_tipo_pagamento)
               ->  Nested Loop  (cost=5149.42..19303.31 rows=8 width=315)
(actual time=365.722..7208.785 rows=2604 loops=1)
                     ->  Merge Join  (cost=5149.42..19255.13 rows=8 width=290)
(actual time=365.551..7112.292 rows=2604 loops=1)
                           Merge Cond: ("outer".cd_pessoa = "inner".cd_pessoa)
                           ->  Nested Loop Left Join  (cost=801.33..14898.41
rows=375 width=286) (actual time=180.146..7026.597 rows=23392 loops=1)
                                 ->  Merge Left Join  (cost=801.33..12638.83
rows=375 width=274) (actual time=180.087..6620.025 rows=23392 loops=1)
                                       Merge Cond: ("outer".cd_pessoa =
"inner".cd_pessoa)
                                       ->  Merge Left Join
(cost=801.33..9709.38 rows=375 width=255) (actual time=179.964..6443.147
rows=23392 loops=1)
                                             Merge Cond: ("outer".cd_pessoa =
"inner".cd_pessoa)
                                             ->  Nested Loop Left Join
(cost=801.33..6779.94 rows=375 width=236) (actual time=178.106..6131.000
rows=23392 loops=1)
                                                   ->  Merge Left Join
(cost=801.33..4521.63 rows=375 width=44) (actual time=177.883..5737.847
rows=23392 loops=1)
                                                         Merge Cond:
("outer".cd_pessoa = "inner".cd_pessoa)
                                                         ->  Index Scan using
pk_pessoa on pessoa p  (cost=0.00..3718.93 rows=375 width=40) (actual
time=41.851..543
1.143 rows=23392 loops=1)
                                                               Filter:
(cliente_liberado(cd_pessoa) = 1)
                                                         ->  Sort
(cost=801.33..801.55 rows=85 width=8) (actual time=135.988..166.175 rows=16470
loops=1)
                                                               Sort Key:
e.cd_pessoa
                                                               ->  Index Scan
using idx_endereco_cd_cep on endereco e  (cost=0.00..798.61 rows=85 width=8)
(actual t
ime=8.121..61.640 rows=16858 loops=1)
                                                                     Filter:
(id_tipo_endereco = 2)
                                                   ->  Index Scan using
pk_pessoa_juridica on pessoa_juridica pj  (cost=0.00..6.01 rows=1 width=196)
(actual time=0.
009..0.010 rows=1 loops=23392)
                                                         Index Cond:
(pj.cd_pessoa = "outer".cd_pessoa)
                                             ->  Index Scan using
idx_telefone_cd_pessoa_id_principal on telefone t  (cost=0.00..2927.68 rows=325
width=23) (actual
time=1.840..106.496 rows=33659 loops=1)
                                                   Filter: (id_principal =
1::smallint)
                                       ->  Index Scan using
idx_telefone_cd_pessoa_id_principal on telefone tf  (cost=0.00..2927.68
rows=325 width=23) (actual time=
0.056..67.089 rows=3422 loops=1)
                                             Filter: (id_tipo = 4::smallint)
                                 ->  Index Scan using pk_cep on cep c
(cost=0.00..6.01 rows=1 width=20) (actual time=0.010..0.011 rows=1 loops=23392)
                                       Index Cond: (c.cd_cep = "outer".cd_cep)
                           ->  Sort  (cost=4348.08..4351.89 rows=1524 width=4)
(actual time=14.178..18.668 rows=2619 loops=1)
                                 Sort Key: cgv.cd_pessoa
                                 ->  Index Scan using
idx_cliente_grupo_vendedor_cd_vendedor on cliente_grupo_vendedor cgv
(cost=0.00..4267.51 rows=1524 width=4) (
actual time=0.177..9.557 rows=2619 loops=1)
                                       Index Cond: (cd_vendedor = 577)
                     ->  Index Scan using pk_cliente_financeiro on
cliente_financeiro cf  (cost=0.00..6.01 rows=1 width=25) (actual
time=0.019..0.022 rows=1 loops=2
604)
                           Index Cond: ("outer".cd_pessoa = cf.cd_pessoa)
               ->  Hash  (cost=6.08..6.08 rows=7 width=6) (actual
time=64.025..64.025 rows=0 loops=1)
                     ->  Index Scan using pk_tipo_pagamento on tipo_pagamento tp
 (cost=0.00..6.08 rows=7 width=6) (actual time=63.991..64.007 rows=7 loops=1)
         ->  Index Scan using pk_condicao_pagamento on condicao_pagamento cp
(cost=0.00..               Index Cond: (cp.cd_condicao = "outer".cd_condicao)
   ->  Index Scan using pk_cliente on cliente cl  (cost=0.00..6.01 rows=1
width=10) (actual time=0.013..0.017 rows=1 loops=2604)
         Index Cond: ("outer".cd_pessoa = cl.cd_pessoa)
 Total runtime: 62536.845 ms
(42 registros)
4.68 rows=1 width=6) (actual time=0.014..0.016 rows=1 loops=2604)


*************************

 Hash Join  (cost=2.23..11191.77 rows=9 width=134) (actual
time=341.708..21868.167 rows=2604 loops=1)
   Hash Cond: ("outer".cd_condicao = "inner".cd_condicao)
   ->  Hash Join  (cost=1.09..11190.16 rows=9 width=132) (actual
time=329.205..19758.764 rows=2604 loops=1)
         Hash Cond: ("outer".cd_tipo_pagamento = "inner".cd_tipo_pagamento)
         ->  Nested Loop  (cost=0.00..11188.94 rows=9 width=130) (actual
time=329.086..19727.477 rows=2604 loops=1)
               ->  Merge Join  (cost=0.00..9190.52 rows=245 width=138) (actual
time=7.860..18543.354 rows=24380 loops=1)
                     Merge Cond: ("outer".cd_pessoa = "inner".cd_pessoa)
                     ->  Merge Join  (cost=0.00..11686.19 rows=245 width=128)
(actual time=7.692..17802.380 rows=24380 loops=1)
                           Merge Cond: ("outer".cd_pessoa = "inner".cd_pessoa)
                           ->  Nested Loop Left Join  (cost=0.00..14123.02
rows=375 width=106) (actual time=7.513..17071.221 rows=70931 loops=1)
                                 ->  Merge Left Join  (cost=0.00..12973.12
rows=375 width=94) (actual time=7.297..16005.974 rows=70931 loops=1)
                                       Merge Cond: ("outer".cd_pessoa =
"inner".cd_pessoa)
                                       ->  Merge Left Join  (cost=0.00..10076.90
rows=375 width=82) (actual time=7.161..15391.752 rows=70931 loops=1)
                                             Merge Cond: ("outer".cd_pessoa =
"inner".cd_pessoa)
                                             ->  Nested Loop Left Join
(cost=0.00..7040.30 rows=375 width=70) (actual time=6.990..14516.256 rows=47998
loops=1)
                                                   ->  Nested Loop Left Join
(cost=0.00..5401.41 rows=375 width=37) (actual time=6.839..13504.771 rows=47998
loops=
1)
                                                         ->  Index Scan using
pk_pessoa on pessoa p  (cost=0.00..3398.09 rows=375 width=33) (actual
time=6.599..1234
7.532 rows=47998 loops=1)
                                                               Filter:
(cliente_liberado(cd_pessoa) = 1)
                                                         ->  Index Scan using
un_endereco_id_tipo_endereco on endereco e  (cost=0.00..5.33 rows=1 width=8)
(actual t
ime=0.015..0.016 rows=0 loops=47998)
                                                               Index Cond:
(e.cd_pessoa = "outer".cd_pessoa)
                                                               Filter:
(id_tipo_endereco = 2)
                                                   ->  Index Scan using
pk_pessoa_juridica on pessoa_juridica pj  (cost=0.00..4.36 rows=1 width=37)
(actual time=0.0
12..0.013 rows=0 loops=47998)
                                                         Index Cond:
(pj.cd_pessoa = "outer".cd_pessoa)
                                             ->  Index Scan using
idx_telefone_cd_pessoa_id_principal on telefone t  (cost=0.00..2884.52
rows=59265 width=16) (actua
l time=0.146..260.008 rows=58128 loops=1)
                                                   Filter: (id_principal =
1::smallint)
                                       ->  Index Scan using
idx_telefone_cd_pessoa_id_principal on telefone tf  (cost=0.00..2884.52
rows=4217 width=16) (actual time
=0.053..159.212 rows=3600 loops=1)
                                             Filter: (id_tipo = 4::smallint)
                                 ->  Index Scan using pk_cep on cep c
(cost=0.00..3.05 rows=1 width=20) (actual time=0.006..0.007 rows=0 loops=70931)
                                       Index Cond: (c.cd_cep = "outer".cd_cep)
                           ->  Index Scan using pk_cliente_financeiro on
cliente_financeiro cf  (cost=0.00..1806.88 rows=48765 width=22) (actual
time=0.146..175.468
 rows=48765 loops=1)
                     ->  Index Scan using pk_cliente on cliente cl
(cost=0.00..1387.01 rows=48805 width=10) (actual time=0.135..179.715 rows=48804
loops=1)
               ->  Index Scan using idx_cliente_grupo_vendedor_cd_pessoa on
cliente_grupo_vendedor cgv  (cost=0.00..8.14 rows=1 width=4) (actual
time=0.042..0.043 r
ows=0 loops=24380)
                     Index Cond: (cgv.cd_pessoa = "outer".cd_pessoa)
                     Filter: (cd_vendedor = 577)
         ->  Hash  (cost=1.07..1.07 rows=7 width=6) (actual time=0.059..0.059
rows=0 loops=1)
               ->  Seq Scan on tipo_pagamento tp  (cost=0.00..1.07 rows=7
width=6) (actual time=0.033..0.047 rows=7 loops=1)
   ->  Hash  (cost=1.11..1.11 rows=11 width=6) (actual time=0.096..0.096 rows=0
loops=1)
         ->  Seq Scan on condicao_pagamento cp  (cost=0.00..1.11 rows=11
width=6) (actual time=0.054..0.079 rows=11 loops=1)
 Total runtime: 21873.236 ms
(39 rows)


SELECT  p.cd_pessoa,
        obtem_cnpj_cpf(p.cd_pessoa) AS nr_cnpj_cpf, p.nm_pessoa,
COALESCE(pj.nm_fantasia, p.nm_pessoa),
        obtem_endereco(obtem_endereco_comercial(p.cd_pessoa)) AS ds_endereco,
        obtem_bairro(obtem_endereco_comercial(p.cd_pessoa)) AS ds_bairro,
        c.cd_cidade, c.nr_cep, pj.nr_ie, '0' || t.nr_telefone, '0' ||
tf.nr_telefone,
        cf.cd_tipo_pagamento, cf.cd_condicao, cp.nr_dias, cl.cd_atividade,
tp.nr_hierarquia,
        '0', REPLACE(cf.pr_taxa_financeira, '.', ',') AS pr_taxa_financeira,
        TO_CHAR(p.dt_nascimento, 'DDMMYYYY') AS dt_nascimento,
        cl.nr_checkouts,
        CASE WHEN cf.id_confianca = 1 THEN 'A'
             WHEN cf.id_confianca = 2 THEN 'B'
             WHEN cf.id_confianca = 3 THEN 'C'
                     WHEN cf.id_confianca = 4 THEN 'D'
        END AS id_confianca,
        '' AS id_cadastro
FROM pessoa p
    LEFT OUTER JOIN endereco e ON e.cd_pessoa = p.cd_pessoa AND
e.id_tipo_endereco = 2
      LEFT OUTER JOIN pessoa_juridica pj ON pj.cd_pessoa = p.cd_pessoa
      LEFT OUTER JOIN telefone t ON t.cd_pessoa = p.cd_pessoa AND t.id_principal
= '1'
      LEFT OUTER JOIN telefone tf ON tf.cd_pessoa = p.cd_pessoa AND tf.id_tipo =
'4'
      LEFT OUTER JOIN cep c ON c.cd_cep = e.cd_cep
      JOIN cliente cl ON cl.cd_pessoa = p.cd_pessoa
      JOIN cliente_financeiro cf ON cf.cd_pessoa = cl.cd_pessoa
      JOIN cliente_grupo_vendedor cgv ON cgv.cd_pessoa = p.cd_pessoa
      JOIN condicao_pagamento cp ON cp.cd_condicao = cf.cd_condicao
      JOIN tipo_pagamento tp ON tp.cd_tipo_pagamento = cf.cd_tipo_pagamento
WHERE cgv.cd_vendedor = '577'
AND cliente_liberado(p.cd_pessoa) = 1;



--
+---------------------------------------------------+
|  Alvaro Nunes Melo    Atua Sistemas de Informacao |
| al_nunes@atua.com.br        www.atua.com.br       |
|    UIN - 42722678            (54) 327-1044        |
+---------------------------------------------------+

----------------------------------------------------------------
This message was sent using IMP, the Internet Messaging Program.

Atua Sistemas de Informação - http://www.atua.com.br