Skip site navigation (1) Skip section navigation (2)

Proposed patch for operator lookup caching

From: Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us>
To: pgsql-patches(at)postgreSQL(dot)org
Subject: Proposed patch for operator lookup caching
Date: 2007-11-27 02:13:46
Message-ID: 8740.1196129626@sss.pgh.pa.us (view raw or flat)
Thread:
Lists: pgsql-hackerspgsql-patches
Since Simon seems intent on hacking something in there, here is a patch
that I think is actually sane for improving operator lookup speed.
This patch caches all lookups, exact or ambiguous (since even the exact
ones require multiple cache searches in common cases); and behaves sanely
in the presence of search_path, pg_operator, or pg_cast changes.

I see about a 45% speedup (2110 vs 1445 tps) on Guillame Smet's test case.
On straight pgbench --- which has no ambiguous operators, plus it's not
read-only --- it's hard to measure any consistent speedup, but I can say
that it's not slower.  Some other test cases would be nice.

I went through the code that's being bypassed in some detail, to see what
dependencies were being skipped over.  I think that as long as we assume
that no *existing* type changes its domain base type, typtype, array
status, type category, or preferred-type status, we don't need to flush
the cache on pg_type changes.  This is a good thing since pg_type changes
frequently (eg, at temp table create or drop).

The only case that I believe to be unhandled is that the cache doesn't pay
attention to ALTER TABLE ... INHERIT / NO INHERIT events.  This means it
is theoretically possible to return the wrong operator if an operator
takes a complex type as input and the calling situation involves another
complex type whose inheritance relationship to that one changes.  That's
sufficiently far out of the normal case that I'm not very worried about it
(in fact, we probably have bugs in that area even without this patch,
since for instance cached plans don't respond to such changes either).
We could plug the hole by forcing a system-wide cache reset during ALTER
TABLE ... INHERIT / NO INHERIT, if anyone insists.

I'm not entirely happy about applying a patch like this so late in
the beta cycle, but I'd much rather do this than than any of the
less-than-half-baked ideas that have been floated in the discussion
so far.

			regards, tom lane

Responses

pgsql-hackers by date

Next:From: Tom LaneDate: 2007-11-27 02:19:48
Subject: Re: [PATCHES] Fixes for MONEY type using locale
Previous:From: John WalkerDate: 2007-11-27 02:01:39
Subject: developing for psqlodbc

pgsql-patches by date

Next:From: Tom LaneDate: 2007-11-27 02:19:48
Subject: Re: [PATCHES] Fixes for MONEY type using locale
Previous:From: Bruce MomjianDate: 2007-11-26 23:32:41
Subject: Re: [PATCHES] Fixes for MONEY type using locale

Privacy Policy | About PostgreSQL
Copyright © 1996-2014 The PostgreSQL Global Development Group