Em 25 de agosto de 2017 05:53, Flavio Henrique Araque Gurgel <[email protected]> escreveu: > > > Em sex, 25 de ago de 2017 às 10:45, Neto pr <[email protected]> escreveu: >> >> Olá Pessoal >> Preciso de uma ajuda na análise desses 2 planos de execução. >> Gostaria de descobrir, porque uma query sem índice executa mais rápido >> (7 vezes+-) do que com índice, embora os custos sejam menores. >> Tenho outros casos que aconteceram a mesma situação. Ajustei o >> servidor conforme www.pgconfig.org. >> Estou utilizando o postgresql versão 9.0.1 no SO Debian 8, sei que é >> uma versão antiga, mas fiz o teste em uma versão 9.6.4 e também >> ocorreu o mesmo. >> >> Pelo que vi está utilizando o índice shipmodelineitem000 no 1º plano... >> >> Query| Índices(sim/não) |Tempo gasto |Total Cost >> ===================================================== >> 12 com índice 00:08:58 2710805.51 >> 12 sem índice 00:01:42 3365996.34 >> >> ----------------- Explain Analyze Query 12 Utilizando índice >> ---------------------------- >> Sort (cost=2710805.51..2710805.51 rows=1 width=27) (actual >> time=537713.672..537713.672 rows=2 loops=1) >> Sort Key: lineitem.l_shipmode >> Sort Method: quicksort Memory: 25kB >> -> HashAggregate (cost=2710805.47..2710805.50 rows=1 width=27) >> (actual time=537713.597..537713.598 rows=2 loops=1) >> -> Merge Join (cost=1994471.69..2708777.28 rows=270426 >> width=27) (actual time=510717.977..536818.802 rows=311208 loops=1) >> Merge Cond: (orders.o_orderkey = lineitem.l_orderkey) >> -> Index Scan using orders_pkey on orders >> (cost=0.00..672772.57 rows=15000045 width=20) >> (actual time=0.019..20898.325 rows=14999972 >> loops=1) >> -> Sort (cost=1994455.40..1995131.47 >> rows=270426 width=19) (actual time=510690.114..510915.678 rows=311208 >> loops=1) >> Sort Key: lineitem.l_orderkey >> Sort Method: external sort Disk: >> 11568kB >> -> Bitmap Heap Scan on >> lineitem (cost=336295.10..1970056.39 rows=270426 width=19) (actual >> time=419620.817..509685.421 rows=311208 loops=1) >> Recheck Cond: >> (l_shipmode = ANY (_{TRUCK,AIR}_::bpchar[])) >> Filter: >> ((l_commitdate < l_receiptdate) AND (l_shipdate < l_commitdate) AND >> (l_receiptdate >= _1997-01-01_::date) AND (l_receiptdate < >> _1998-01-0100:00:00_::timestamp without time zone)) >> -> Bitmap >> Index Scan on idx_l_shipmodelineitem000 (cost=0.00..336227.49 >> rows=15942635 width=0) (actual time=419437.172..419437.172 >> rows=17133713 loops=1) >> Index >> Cond: (l_shipmode = ANY (_{TRUCK,AIR}_::bpchar[])) >> >> Total runtime: 537728.848 ms >> >> >> ----------------- Explain Analyze Query 12 SEM Utilizar índice >> ---------------------------- >> Sort (cost=3365996.33..3365996.34 rows=1 width=27) (actual >> time=101850.883..101850.884 rows=2 loops=1) >> Sort Key: lineitem.l_shipmode Sort Method: quicksort Memory: 25kB >> -> HashAggregate (cost=3365996.30..3365996.32 rows=1 width=27) >> (actual time=101850.798..101850.800 rows=2 loops=1) >> -> Merge Join (cost=2649608.28..3363936.68 rows=274616 >> width=27) (actual time=75497.181..100938.830 rows=311208 loops=1) >> Merge Cond: (orders.o_orderkey = lineitem.l_orderkey) >> -> Index Scan using orders_pkey on orders >> (cost=0.00..672771.90 rows=15000000 width=20) (actual >> time=0.020..20272.828 rows=14999972 loops=1) >> -> Sort (cost=2649545.68..2650232.22 >> rows=274616 width=19) (actual time=75364.450..75618.772 rows=311208 >> loops=1) >> Sort Key: lineitem.l_orderkey >> Sort Method: external sort Disk: 11568kB >> -> Seq Scan on lineitem >> (cost=0.00..2624738.17 rows=274616 width=19) (actual >> time=0.839..74391.087 rows=311208 loops=1) >> Filter: ((l_shipmode= >> ANY (_{TRUCK,AIR}_::bpchar[])) AND (l_commitdate < l_receiptdate) AND >> (l_shipdate < l_commitdate) AND (l_receiptdate >= _1997-01-01_::date) >> AND (l_receiptdate < _1998-01-01 00:00:00_::timestamp without time >> zone)) >> >> Total runtime: 101865.253 ms >> >> ------- query 12 ---------------------- >> select >> l_shipmode, >> sum(case >> when o_orderpriority = '1-URGENT' >> or o_orderpriority = '2-HIGH' >> then 1 >> else 0 >> end) as high_line_count, >> sum(case >> when o_orderpriority <> '1-URGENT' >> and o_orderpriority <> '2-HIGH' >> then 1 >> else 0 >> end) as low_line_count >> from >> orders, >> lineitem >> where >> o_orderkey = l_orderkey >> and l_shipmode in ('TRUCK', 'AIR') >> and l_commitdate < l_receiptdate >> and l_shipdate < l_commitdate >> and l_receiptdate >= date '1997-01-01' >> and l_receiptdate < date '1997-01-01' + interval '1' year >> group by >> l_shipmode >> order by >> l_shipmode > > > Os custos são baseados nas configurações do servidor. > Quais são os valores de seus: > cpu_index_tuple_cost > cpu_operator_cost > cpu_tuple_cost > random_page_cost > seq_page_cost > ? > Ola Estou usando um servidor HP proliant Ml110-G9: Processador: (1) Intel Xeon E5-1603v3 (2.8GHz/4-core/10MB/140W) Memória RAM: 4GB DDR4 Disco Rígido: SATA 1TB 7.2K rpm LFF Mais especificacoes aqui:https://www.hpe.com/us/en/product-catalog/servers/proliant-servers/pip.specifications.hpe-proliant-ml110-gen9-server.7796454.html
Eu Nao alterei os parametros citados, alterei somente o que foi sugerido pela ferramenta www.pgconfig.org Segue abaixo o que esta no postgresql.conf. Voces indicariam qual valor para por exemplo: cpu_index_tuple_cost e outros, baseado neste servidor. #seq_page_cost = 1.0 #random_page_cost = 4.0 #cpu_tuple_cost = 0.01 #cpu_index_tuple_cost = 0.005 #cpu_operator_cost = 0.0025 shared_buffers = 1GB effective_cache_size = 3GB work_mem = 26214kB maintenance_work_mem = 512MB checkpoint_segments = 128 checkpoint_completion_target = 0.9 wal_buffers = 16MB default_statistics_target = 500 > Se você muda essas configurações e o servidor não acompanha, seus custos > mudam nas estatísticas e o PostgreSQL vai traçar planos diferentes, baseados > no que você configurou, mas suas configurações podem não ser ótimas e o > desempenho será pior. Talvez isso que esta ocorrendo, pois esse servidor veio com 8 gb de memoria, para ver se ele utilizava os indices, reduzi a memoria RAM para 4 GB (de 8gb que ele tinha), para ver se ao inves de buscar no disco e jogar para memoria RAM se ele iria usar os indices e ter um resultado melhor, pelo fato de nao ter espaco em memoria RAM, mas mesmo com apenas 4 gb de RAM, o desempenho utilizando indices 'e deploravel. . []`s Neto > > O contrário também é verdadeiro. Se suas configurações são aquelas por > padrão mas seu servidor tiver, por exemplo, discos mais rápidos em modo > aleatório (como SSD), os planos não ótimos serão mais rápidos. > > Enfim, precisa ajustar suas configurações para seu hardware. > > []s > Flavio Gurgel > > _______________________________________________ > pgbr-geral mailing list > [email protected] > https://listas.postgresql.org.br/cgi-bin/mailman/listinfo/pgbr-geral _______________________________________________ pgbr-geral mailing list [email protected] https://listas.postgresql.org.br/cgi-bin/mailman/listinfo/pgbr-geral
