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 |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-hackers pgsql-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
Attachment | Content-Type | Size |
---|---|---|
unknown_filename | text/plain | 13.0 KB |
From | Date | Subject | |
---|---|---|---|
Next Message | Tom Lane | 2007-11-27 02:19:48 | Re: [PATCHES] Fixes for MONEY type using locale |
Previous Message | John Walker | 2007-11-27 02:01:39 | developing for psqlodbc |
From | Date | Subject | |
---|---|---|---|
Next Message | Tom Lane | 2007-11-27 02:19:48 | Re: [PATCHES] Fixes for MONEY type using locale |
Previous Message | Bruce Momjian | 2007-11-26 23:32:41 | Re: [PATCHES] Fixes for MONEY type using locale |