pg.ddx.io  pgsql-performance@postgresql.org mailing list archive  
help / color / mirror / Atom feed
From: Andrew - Supernews <andrew+nonews@supernews.com>
To: pgsql-bugs@postgresql.org
Subject: Re: BUG #1552: massive performance hit between 7.4 and 8.0.1
Date: Wed, 23 Mar 2005 19:46:50 -0000
Message-ID: <slrnd43hta.rhd.andrew+nonews@trinity.supernews.net> (raw)
References: <20050318232102.08FA0F0E5D@svr2.postgresql.org>
	<1111567230.11750.596.camel@localhost.localdomain>
	<4241C1DF.2020000@deepsky.com>

On 2005-03-23, Keith Browne <tuxedo@deepsky.com> wrote:
> One other thing which puzzled me: as a test, I tried modifying our 
> script to spit out raw SQL statements instead of connecting to the 
> database and performing the inserts itself.  Normally, our script 
> populates two tables in one pass, and then populates the third and 
> fourth tables in a second pass.  I massaged the SQL by hand to group the 
> inserts together by table, so that the first table would be entirely 
> populated, then the second, etc.  When I ran this SQL script by piping 
> it straight into psql, it finished in about four minutes.

Changing the order so that the referenced table is fully populated, or at
least populated with more than a handful of pages of rows, before doing
_any_ insert on a referencing table in the same session will avoid the
misplan of the FK trigger queries, because when the first insert happens
on a referencing table, there will be no reason for the planner to prefer
a sequential scan. So this result is not surprising at all.

-- 
Andrew, Supernews
http://www.supernews.com - individual and corporate NNTP services



view thread (14+ messages)  latest in thread

Message-ID: <slrnd43hta.rhd.andrew+nonews@trinity.supernews.net>
Permalink:  ../slrnd43hta.rhd.andrew+nonews@trinity.supernews.net/
Also on:    postgresql.org/message-id/slrnd43hta.rhd.andrew+nonews@trinity.supernews.net

 · 

reply

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 from your mail client; the link fills in
  the headers below: reply via email
  Some clients, webmail especially, drop In-Reply-To and References from
  a link, and the reply then starts a new thread: use the mbox below.

  To: pgsql-performance@postgresql.org
  Cc: andrew+nonews@supernews.com, pgsql-bugs@postgresql.org
  Subject: Re: BUG #1552: massive performance hit between 7.4 and 8.0.1
  In-Reply-To: <slrnd43hta.rhd.andrew+nonews@trinity.supernews.net>
  References: <20050318232102.08FA0F0E5D@svr2.postgresql.org> <1111567230.11750.596.camel@localhost.localdomain> <4241C1DF.2020000@deepsky.com> <slrnd43hta.rhd.andrew+nonews@trinity.supernews.net>

* 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