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


> 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

	94 ms for an index scan ?
	this look really slow...

	was the index in the RAM cache ? does it fit ? is it faster the second  
time ? If it's still that slow, something somewhere is severely screwed.

	B has 150K rows you say, so everything about B should fit in RAM, and you  
should get 0.2 ms for an index scan, not 90 ms !
	Try this :

	Locate the files on disk which are involved in table B (table + indexes)  
looking at the system catalogs
	Look at the size of the files. Is the index severely bloated ? REINDEX ?  
DROP/Recreate the index ?
	Load them into the ram cache (just cat files | wc -b several times until  
it's almost instantaneous)
	Retry your query and your COPY

	I know it's stupid... but it's a lot faster to load an index in the cache  
by plainly reading the file rather than accessing it randomly.
	(even though, with this number of rows, it should not be THAT slow !)




view thread (24+ messages)  latest in thread

Message-ID: <op.so9q4exeth1vuj@localhost>
Permalink:  ../op.so9q4exeth1vuj@localhost/
Also on:    postgresql.org/message-id/op.so9q4exeth1vuj@localhost

 · 

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

* 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