Diogo Biazus escreveu: > Em 13/08/2008, às 11:23, Mateus escreveu: > >> Estou fazendo um teste para migrar a versão do pg 8.2.6 para 8.3. >> >> Rodei uma consulta pesada no o 8.2 e no 8.3. O 8.2 rodou essa consulta >> em 6s e pg 8.3 em 15. >> >> O pg 8.3 não deveria ser mais rápido ? > > Como o Adnré já comentou para responder o porque da diferença ver o > plano de execução nas duas versões seria necessário, de preferência > com um EXPLAIN ANALYZE para vermos onde está a diferença. > Em alguns testes caseiros que eu fiz, com uma carga só de SELECTs > notei uma melhora de aproximadamente 15% na performance na 8.3, mas > nunca fiz testes com atualização de dados. > > Outro detalhe apontado pelo André, espero que o shared_buffers = 600GB > seja na verdade 600MB. > > -- > Diogo Biazus > [EMAIL PROTECTED] > http://www.softa.com.br > http://www.postgresql.org.br >
explain analyze PG 8.2.6 "Nested Loop Left Join (cost=446.97..54099.40 rows=1 width=1237) (actual time=7960.564..8367.378 rows=1 loops=1)" " Join Filter: ((demcom.tg99.cdvendedor)::text = ((mot_sic.codmotemp)::character varying)::text)" " -> Nested Loop Left Join (cost=446.97..54046.03 rows=1 width=1207) (actual time=7959.368..8365.757 rows=1 loops=1)" " -> Nested Loop Left Join (cost=446.97..54039.75 rows=1 width=1170) (actual time=7959.342..8365.724 rows=1 loops=1)" " Join Filter: ((demcom.tg99.cdveiculo)::text = (placa.placa)::text)" " -> Hash Left Join (cost=446.97..54020.04 rows=1 width=1152) (actual time=7958.217..8364.531 rows=1 loops=1)" " Hash Cond: (demcom.tg99.cdcarga = (tipos.codtipo)::double precision)" " -> Nested Loop Left Join (cost=444.94..54017.99 rows=1 width=1115) (actual time=7958.045..8364.355 rows=1 loops=1)" " Join Filter: ((demcom.tg99.cdpedido)::text = (remetente.cdpedido)::text)" " -> Nested Loop Left Join (cost=296.62..35198.77 rows=1 width=918) (actual time=4177.194..4558.696 rows=1 loops=1)" " Join Filter: ((demcom.tg99.cdpedido)::text = (destinatario.cdpedido)::text)" " -> Nested Loop Left Join (cost=148.31..16379.54 rows=1 width=721) (actual time=433.562..790.402 rows=1 loops=1)" " Join Filter: ((demcom.tg99.cdpedido)::text = (demcom.tg99.cdpedido)::text)" " -> Nested Loop Left Join (cost=0.00..4734.64 rows=1 width=585) (actual time=71.555..428.391 rows=1 loops=1)" " Join Filter: ((demcom.tg99.cdpedido)::text = (demcom.tg99.cdpedido)::text)" " -> Index Scan using tg99_pkey on tg99 (cost=0.00..6.28 rows=1 width=449) (actual time=0.026..0.029 rows=1 loops=1)" " Index Cond: ((cdpedido)::text = 'AN/028266'::text)" " -> Merge Join (cost=0.00..4515.50 rows=17029 width=149) (actual time=0.035..405.466 rows=16330 loops=1)" " Merge Cond: ((clientes.cdpsja)::text = (demcom.tg99.cdcosignat)::text)" " -> Index Scan using pk_clientes_cdpsja on clientes (cost=0.00..243.98 rows=2725 width=144) (actual time=0.010..4.464 rows=2723 loops=1)" " -> Index Scan using idx_tg99_cdcosignat on tg99 (cost=0.00..30089.75 rows=126453 width=21) (actual time=0.015..203.885 rows=126453 loops=1)" " -> Hash Join (cost=148.31..11591.16 rows=4299 width=149) (actual time=9.718..356.617 rows=3862 loops=1)" " Hash Cond: ((demcom.tg99.cdredespac)::text = (clientes.cdpsja)::text)" " -> Seq Scan on tg99 (cost=0.00..10609.53 rows=126453 width=18) (actual time=0.005..165.569 rows=126453 loops=1)" " -> Hash (cost=114.25..114.25 rows=2725 width=144) (actual time=9.644..9.644 rows=2725 loops=1)" " -> Seq Scan on clientes (cost=0.00..114.25 rows=2725 width=144) (actual time=0.006..4.220 rows=2725 loops=1)" " -> Hash Left Join (cost=148.31..15974.03 rows=126453 width=161) (actual time=10.015..3587.077 rows=126453 loops=1)" " Hash Cond: ((demcom.tg99.cddestinat)::text = (base.clientes.cdpsja)::text)" " -> Seq Scan on tg99 (cost=0.00..10609.53 rows=126453 width=22) (actual time=0.004..194.943 rows=126453 loops=1)" " -> Hash (cost=114.25..114.25 rows=2725 width=156) (actual time=9.900..9.900 rows=2725 loops=1)" " -> Seq Scan on clientes (cost=0.00..114.25 rows=2725 width=156) (actual time=0.008..4.327 rows=2725 loops=1)" " -> Hash Left Join (cost=148.31..15974.03 rows=126453 width=161) (actual time=10.289..3628.145 rows=126453 loops=1)" " Hash Cond: ((demcom.tg99.cdremetent)::text = (base.clientes.cdpsja)::text)" " -> Seq Scan on tg99 (cost=0.00..10609.53 rows=126453 width=20) (actual time=0.004..194.965 rows=126453 loops=1)" " -> Hash (cost=114.25..114.25 rows=2725 width=156) (actual time=10.194..10.194 rows=2725 loops=1)" " -> Seq Scan on clientes (cost=0.00..114.25 rows=2725 width=156) (actual time=0.008..4.519 rows=2725 loops=1)" " -> Hash (cost=1.46..1.46 rows=46 width=41) (actual time=0.154..0.154 rows=46 loops=1)" " -> Seq Scan on tipos (cost=0.00..1.46 rows=46 width=41) (actual time=0.007..0.067 rows=46 loops=1)" " -> Seq Scan on cavalos cavalos_sic (cost=0.00..12.99 rows=299 width=162) (actual time=0.011..0.768 rows=299 loops=1)" " -> Index Scan using pk_pr99_cdprod on pr99 (cost=0.00..6.27 rows=1 width=45) (actual time=0.017..0.019 rows=1 loops=1)" " Index Cond: ((demcom.tg99.cdprod1)::text = (pr99.cdprod)::text)" " -> Seq Scan on motoristas mot_sic (cost=0.00..53.30 rows=3 width=34) (actual time=0.009..0.916 rows=295 loops=1)" " Filter: ((status <> 4) AND (abs(motorista) = 1::smallint))" "Total runtime: 8367.936 ms" explain analyze PG 8.3.3 "Nested Loop Left Join (cost=398.98..677195.27 rows=1 width=1223) (actual time=16171.826..16675.808 rows=1 loops=1)" " -> Nested Loop Left Join (cost=398.97..677186.71 rows=1 width=1196) (actual time=16101.626..16605.599 rows=1 loops=1)" " -> Nested Loop Left Join (cost=398.97..677178.43 rows=1 width=1165) (actual time=16088.663..16592.628 rows=1 loops=1)" " Join Filter: ((demcom.tg99.cdveiculo)::text = (cavalos_sic.cavalo)::text)" " -> Hash Left Join (cost=398.97..677159.71 rows=1 width=1147) (actual time=16076.390..16579.410 rows=1 loops=1)" " Hash Cond: (demcom.tg99.cdcarga = (tipos.codtipo)::double precision)" " -> Nested Loop Left Join (cost=396.94..677157.66 rows=1 width=1027) (actual time=16063.957..16566.972 rows=1 loops=1)" " Join Filter: ((demcom.tg99.cdpedido)::text = (demcom.tg99.cdpedido)::text)" " -> Nested Loop Left Join (cost=396.94..672260.33 rows=1 width=912) (actual time=15932.243..16033.064 rows=1 loops=1)" " Join Filter: ((demcom.tg99.cdpedido)::text = (demcom.tg99.cdpedido)::text)" " -> Nested Loop Left Join (cost=264.62..341385.84 rows=1 width=715) (actual time=8706.955..8757.214 rows=1 loops=1)" " Join Filter: ((demcom.tg99.cdpedido)::text = (demcom.tg99.cdpedido)::text)" " -> Nested Loop Left Join (cost=132.31..10511.35 rows=1 width=518) (actual time=1264.548..1264.555 rows=1 loops=1)" " Join Filter: ((demcom.tg99.cdpedido)::text = (demcom.tg99.cdpedido)::text)" " -> Index Scan using tg99_pkey on tg99 (cost=0.00..8.29 rows=1 width=403) (actual time=46.484..46.487 rows=1 loops=1)" " Index Cond: ((cdpedido)::text = 'AN/028266'::text)" " -> Hash Join (cost=132.31..10456.66 rows=3712 width=125) (actual time=32.995..1212.205 rows=3863 loops=1)" " Hash Cond: ((demcom.tg99.cdredespac)::text = (clientes.cdpsja)::text)" " -> Seq Scan on tg99 (cost=0.00..9496.37 rows=126537 width=12) (actual time=7.766..991.691 rows=126537 loops=1)" " -> Hash (cost=98.25..98.25 rows=2725 width=120) (actual time=25.047..25.047 rows=2725 loops=1)" " -> Seq Scan on clientes (cost=0.00..98.25 rows=2725 width=120) (actual time=6.776..17.392 rows=2725 loops=1)" " -> Hash Left Join (cost=132.31..328027.41 rows=126537 width=134) (actual time=44.560..7280.081 rows=126537 loops=1)" " Hash Cond: ((demcom.tg99.cddestinat)::text = (base.clientes.cdpsja)::text)" " -> Seq Scan on tg99 (cost=0.00..9496.37 rows=126537 width=16) (actual time=0.005..217.671 rows=126537 loops=1)" " -> Hash (cost=98.25..98.25 rows=2725 width=129) (actual time=13.514..13.514 rows=2725 loops=1)" " -> Seq Scan on clientes (cost=0.00..98.25 rows=2725 width=129) (actual time=0.011..5.913 rows=2725 loops=1)" " -> Hash Left Join (cost=132.31..328027.41 rows=126537 width=134) (actual time=13.485..7063.399 rows=126537 loops=1)" " Hash Cond: ((demcom.tg99.cdremetent)::text = (base.clientes.cdpsja)::text)" " -> Seq Scan on tg99 (cost=0.00..9496.37 rows=126537 width=14) (actual time=0.005..203.793 rows=126537 loops=1)" " -> Hash (cost=98.25..98.25 rows=2725 width=129) (actual time=13.318..13.318 rows=2725 loops=1)" " -> Seq Scan on clientes (cost=0.00..98.25 rows=2725 width=129) (actual time=0.009..5.741 rows=2725 loops=1)" " -> Merge Join (cost=0.00..4706.99 rows=15227 width=125) (actual time=47.619..510.875 rows=16348 loops=1)" " Merge Cond: ((clientes.cdpsja)::text = (demcom.tg99.cdcosignat)::text)" " -> Index Scan using pk_clientes_cdpsja on clientes (cost=0.00..249.72 rows=2725 width=120) (actual time=16.749..22.143 rows=2723 loops=1)" " -> Index Scan using idx_tg99_cdcosignat on tg99 (cost=0.00..35406.48 rows=126537 width=15) (actual time=30.854..272.689 rows=126537 loops=1)" " -> Hash (cost=1.46..1.46 rows=46 width=124) (actual time=12.354..12.354 rows=46 loops=1)" " -> Seq Scan on tipos (cost=0.00..1.46 rows=46 width=124) (actual time=12.164..12.240 rows=46 loops=1)" " -> Seq Scan on cavalos cavalos_sic (cost=0.00..11.99 rows=299 width=150) (actual time=10.719..12.730 rows=299 loops=1)" " -> Index Scan using pk_pr99_cdprod on pr99 (cost=0.00..8.27 rows=1 width=36) (actual time=12.949..12.952 rows=1 loops=1)" " Index Cond: ((demcom.tg99.cdprod1)::text = (pr99.cdprod)::text)" " -> Index Scan using idx_motoristas_codmotemp on motoristas mot_sic (cost=0.00..8.28 rows=1 width=31) (actual time=27.430..27.434 rows=1 loops=1)" " Index Cond: ((demcom.tg99.cdvendedor)::integer = mot_sic.codmotemp)" " Filter: ((mot_sic.status <> 4) AND (abs(mot_sic.motorista) = 1::smallint))" "Total runtime: 16677.346 ms" _______________________________________________ pgbr-geral mailing list [email protected] https://listas.postgresql.org.br/cgi-bin/mailman/listinfo/pgbr-geral
