On Wed, Sep 21, 2011 at 8:47 AM, Greg Stark <st...@mit.edu> wrote: > On Wed, Sep 21, 2011 at 8:08 AM, Heikki Linnakangas > <heikki.linnakan...@enterprisedb.com> wrote: >>> How about almost every primary index creation? >> >> Nope. Swamped by everything else. > > Really? I think it's pretty common for shops to be able to dedicate > large amounts of RAM to building initial indexes on data loads or > reindex operations. Enough that they can cache the entire table for > the short time they're doing the index builds even if they're quite > large. Witness the recent pleas to allow maintenance_work_mem on the > order of tens of gigabytes. And it's also pretty common that shops can > dedicate very large I/O bandwidth, in many cases enough to saturate > the memory bandwidth, for doing these kinds of batch operations when > they get large enough to need to do an external sort. > > There's still overhead of reading the pages, the tuples, finding the > sort keys in the tuple, etc. But I think the actual qsort or heap > operations in tapesort are pretty big portions of the work. > > This is pretty easy to measure. Just run oprofile or gprof and see > what percentage of time for a big index build is spent in qsort.
+1 for some actual measurements. I don't think anyone on this thread is saying that if we can get big performance gains from doing this we still shouldn't do it. But at this point it's unclear that we can get a consistent speedup that isn't heavily dependent on the choice of compiler flags (to say nothing of compiler and OS), and even if we can, it's not clear that it will still be noticeable when you measure the run time of an entire query rather than just the speed of qsort(). Like Tom and Heikki, I'm a bit skeptical: it wouldn't surprise me to find out that qsort() is 5% of the runtime any realistic test case and the average qsort() speedup based on tests on a couple different platforms is 10% and so on average we're looking at a 0.5% improvement, in which case it might be more trouble than it's worth, especially if it turns out that there are OS/platform combinations where the inlined version is (for some crazy reason) slower. I've seen performance differences of up to 3% from minor code rearrangements that don't seem like they should matter at all, just because code and data shifts around across cache-line boundaries and the new arrangement is slightly better or worse than the old one. So if the performance improvement turns out to be very small, then validating that it actually IS an improvement in general is likely to be kind of a pain in the ass. On the other hand, the performance improvement might turn out to be large. Maybe there's a test case where, as Heikki suggests, 50% of the time is spent in qsort(). If we can reliably make that 25% faster, I wouldn't dismiss that out of hand; I think that would be pretty good, assuming it didn't require massive amounts of spaghetti code to make it work. I don't see that that would be any more marginal than the sorts of things we've optimized in, say, commit 4fc115b2e981f8c63165ca86a23215380a3fda66, or commit f4d242ef94730c447d87b9840a40b0ec3371fe0f. -- Robert Haas EnterpriseDB: http://www.enterprisedb.com The Enterprise PostgreSQL Company -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
