On Mon, 2003-01-20 at 15:47, Dave Page wrote: > > -----Original Message----- > > From: Tom Lane [mailto:[EMAIL PROTECTED]] > > Sent: 20 January 2003 15:28 > > To: Dave Page > > Cc: PostgreSQL Hackers Mailing List; Didier Moens > > Subject: Re: [HACKERS] Foreign key wierdness > > > > > > "Dave Page" <[EMAIL PROTECTED]> writes: > > > A pgAdmin user has noticed that Foreign Keys take > > significantly longer > > > to create when migrating a database in pgAdmin in v1.4.12 than in > > > v1.4.2. > > > > The only reason ADD FOREIGN KEY would take a long time is if > > (a) it has to wait awhile to get exclusive lock on either > > the referencing or referenced table; and/or > > (b) it takes a long time to verify that the existing entries > > in the referencing table all have matches in the referenced table. > > (that's the behind-the-scenes query you see) > > > > I'm betting that the table was busy, or there was a lot more > > data present in the one case, or you hadn't ever > > vacuumed/analyzed one or both tables and so a bad plan was > > chosen for the verification query. The schema reference is > > definitely not the issue. > > Thing is Tom, this issue can be reproduced *every* time, without fail. > The difference is huge as well, it's a difference of a couple of > seconds, the total migration will take around 1704.67 seconds without > schema qualification, and 11125.99 with schema qualification to quote > one test run.
can you try running ANALYZE (or VACUUM ANALYZE) after importing data but before creating the foreign keys ? > As I understand it, this has be tried on a test box, and a production > box (running RedHat builds of 7.3.1), and is a migration of the same > source Access database. > > I've been looking at his for some time now (couple of weeks or more), > and the only thing I can find is the SELECT ... FOR UPDATE in the > PostgreSQL logs that I quoted. does this SELECT ... FOR UPDATE occur only when schemas are used ? > These exactly follow *every* fkey > creation, and are definately not issued by pgAdmin. If they were issued > by another app or user, how come they exactly follow each fkey creation, > and are on the reference table of the fkey? I think Tom was trying to tell that the backend code indeed runs this, but that it should not be that slow. -- Hannu Krosing <[EMAIL PROTECTED]> ---------------------------(end of broadcast)--------------------------- TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]