[POC] Faster processing at Gather node

Hello everybody,

While analysing the performance of TPC-H queries for the newly developed
parallel-operators, viz, parallel index, bitmap heap scan, etc. we noticed
that the time taken by gather node is significant. On investigation, as per
the current method it copies each tuple to the shared queue and notifies
the receiver. Since, this copying is done in shared queue, a lot of locking
and latching overhead is there.

So, in this POC patch I tried to copy all the tuples in a local queue thus
avoiding all the locks and latches. Once, the local queue is filled as per
it's capacity, tuples are transferred to the shared queue. Once, all the
tuples are transferred the receiver is sent the notification about the same.

With this patch I could see significant improvement in performance for
simple queries,

head:
explain analyse select * from t where i < 30000000;
QUERY PLAN

-----------------------------------------------------------------------------------------------------------------------------
Gather (cost=0.00..83225.55 rows=29676454 width=19) (actual
time=1.379..35871.235 rows=29999999 loops=1)
Workers Planned: 64
Workers Launched: 64
-> Parallel Seq Scan on t (cost=0.00..83225.55 rows=463695 width=19)
(actual time=0.125..1415.521 rows=461538 loops=65)
Filter: (i < 30000000)
Rows Removed by Filter: 1076923
Planning time: 0.180 ms
Execution time: 38503.478 ms
(8 rows)

patch:
explain analyse select * from t where i < 30000000;
QUERY PLAN

----------------------------------------------------------------------------------------------------------------------------
Gather (cost=0.00..83225.55 rows=29676454 width=19) (actual
time=0.980..24499.427 rows=29999999 loops=1)
Workers Planned: 64
Workers Launched: 64
-> Parallel Seq Scan on t (cost=0.00..83225.55 rows=463695 width=19)
(actual time=0.088..968.406 rows=461538 loops=65)
Filter: (i < 30000000)
Rows Removed by Filter: 1076923
Planning time: 0.158 ms
Execution time: 27331.849 ms
(8 rows)

head:
explain analyse select * from t where i < 40000000;
QUERY PLAN

-----------------------------------------------------------------------------------------------------------------------------
Gather (cost=0.00..83225.55 rows=39501511 width=19) (actual
time=0.890..38438.753 rows=39999999 loops=1)
Workers Planned: 64
Workers Launched: 64
-> Parallel Seq Scan on t (cost=0.00..83225.55 rows=617211 width=19)
(actual time=0.074..1235.180 rows=615385 loops=65)
Filter: (i < 40000000)
Rows Removed by Filter: 923077
Planning time: 0.113 ms
Execution time: 41609.855 ms
(8 rows)

patch:
explain analyse select * from t where i < 40000000;
QUERY PLAN

----------------------------------------------------------------------------------------------------------------------------
Gather (cost=0.00..83225.55 rows=39501511 width=19) (actual
time=1.085..31806.671 rows=39999999 loops=1)
Workers Planned: 64
Workers Launched: 64
-> Parallel Seq Scan on t (cost=0.00..83225.55 rows=617211 width=19)
(actual time=0.083..954.342 rows=615385 loops=65)
Filter: (i < 40000000)
Rows Removed by Filter: 923077
Planning time: 0.151 ms
Execution time: 35341.429 ms
(8 rows)

head:
explain analyse select * from t where i < 45000000;
QUERY PLAN

--------------------------------------------------------------------------------------------------------------------------------
Gather (cost=0.00..102756.80 rows=44584013 width=19) (actual
time=0.563..49156.252 rows=44999999 loops=1)
Workers Planned: 32
Workers Launched: 32
-> Parallel Seq Scan on t (cost=0.00..102756.80 rows=1393250 width=19)
(actual time=0.069..1905.436 rows=1363636 loops=33)
Filter: (i < 45000000)
Rows Removed by Filter: 1666667
Planning time: 0.106 ms
Execution time: 52722.476 ms
(8 rows)

patch:
explain analyse select * from t where i < 45000000;
QUERY PLAN

--------------------------------------------------------------------------------------------------------------------------------
Gather (cost=0.00..102756.80 rows=44584013 width=19) (actual
time=0.545..37501.200 rows=44999999 loops=1)
Workers Planned: 32
Workers Launched: 32
-> Parallel Seq Scan on t (cost=0.00..102756.80 rows=1393250 width=19)
(actual time=0.068..2165.430 rows=1363636 loops=33)
Filter: (i < 45000000)
Rows Removed by Filter: 1666667
Planning time: 0.087 ms
Execution time: 41458.969 ms
(8 rows)

The improvement in performance is most when the selectivity is around
20-30%, in which case currently parallelism is not selected.

I am testing the performance impact of this on TPC-H queries, in the
meantime would appreciate some feedback on the design, etc.

--
Regards,
Rafia Sabih
EnterpriseDB: http://www.enterprisedb.com/