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?
Regards,
*1:http://archives.postgresql.org/pgsql-hackers/2008-12/msg00077.php
--
Hitoshi Harada