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:NAME1  46218            ExecEndSeqScan:return Inline scan time 817290  46216
ExecEndDelegatedSeqScan:returnDelegated scan time 599030  46218            ExecEndSeqScan:return Inline scan time
957080 46216   ExecEndDelegatedSeqScan:return Delegated scan time 582550  46218            ExecEndSeqScan:return Inline
scantime 790280  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