From: Stephan Szabo <sszabo@megazone23.bigpanda.com>
To: Tom Lane <tgl@sss.pgh.pa.us>
Cc: Kevin Brown <kevin@sysexperts.com>
Cc: pgsql-hackers@postgresql.org
Subject: Re: [PERFORM] Foreign key performance
Date: Sat, 19 Apr 2003 12:03:02 -0700 (PDT)
Message-ID: <20030419114146.E18449-200000@megazone23.bigpanda.com> (raw)
In-Reply-To: <20030418080950.R2132-100000@megazone23.bigpanda.com>
On Fri, 18 Apr 2003, Stephan Szabo wrote:
> On Fri, 18 Apr 2003, Tom Lane wrote:
>
> > But the call counts do sound pretty damning.
>
> Yeah, but even with my hack last night it was still appreciably slower
> than immediate constraints. Comparing the call counts in that function
> for the immediate versus deferred(hacked) weren't giving me a good idea of
> where that time was going.
This last was due to assert checking I think. AllocSetCheck was the big
time waster on the hacked deferred case. Turning off assert checking I
get:
Median over 3 100000 inserts in one transaction (excepting the original
code which took a really long time so I ran it once) from time psql ...
No Fk 24.14s
Immediate FK 42.80s
Original Deferred FK 1862.06s
Hacked Deferred FK 35.30s
The hack was just the keeping around the list pointer from the last run
through (see attached - passed simple fk tests and regression, but there
might be problems I don't see). Looking at the code, I also wonder if we
would get some gain by not allocating the per_tuple_context at the
beginning but only when a non-deferred constraint is found since otherwise
we're creating and destroying the context and possibly never using it.
The cost would presumably be some boolean tests inside the inner loop.
Attachments:trigger.patch (text/plain, 3.7K) download
*** src/backend/commands/trigger.c 2003-04-19 12:41:24.000000000 -0700
--- src/backend/commands/trigger.hack1.c 2003-04-19 13:41:17.000000000 -0700
***************
*** 1626,1637 ****
--- 1626,1643 ----
* Because this can grow pretty large, we don't use separate List nodes,
* but instead thread the list through the dte_next fields of the member
* nodes. Saves just a few bytes per entry, but that adds up.
+ * + * deftrig_events_imm holds the tail pointer as of the last + * deferredTriggerInvokeEvents call, since generally immediate triggers+ * can't exist before this point (since they should already have been+ * called) excepting SET CONSTRAINTS
*
* XXX Need to be able to shove this data out to a file if it grows too
* large...
* ----------
*/
static DeferredTriggerEvent deftrig_events;
+ static DeferredTriggerEvent deftrig_events_imm;
static DeferredTriggerEvent deftrig_event_tail;
***************
*** 1844,1850 ****
static void
deferredTriggerInvokeEvents(bool immediate_only)
{
! DeferredTriggerEvent event,
prev_event = NULL;
MemoryContext per_tuple_context;
Relation rel = NULL;
--- 1850,1856 ----
static void
deferredTriggerInvokeEvents(bool immediate_only)
{
! DeferredTriggerEvent event = NULL,
prev_event = NULL;
MemoryContext per_tuple_context;
Relation rel = NULL;
***************
*** 1869,1880 ****
/* Make a per-tuple memory context for trigger function calls */
per_tuple_context =
AllocSetContextCreate(CurrentMemoryContext,
! "DeferredTriggerTupleContext",
! ALLOCSET_DEFAULT_MINSIZE,
! ALLOCSET_DEFAULT_INITSIZE,
! ALLOCSET_DEFAULT_MAXSIZE);
- event = deftrig_events;
while (event != NULL)
{
bool still_deferred_ones = false;
--- 1875,1897 ----
/* Make a per-tuple memory context for trigger function calls */
per_tuple_context =
AllocSetContextCreate(CurrentMemoryContext,
! "DeferredTriggerTupleContext",
! ALLOCSET_DEFAULT_MINSIZE,
! ALLOCSET_DEFAULT_INITSIZE,
! ALLOCSET_DEFAULT_MAXSIZE);
!
! /*
! * If immediate_only is true, then the only events that could have fired
! * are those since deftrig_events_imm.
! * The one exception to only since deftrig_events_imm would be
! * SET CONSTRAINTS ... IMMEDIATE so we set it to NULL in there
! * and treat NULL as being equivalent to the entire list.
! */
! if (immediate_only)
! event = deftrig_events_imm;
! if (event == NULL)
! event = deftrig_events;
while (event != NULL)
{
bool still_deferred_ones = false;
***************
*** 1993,2004 ****
--- 2010,2025 ----
/* Update list tail pointer in case we just deleted tail event */
deftrig_event_tail = prev_event;
+ /* Set the immediate event pointer to the tail */+ deftrig_events_imm = prev_event;+
/* Release working resources */
if (rel)
heap_close(rel, NoLock);
FreeTriggerDesc(trigdesc);
if (finfo)
pfree(finfo);
+
MemoryContextDelete(per_tuple_context);
}
***************
*** 2274,2279 ****
--- 2295,2308 ----
}
/*
+ * Reset the pointer for the immediate constraints to NULL (ie+ * use beginning of list). This is really only necessary for+ * dealing when setting to IMMEDIATE, and could really point+ * the first constraint we changed, but for now this will do.+ */+ deftrig_events_imm = NULL;+ + /*
* SQL99 requires that when a constraint is set to IMMEDIATE, any
* deferred checks against that constraint must be made when the SET
* CONSTRAINTS command is executed -- i.e. the effects of the SET
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: sszabo@megazone23.bigpanda.com, tgl@sss.pgh.pa.us, kevin@sysexperts.com, pgsql-hackers@postgresql.org
Subject: Re: [PERFORM] Foreign key performance
In-Reply-To: <20030419114146.E18449-200000@megazone23.bigpanda.com>
* 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