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 -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
