pg.ddx.io  pgsql-performance@postgresql.org mailing list archive  
help / color / mirror / Atom feed
From: Tom Lane <tgl@sss.pgh.pa.us>
To: andrew@supernews.com
Cc: pgsql-bugs@postgresql.org, Keith Browne <tuxedo@deepsky.com>
Cc: Brian O'Reilly <fade@deepsky.com>
Subject: Re: BUG #1552: massive performance hit between 7.4 and 8.0.1
Date: Wed, 23 Mar 2005 15:12:43 -0500
Message-ID: <13841.1111608763@sss.pgh.pa.us> (raw)
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>

Andrew - Supernews <andrew+nonews@supernews.com> writes:
> 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.

I'm still looking for an example that demonstrates why this is a common
problem that we need to worry about.  ISTM that if an FK reference is
hit when there are still zero entries in the referenced table, that
insertion will fail anyway, and so people wouldn't try to load data in
such an order.

In the long term it would be good to replan the FK plans when the
referenced tables have grown so much that the plan ought to change.
Once we have the plan invalidation machinery that Neil is working on,
it might be fairly practical to do that; but no such thing is going
to appear in existing release branches of course.

We could band-aid this in 8.0 as previously suggested (have the planner
assume > 0 pages when it sees actually 0 pages) but without seeing a
concrete example I can't tell if that will fix the complaint or not.

			regards, tom lane



view thread (14+ messages)  latest in thread

Message-ID: <13841.1111608763@sss.pgh.pa.us>
Permalink:  ../13841.1111608763@sss.pgh.pa.us/
Also on:    postgresql.org/message-id/13841.1111608763@sss.pgh.pa.us

 · 

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 using the --to and --cc options:
  reply via email

  To: pgsql-performance@postgresql.org
  Cc: tgl@sss.pgh.pa.us, andrew@supernews.com, tuxedo@deepsky.com, fade@deepsky.com
  Subject: Re: BUG #1552: massive performance hit between 7.4 and 8.0.1
  In-Reply-To: <13841.1111608763@sss.pgh.pa.us>

* 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