> I think all of this data cannot fit in shared_buffers, you might want to increase shared_buffers
> to larger size (not 30GB but close to your data size) to see how it behaves.
When I use shared_buffers larger than my data size such as 10 GB, results scale nearly as expected at least for this instance type.
> You queries have Aggregation, ORDER/GROUP BY, so there is a chance
> that I/O can happen for those operation's
> if PG doesn't have sufficient memory (work_mem) to perform such operation.
I used work_mem as 32 MB, this should be enough for these queries. I also tested with higher values of work_mem, and didn't obverse any difference.
> Can you simplify your queries (simple scan or in other words no
> aggregation or other things) to see how
> they behave in your env., once you are able to see simple queries
> scaling as per your expectation, you
> can try with complex one's.
Actually we observe problem when queries start to get simpler such as select count(*). Here is the results table in more compact format:
select count(*) TPC-H Simple(#6) TPC-H Complex(#1)
1 Table / 1 query 1.5 s 2.5 s 8.4 s
2 Tables/ 2 queries 1.5 s 2.5 s 8.4 s
4 Tables/ 4 queries 2.0 s 2.9 s 8.8 s
8 Tables/ 8 queries 3.3 s 4.0 s 9.6 s
> Can we have the explain analyze of those queries, postgres
> configuration, perhaps vmstat output during execution?
> configuration, perhaps vmstat output during execution?
postgres=# explain analyze SELECT count(*) from lineitem_1;
QUERY PLAN
------------------------------------------------------------------------------------------------------------------------------
Aggregate (cost=199645.01..199645.02 rows=1 width=0) (actual time=11317.391..11317.393 rows=1 loops=1)
-> Seq Scan on lineitem_1 (cost=0.00..184641.81 rows=6001281 width=0) (actual time=0.011..5805.255 rows=6001215 loops=1)
Total runtime: 11317.440 ms
(3 rows)
postgres=# explain analyze SELECT
postgres-# sum(l_extendedprice * l_discount) as revenue
postgres-# FROM
postgres-# lineitem_1
postgres-# WHERE
postgres-# l_shipdate >= date '1994-01-01'
postgres-# AND l_shipdate < date '1994-01-01' + interval '1' year
postgres-# AND l_discount between 0.06 - 0.01 AND 0.06 + 0.01
postgres-# AND l_quantity < 24;
QUERY PLAN
------------------------------------------------------------------------------------------------------------------------------
Aggregate (cost=260215.36..260215.37 rows=1 width=16) (actual time=1751.775..1751.776 rows=1 loops=1)
-> Seq Scan on lineitem_1 (cost=0.00..259657.82 rows=111508 width=16) (actual time=0.031..1630.449 rows=114160 loops=1)
Filter: ((l_shipdate >= '1994-01-01'::date) AND (l_shipdate < '1995-01-01 00:00:00'::timestamp without time zone) AND (l_discount >= 0.05::double precision) AND (l_discount <= 0.07::double precision) AND
(l_quantity < 24::double precision))
Rows Removed by Filter: 5887055
Total runtime: 1751.830 ms
(5 rows)
postgres=# explain analyze SELECT
l_returnflag,
l_linestatus,
sum(l_quantity) as sum_qty,
sum(l_extendedprice) as sum_base_price,
sum(l_extendedprice * (1 - l_discount)) as sum_disc_price,
sum(l_extendedprice * (1 - l_discount) * (1 + l_tax)) as sum_charge,
avg(l_quantity) as avg_qty,
avg(l_extendedprice) as avg_price,
avg(l_discount) as avg_disc,
count(*) as count_order
FROM
lineitem_1
WHERE
l_shipdate <= date '1998-12-01' - interval '90' day
GROUP BY
l_returnflag,
l_linestatus
ORDER BY
l_returnflag,
l_linestatus;
QUERY PLAN
-------------------------------------------------------------------------------------------------------------------------------------
Sort (cost=436342.68..436342.69 rows=6 width=36) (actual time=18720.932..18720.936 rows=4 loops=1)
Sort Key: l_returnflag, l_linestatus
Sort Method: quicksort Memory: 25kB
-> HashAggregate (cost=436342.49..436342.60 rows=6 width=36) (actual time=18720.887..18720.892 rows=4 loops=1)
-> Seq Scan on lineitem_1 (cost=0.00..199645.01 rows=5917437 width=36) (actual time=0.011..6754.619 rows=5916591 loops=1)
Filter: (l_shipdate <= '1998-09-02 00:00:00'::timestamp without time zone)
Rows Removed by Filter: 84624
Total runtime: 18721.021 ms
(8 rows)
Here are the results of "vmstat 1" while running 8 parallel TPC-H Simple (#6) queries: Although there is no need for I/O, "wa" fluctuates between 0 and 1.
procs -----------memory---------- ---swap-- -----io---- --system-- -----cpu-----
r b swpd free buff cache si so bi bo in cs us sy id wa st
0 0 0 30093568 84892 38723896 0 0 0 0 22 14 0 0 100 0 0
8 1 0 30043056 84892 38723896 0 0 0 0 27080 52708 16 14 70 0 0
8 1 0 30006600 84892 38723896 0 0 0 0 44952 118286 43 44 12 1 0
8 0 0 29986264 84900 38723896 0 0 0 20 28043 95934 49 42 8 1 0
7 0 0 29991976 84900 38723896 0 0 0 0 8308 73641 52 42 6 0 0
0 0 0 30091828 84900 38723896 0 0 0 0 3996 30978 23 24 53 0 0
0 0 0 30091968 84900 38723896 0 0 0 0 17 23 0 0 100 0 0
