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
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