Re: tuplestore potential performance problem

"Hitoshi Harada" <umi.tanuki@gmail.com> writes:
> While attacking this issue(*1), I found that tuplestore that is on the
> file status has potential performance problem.

> The performance problem introduced by Heikki's new approach was caused
> by BufFile's frequent flush out in such cases like you put a new row
> into it and read middle row of it then put another row again, and so
> on. When tuplestore switches its internal mode from TSS_WRITEFILE to
> TSS_READFILE, underlying BufFile seeks to read pointer and flushes out
> its dirty buffer if the reading pointer is not near the writing
> pointer. Also, reading to writing switch avoids OS disk cache benefit.

> This is not critical in TSS_INMEM.

> So I decided to keep writing until finish if the tuplestore gets in
> file mode from memory mode rather than switching reading and writing
> randomly, which recovers the earlier performance almost. I am not sure
> but am afraid that the nodeCtescan also uses similar logic. Doesn't
> CTE have any problem for large data set?

If this means a lot of contortion/complication in the upper-level code,
seems like it'd be better to address the performance issue within
tuplestore/buffile.  We could keep separate buffers for write and read
perhaps.  But do you have real evidence of a performance problem?
I'd sort of expect the kernel's disk cache to mitigate this pretty well.
        regards, tom lane