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
Date: Tue, 25 Nov 2014 17:57:34 -0500
Message-ID: <23971.1416956254@sss.pgh.pa.us> (raw)
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>
List-Unsubscribe: <mailto:majordomo@postgresql.org?body=unsub%20pgsql-performance>
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
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: pgsql-performance@postgresql.org
Cc: tgl@sss.pgh.pa.us, scott.marlowe@gmail.com
Subject: Re: Small performance regression in 9.2 has a big impact
In-Reply-To: <23971.1416956254@sss.pgh.pa.us>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox