From: Keith Browne <tuxedo@deepsky.com>
To: Tom Lane <tgl@sss.pgh.pa.us>
To: pgsql-bugs@postgresql.org
Cc: andrew@supernews.com, 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:55:07 -0500
Message-ID: <4241D7AB.4030607@deepsky.com> (raw)
In-Reply-To: <13841.1111608763@sss.pgh.pa.us>
References: <20050318232102.08FA0F0E5D@svr2.postgresql.org>
<1111567230.11750.596.camel@localhost.localdomain>
<4241C1DF.2020000@deepsky.com>
<slrnd43hta.rhd.andrew+nonews@trinity.supernews.net>
<13841.1111608763@sss.pgh.pa.us>
Tom Lane wrote:
> 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.
Tom,
We're filling pairs of tables with rows having nearly a one-to-one
mapping; very rarely, the second table will have multiple rows
corresponding to one row in the first table. When we insert the first
row in the second table, therefore, we've just put the corresponding row
into the first table, so the foreign key constraint is satisfied.
I can't say how common this sort of thing will be. It appears to me
that BUG #1541 is similar to what we're seeing, and a search of the
mailing lists also turns up this message:
http://archives.postgresql.org/pgsql-performance/2004-11/msg00416.php
which also describes symptoms similar to what I'm seeing.
> 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.
It sounds like this could work for us, if it would disable sequential
searches into a table which grows from 0 to >60,000 rows in one session.
Is breaking and re-establishing the database session the best
workaround, or is there a better way to provide a hint to the planner?
Regards,
Keith Browne
tuxedo@deepsky.com
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: tuxedo@deepsky.com, tgl@sss.pgh.pa.us, pgsql-bugs@postgresql.org, fade@deepsky.com
Subject: Re: BUG #1552: massive performance hit between 7.4 and 8.0.1
In-Reply-To: <4241D7AB.4030607@deepsky.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