Pessoal, Neste Explain Analyse, Buffers abaixo, tem a presenca de 2 indices secundários btree que criei. Estou querendo migrar eles para um SSD para ver se tem melhor performance. Veja a característica deles
* Indice idx_l_partkeylineitem000 tem 2,5 Gb * Indice idx_p_typepart000 tem 157 Mb Gostaria de saber o que significa o Rows abaixo, seria o numero linhas que foram obtidas da tabela através do índice? ---> Bitmap Index Scan on idx_p_typepart000 (cost=0.00..740.43 rows=26667 width=0) ---> Index Scan using idx_l_partkeylineitem000 on lineitem (cost=0.57..97.65 rows=26 width=36) (actual time=46.953..503.126 rows=30 loops=26469) Pois se analisar o indice idx_p_typepart000 apesar de ser menor, foi bem mais utilizado (26667 rows) que o outro. Sobre o que foi encontrado no Buffer, alguém saberia infomar o que seria: ---> Buffers: shared hit=1394261 read=1499550 written=2, temp read=135262 written=135016 Seria o que foi lido, escrito no buffer? mas está em que medida (kb, bytes, mb??) O que seria o Hit? ---------------------------------EXPLAIN ANALYSE BUFFERS--------------------------------------------------------------------------------------------------------------------------------- Finalize GroupAggregate (cost=2360092.54..2361201.87 rows=2406 width=40) (actual time=4636719.848..4636719.861 rows=2 loops=1) Group Key: (date_part(_year_::text, (orders.o_orderdate)::timestamp without time zone)) Buffers: shared hit=448727 read=515529 written=1, temp read=45083 written=45001 -> Gather Merge (cost=2360092.54..2361087.59 rows=4812 width=72) (actual time=4636709.744..4636719.825 rows=6 loops=1) Workers Planned: 2 Workers Launched: 2 Buffers: shared hit=448727 read=515529 written=1, temp read=45083 written=45001 -> Partial GroupAggregate (cost=2359092.52..2359532.14 rows=2406 width=72) (actual time=4636362.931..4636373.148 rows=2 loops=3) Group Key: (date_part(_year_::text, (orders.o_orderdate)::timestamp without time zone)) Buffers: shared hit=1394318 read=1499550 written=2, temp read=135262 written=135016 -> Sort (cost=2359092.52..2359136.02 rows=17400 width=46) (actual time=4636352.717..4636354.392 rows=16138 loops=3) Sort Key: (date_part(_year_::text, (orders.o_orderdate)::timestamp without time zone)) Sort Method: quicksort Memory: 1641kB Buffers: shared hit=1394318 read=1499550 written=2, temp read=135262 written=135016 -> Hash Join (cost=1200128.12..2357866.97 rows=17400 width=46) (actual time=151052.112..4636336.400 rows=16138 loops=3) Hash Cond: (supplier.s_nationkey = n2.n_nationkey) , Buffers: shared hit=1394304 read=1499550 written=2, temp read=135262 written=135016 -> Hash Join (cost=1200126.56..2357564.91 rows=17400 width=24) (actual time=151052.004..4636323.494 rows=16138 loops=3) Hash Cond: (lineitem.l_suppkey = supplier.s_suppkey) Buffers: shared hit=1394261 read=1499550 written=2, temp read=135262 written=135016 -> Hash Join (cost=1190051.56..2346086.71 rows=17441 width=24) (actual time=86075.147..4624489.804 rows=16138 loops=3) Hash Cond: (lineitem.l_orderkey = orders.o_orderkey) Buffers: shared hit=1386918 read=1493051 written=2, temp read=133048 written=132820 -> Nested Loop (cost=747.67..1144119.43 rows=285995 width=28) (actual time=90.131..4473100.641 rows=264822 loops=3) Buffers: shared hit=84395 read=841021 -> Parallel Bitmap Heap Scan on part (cost=747.10..56230.60 rows=11111 width=4) (actual time=48.979..32834.581 rows=8823 loops=3) Recheck Cond: ((p_type)::text = _LARGE BRUSHED NICKEL_::text) Heap Blocks: exact=7547 Buffers: shared read=22871 -> Bitmap Index Scan on idx_p_typepart000 (cost=0.00..740.43 rows=26667 width=0) (actual time=31.036..31.036 rows=26469 loops=1) Index Cond: ((p_type)::text = _LARGE BRUSHED NICKEL_::text) Buffers: shared read=134 -> Index Scan using idx_l_partkeylineitem000 on lineitem (cost=0.57..97.65 rows=26 width=36) (actual time=46.953..503.126 rows=30 loops=26469) ------------------------------------------------------------------------------------------------------------------------------------------------------------------- ----------------- QUERY No 8 TPCH---------------------------------------------------------------------------------------------------------------------------------- select o_year, sum(case when nation = 'INDIA' then volume else 0 end) / sum(volume) as mkt_share from ( select extract(year from o_orderdate) as o_year, l_extendedprice * (1 - l_discount) as volume, n2.n_name as nation from part, supplier, lineitem, orders, customer, nation n1, nation n2, region where p_partkey = l_partkey and s_suppkey = l_suppkey and l_orderkey = o_orderkey and o_custkey = c_custkey and c_nationkey = n1.n_nationkey and n1.n_regionkey = r_regionkey and r_name = 'ASIA' and s_nationkey = n2.n_nationkey and o_orderdate between date '1995-01-01' and date '1996-12-31' and p_type = 'LARGE BRUSHED NICKEL' ) as all_nations group by o_year order by o_year _______________________________________________ pgbr-geral mailing list pgbr-geral@listas.postgresql.org.br https://listas.postgresql.org.br/cgi-bin/mailman/listinfo/pgbr-geral