Re: BUG #1552: massive performance hit between 7.4 and 8.0.1

On 2005-03-23, Keith Browne <tuxedo(at)deepsky(dot)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