Andrew, > What I would like to have is a high level description of > . how the new text mode code differs from the old text mode code, and > . which part of the change is responsible for how much performance gain. > > Maybe I have missed that in previous discussion, but this change is > sufficiently invasive that I think you owe that to the reviewers.
You're right - this is a nearly full replacement of the code for the text mode, so some explanation is necessary. We have users that are focused on Business Intelligence and Data Warehousing use cases. They routinely load files of sizes upward of 1GB and some load n x 10GB per load. They normally use a text format because it's the easiest and fastest to produce and should also be fast to load. In the absence of a configurable format loader like Oracle's SQL*Loader, the COPY FROM path is what they use. We'll leave the formatting discussion for later, because there are definitely big improvements needed to serve this audience, but there is too much info to cover here before 8.1 freeze, so back to performance. Our customers noticed that PostgreSQL's COPY text performance was substantially slower than Oracle and far slower than the underlying disk subsystems. Typical performance ranged from 4MB/s to 8MB/s and was bottlenecked on CPU. Disk subsystems we use are typically capable of n x 100MB/s write rates. This was proving to be a big problem for daily update loads on warehouses, so we looked into what the slowdown was. We profiled the copy input path and found that the combination of per character I/O (fgetc, etc) and the subsequent per character logic was responsible for a large fraction of the time it took to load the data. The parsing routine was running at 17 MB/s on the fastest (Opteron) CPUs at very high compiler optimization, whether taking input from the network or from file on a backend COPY. Given other logic we've worked with for high performance I/O subsystems, we knew we could improve this to well over 100MB/s on typical machines, but it would require rewriting the logic to expose more to the compiler and CPU. An improvement would require exposing much more long runs of instructions with efficient access to memory to the compiler/CPU to allow for pipelining and micro-parallelism to become effective in order to reach higher I/O input rates. That is why the current patch reads a buffer of input, then scans for the "special" bytes (line ends, escapes, delimiters), avoiding copies until they can be efficiently done in bulk operations. The resulting code now runs at over 100MB/s, which exposes the remainder of the COPY path as the new bottleneck. Now we see between 20% and 90% performance improvements in COPY speed, which will make many customers in BI/DW very happy. I do not believe that significant performance gains can be made without this invasive change to the copy.c routine because of the need for extensive buffering and array logic, which is requires a substantially different organization of the processing. I hope this helps, - Luke ---------------------------(end of broadcast)--------------------------- TIP 9: In versions below 8.0, the planner will ignore your desire to choose an index scan if your joining column's datatypes do not match