Re: Support Parallel Query Execution in Executor

Gregory Maxwell wrote:
> We should consider true parallel execution and overlapping execution
> with I/O as distinct cases.
>
> For example, one case made in this thread involved bursty performance
> with seqscans presumably because the I/O was stalling while processing
> was being performed. In general this can be avoided without parallel
> execution through the use of non-blocking I/O and making an effort to
> keep the request pipeline full.
>
> There are other cases where it is useful to perform parallel I/O
> without parallel processing.. for example: a query that will perform
> an index lookup per row can benefit from running some number of those
> lookups in parallel in order to hide the lookup latency and give the
> OS and disk elevators a chance to make the random accesses a little
> more orderly. This can be accomplished without true parallel
> processing. (Perhaps PG does this already?)
>
>

I have done some testing more along these lines with an old fork of
postgres code
(2001). In my tests, I used a thread to delegate out the actual heap
scan of the
SeqScan. The job of the "slave" thread the was to fault in buffer
pages and
determine the time validity of the tuples. ItemPointers are passed back
to the
"master" thread via a common memory area guarded by mutex locking. The
master thread is then responsible for converting the ItemPointers to
HeapTuples
and finishing the execution run. I added a little hack to the buffer
code to force
pages read into the buffer to stay at the back of the free buffer list
until the master
thread has had a chance to use it. These are the parameters of my test
table.

Pages 9459: ; Tup 961187: Live 673029, Dead 288158

Average tuple size is 70 bytes

create table test (rand int, varchar(256) message)

So far I've done a couple of runs with a single query on a 2 processor
machine with
the following results via dtrace.

select * from test;

CPU ID FUNCTION:NAME
1 46218 ExecEndSeqScan:return Inline scan time 81729
0 46216 ExecEndDelegatedSeqScan:return Delegated scan time 59903
0 46218 ExecEndSeqScan:return Inline scan time 95708
0 46216 ExecEndDelegatedSeqScan:return Delegated scan time 58255
0 46218 ExecEndSeqScan:return Inline scan time 79028
0 46216 ExecEndDelegatedSeqScan:return Delegated scan time 50500

average 34% decrease in total time using the delegated scan.

A very crude, simple test but I think it shows some promise.

I know I used threads but you could probably just as easily use a slave
process
and pass ItemPointers via pipes or shared memory.

Thanks,

Myron Scott