pg.ddx.io  pgsql-performance@postgresql.org mailing list archive  
help / color / mirror / Atom feed
From: Bart Samwel <bsamwel@liacs.nl>
To: Greg Stark <gsstark@mit.edu>
Cc: bsamwel@xs4all.nl
Cc: pgsql-performance@postgresql.org
Subject: Re: Adding a foreign key constraint is extremely slow
Date: Wed, 26 Mar 2003 18:08:51 +0100
Message-ID: <3E81DEA3.7060305@liacs.nl> (raw)
In-Reply-To: <874r5qmctw.fsf@stark.dyndns.tv>
References: <20505.194.109.187.67.1048442304.squirrel@webmail.xs4all.nl>
	<874r5qmctw.fsf@stark.dyndns.tv>

Greg Stark wrote:
> bsamwel@xs4all.nl writes:
> 
> 
>>alter table triples add foreign key(id1) references wwwlog(id);
>>
>>PostgreSQL starts doing heavy work for at least one and a half hour, and I
>>broke it off at that. It is not possible to "explain" a statement like
>>this! Probably what it does is that it will check the foreign key
>>constraint for every field in the table. This will make it completely
>>impossible to load my data, because:
>>
>>(2) I cannot set the foreign key constraints AFTER loading the 0.9 million
>>records because I've got no clue at all how long this operation is going
>>to take.
> 
> 
> Try adding an index on wwwlog(id) so that it can check the constraint without
> doing a full table scan for each value being checked.

AFAIK, because wwwlog(id) is the primary key, this index already exists 
implicitly.  Still, 0.9 million separate index lookups are too slow for 
my purposes, if for example it takes something as low as 1 ms per lookup 
it will still take 900 seconds (= 15 minutes) to complete. As the 
complete adding of the foreign key constraint took about an hour, that 
would suggest an average of 4 ms per lookup, which suggests that the 
index is, in fact, present. :)

Anyway, I've actually waited for the operation to complete. The problem 
is out of my way for now.

Bart


-- 

Leiden Institute of Advanced Computer Science (http://www.liacs.nl)
E-mail: bsamwel@liacs.nl    Telephone: +31-71-5277037
Homepage: http://www.liacs.nl/~bsamwel
Opinions stated in this e-mail are mine and not necessarily my employer's.




view thread (4+ messages)

Message-ID: <3E81DEA3.7060305@liacs.nl>
Permalink:  ../3E81DEA3.7060305@liacs.nl/
Also on:    postgresql.org/message-id/3E81DEA3.7060305@liacs.nl

 · 

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 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: bsamwel@liacs.nl, gsstark@mit.edu, bsamwel@xs4all.nl
  Subject: Re: Adding a foreign key constraint is extremely slow
  In-Reply-To: <3E81DEA3.7060305@liacs.nl>
  References: <20505.194.109.187.67.1048442304.squirrel@webmail.xs4all.nl> <874r5qmctw.fsf@stark.dyndns.tv> <3E81DEA3.7060305@liacs.nl>

* 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