I wrote: > Scott Marlowe <scott.marlowe@xxxxxxxxx> writes: >> OK so there's a simple set of tree functions we use at work. They're >> quite fast in 8.4 and they've gotten about 40% slower in 9.2. > Hmm, I don't like the trend here. For the repeat-1000x query, I get > these reported execution times: > 8.4 360 ms > 9.0 365 ms > 9.1 440 ms > 9.2 510 ms > 9.3 550 ms > 9.4 570 ms > head 570 ms I found part of the issue: you're doing a lot of UNIONs on varbit columns, and every time we parse one of those, get_sort_group_operators asks the typcache about hash operators for the type. typcache finds out that varbit has no default hash opclass ... but *it doesn't cache negative answers*. So that means a physical lookup in pg_opclass every time :-(. That is actually the only bufmgr traffic induced by this test query, once the catalog caches are loaded. Versions before 9.1 don't have that hit because they didn't consider hashing for UNIONs. I made a quick-hack patch to suppress redundant GetDefaultOpclass calls in typcache.c, and found that that brought HEAD's runtime down to 460ms. I don't think I'd want to commit this in its current form, but with some additions to flush the cache after pg_opclass updates it would be a credible improvement. So that probably explains the jump from 9.0 to 9.1. Don't know yet about the other lossage. regards, tom lane -- Sent via pgsql-performance mailing list (pgsql-performance@xxxxxxxxxxxxxx) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-performance
