|
Can you
show the configuration of postgresql.conf?
Query configuration method:
Select name, setting from pg_settings where name
~ 'buffers|cpu|^enable';
On 2018年07月17日 13:17, Benjamin Scherrey
wrote:
What's the on disk cache size for each drive? The better
HDD performance problem won't be sustained with large amounts
of data and several different queries.
- - Ben Scherrey
Dear,
Some of you can help me understand this.
This query plan is executed in the query below (query 9 of
TPC-H
Benchmark, with scale 40, database with approximately 40
gb).
The experiment consisted of running the query on a HDD
(Raid zero).
Then the same query is executed on an SSD (Raid Zero).
Why did the HDD (7200 rpm) perform better?
HDD - TIME 9 MINUTES
SSD - TIME 15 MINUTES
As far as I know, the SSD has a reading that is 300 times
faster than SSD.
--- Execution Plans---
ssd 40g
https://explain.depesz.com/s/rHkh
hdd 40g
https://explain.depesz.com/s/l4sq
Query ------------------------------------
select
nation,
o_year,
sum(amount) as sum_profit
from
(
select
n_name as nation,
extract(year from o_orderdate) as o_year,
l_extendedprice * (1 - l_discount) -
ps_supplycost *
l_quantity as amount
from
part,
supplier,
lineitem,
partsupp,
orders,
nation
where
s_suppkey = l_suppkey
and ps_suppkey = l_suppkey
and ps_partkey = l_partkey
and p_partkey = l_partkey
and o_orderkey = l_orderkey
and s_nationkey = n_nationkey
and p_name like '%orchid%'
) as profit
group by
nation,
o_year
order by
nation,
o_year desc
--
<b>张文升</b> winston<br />
PostgreSQL DBA<br />
|
