Hello everybody, Here is a thing I observed in my recent experimentation, on changing the value of PARALLEL_TUPLE_QUEUE_SIZE to 6553600, the performance of a TPC-H query is improved by more than 50%.
Specifically, with this change, q12 completes in 14 seconds which was taking 45 seconds on head. There wasn't any change in the plan structure, just the time at gather-merge reduced which gave this improvement. This clearly says that the current value of PARALLEL_TUPLE_QUEUE_SIZE is not the best one for all the queries, rather some modification in it is very likely to improve performance significantly. One way to do is to give this parameters as another GUC just like min_parallel_table_scan_size, etc. Attached .txt file gives the plan at head and with this patch, additionally patch is attached for setting PARALLEL_TUPLE_QUEUE_SIZE to 6553600 too. Thoughts? -- Regards, Rafia Sabih EnterpriseDB: http://www.enterprisedb.com/
Head:
\i /data/rafia.sabih/dss/queries/12.sql
-----------------------------------------------------------------------------------------------------------------------------------------------
------------------------------------------------------------------------
Limit (cost=1001.19..391123.34 rows=1 width=27) (actual
time=45511.755..45511.755 rows=1 loops=1)
-> GroupAggregate (cost=1001.19..2731856.24 rows=7 width=27) (actual
time=45511.753..45511.753 rows=1 loops=1)
Group Key: lineitem.l_shipmode
-> Gather Merge (cost=1001.19..2721491.60 rows=592261 width=27)
(actual time=7.806..44794.334 rows=311095 loops=1)
Workers Planned: 4
Workers Launched: 4
-> Nested Loop (cost=1.13..2649947.55 rows=148065 width=27)
(actual time=0.342..9071.892 rows=62257 loops=5)
-> Parallel Index Scan using idx_l_shipmode on lineitem
(cost=0.57..2543794.01 rows=148065 width=19) (actual time=0.284.
.7936.015 rows=62257 loops=5)
Index Cond: (l_shipmode = ANY ('{"REG
AIR",RAIL}'::bpchar[]))
Filter: ((l_commitdate < l_receiptdate) AND
(l_shipdate < l_commitdate) AND (l_receiptdate >= '1995-01-01'::date) AN
D (l_receiptdate < '1996-01-01 00:00:00'::timestamp without time zone))
Rows Removed by Filter: 3368075
-> Index Scan using orders_pkey on orders
(cost=0.56..0.71 rows=1 width=20) (actual time=0.016..0.016 rows=1 loops=31128
5)
Index Cond: (o_orderkey = lineitem.l_orderkey)
Planning time: 1.143 ms
Execution time: 45522.278 ms
(15 rows)
PATCH:
TPC-H queries:
\i /data/rafia.sabih/dss/queries/12.sql
QUERY PLAN
-----------------------------------------------------------------------------------------------------------------------------------------------
------------------------------------------------------------------------
Limit (cost=1001.19..391123.34 rows=1 width=27) (actual
time=14427.289..14427.290 rows=1 loops=1)
-> GroupAggregate (cost=1001.19..2731856.24 rows=7 width=27) (actual
time=14427.287..14427.287 rows=1 loops=1)
Group Key: lineitem.l_shipmode
-> Gather Merge (cost=1001.19..2721491.60 rows=592261 width=27)
(actual time=8.656..13469.925 rows=311095 loops=1)
Workers Planned: 4
Workers Launched: 4
-> Nested Loop (cost=1.13..2649947.55 rows=148065 width=27)
(actual time=0.418..14050.843 rows=67321 loops=5)
-> Parallel Index Scan using idx_l_shipmode on lineitem
(cost=0.57..2543794.01 rows=148065 width=19) (actual time=0.354.
.12291.338 rows=67321 loops=5)
Index Cond: (l_shipmode = ANY ('{"REG
AIR",RAIL}'::bpchar[]))
Filter: ((l_commitdate < l_receiptdate) AND
(l_shipdate < l_commitdate) AND (l_receiptdate >= '1995-01-01'::date) AN
D (l_receiptdate < '1996-01-01 00:00:00'::timestamp without time zone))
Rows Removed by Filter: 3639282
-> Index Scan using orders_pkey on orders
(cost=0.56..0.71 rows=1 width=20) (actual time=0.023..0.024 rows=1 loops=33660
6)
Index Cond: (o_orderkey = lineitem.l_orderkey)
Planning time: 1.201 ms
Execution time: 14569.550 ms
(15 rows)
change_parallel_tuple_q_size.patch
Description: Binary data
-- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
