From: Tom Lane <tgl@sss.pgh.pa.us>
To: Scott Marlowe <scott.marlowe@gmail.com>
cc: pgsql-performance <pgsql-performance@postgresql.org>
Subject: Re: Small performance regression in 9.2 has a big impact
In-reply-to: <21247.1416952515@sss.pgh.pa.us>
References: 
 <CAOR=d=3j1U_q-zf8+jUx1hkx8ps+N8pm=EUTqyFdJ5ov=+fawg@mail.gmail.com>
 <21247.1416952515@sss.pgh.pa.us>
Comments: In-reply-to Tom Lane <tgl@sss.pgh.pa.us>
 message dated "Tue, 25 Nov 2014 16:55:15 -0500"
Date: Tue, 25 Nov 2014 17:57:34 -0500
Message-ID: <23971.1416956254@sss.pgh.pa.us>
Precedence: bulk
Sender: pgsql-performance-owner@postgresql.org

I wrote:
> Scott Marlowe <scott.marlowe@gmail.com> 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@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-performance