pg.ddx.io  pgsql-performance@postgresql.org mailing list archive  
help / color / mirror / Atom feed
From: Richard van den Berg <richard.vandenberg@trust-factory.com>
To: PFC <lists@boutiquenumerique.com>
Cc: pgsql-performance@postgresql.org
Subject: Re: Foreign key slows down copy/insert
Date: Fri, 15 Apr 2005 10:14:28 +0200
Message-ID: <425F77E4.7030504@trust-factory.com> (raw)
In-Reply-To: <op.so8fvhlwth1vuj@localhost>
References: <425E6045.4060201@trust-factory.com>
	<op.so8fvhlwth1vuj@localhost>

PFC wrote:
>     You're using 7.4.5. It's possible that you have a type mismatch in 
> your  foreign keys which prevents use of the index on B.

I read about this pothole and made damn sure the types match. (Actually, 
I kinda hoped that was the problem, it would have been an easy fix.)

>     First of all, be really sure it's THAT foreign key, ie. do your COPY 
> with  only ONE foreign key at a time if you have several, and see which 
> one is  the killer.

I took exactly this route, and the first FK I tried already hit the 
jackpot. The real table had 4 FKs.

>     EXPLAIN ANALYZE the following :
> 
>     SELECT * FROM B WHERE id = (SELECT id FROM A LIMIT 1);
> 
>     It should use the index. Does it ?

It sure looks like it:

Index Scan using ix_B on B (cost=0.04..3.06 rows=1 width=329) (actual 
time=93.824..93.826 rows=1 loops=1)
    Index Cond: (id = $0)
    InitPlan
      ->  Limit  (cost=0.00..0.04 rows=1 width=4) (actual 
time=15.128..15.129 rows=1 loops=1)
            ->  Seq Scan on A  (cost=0.00..47569.70 rows=1135570 
width=4) (actual time=15.121..15.121 rows=1 loops=1)
  Total runtime: 94.109 ms

The real problem seems to be what Chris and Stephen pointed out: even 
though the FK check is deferred, it is done on a per-row bases. With 1M 
rows, this just takes forever.

Thanks for the help.

-- 
Richard van den Berg, CISSP
-------------------------------------------
Trust Factory B.V. |     www.dna-portal.net
Bazarstraat 44a    |  www.trust-factory.com
2518AK The Hague   |  Phone: +31 70 3620684
The Netherlands    |  Fax  : +31 70 3603009
-------------------------------------------



view thread (24+ messages)  latest in thread

Message-ID: <425F77E4.7030504@trust-factory.com>
Permalink:  ../425F77E4.7030504@trust-factory.com/
Also on:    postgresql.org/message-id/425F77E4.7030504@trust-factory.com

 · 

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: richard.vandenberg@trust-factory.com, lists@boutiquenumerique.com
  Subject: Re: Foreign key slows down copy/insert
  In-Reply-To: <425F77E4.7030504@trust-factory.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