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
[email protected]
https://listas.postgresql.org.br/cgi-bin/mailman/listinfo/pgbr-geral
