Обсуждение: Slow Foreign Key

I'm trying to do a lot of inserts on a detail table, but with foreign
key schema it's too slow.

I made few tests.

1) Master table with 290,000 rows and 4 columns (primary key is SERIAL)
2) Detail table now with 1,300,000 rows and 3 columns (primary key is
SERIAL and I have a column master_id here pointing to a master table
record).
3) If I use:
    ALTER TABLE detail ADD CONSTRAINT master_fk FOREIGN KEY master_id
REFERENCES master

I get 5 INSERTs per second using this:

INSERT INTO master VALUES (DEFAULT, 'Random 1', 'Random 2', 'Random 3');
SELECT CURRVAL('master_master_id_seq');
INSERT INTO detail VALUES (DEFAULT, XX, 999);
(random number of detail records, alternating with a insert into master)

I can insert just 5 rows/second.

4) So, I change it:
    ALTER TABLE detail DROP CONSTRAINT master_fk;

And run INSERTs again.

Now, I have 1000 rows/second (with SELECT checking before every INSERT).

Is FOREIGN KEY so slow like that?  What can I change in PostgreSQL
configuration?

Note that I run some VACUUM and CLUSTER. This let my INSERTs a very
little fast.

My (test) system is:

Linux 2.4.21
PostgreSQL 7.4.1
Pentium IV 1.8Ghz
256MB RAM
HD IDE

Could anyone help me?

--
Ricardo Vaz Mannrich <rvm_l1@silcom.com.br>

Ricardo Vaz Mannrich <rvm_l1@silcom.com.br> writes:
> Is FOREIGN KEY so slow like that?

Not normally.  Have you checked that the referencing and referenced
columns are of the same datatype?  Have you done ANALYZE on both tables
recently?

            regards, tom lane

On Mon, 22 Mar 2004, Tom Lane wrote:

> Ricardo Vaz Mannrich <rvm_l1@silcom.com.br> writes:
> > Is FOREIGN KEY so slow like that?
>
> Not normally.  Have you checked that the referencing and referenced
> columns are of the same datatype?  Have you done ANALYZE on both tables
> recently?

Other question that seems, from my experience, to be relevant - are the
referenced/referencing columns indexed?

--

Sam Barnett-Cormack
Software Developer                           |  Student of Physics & Maths
UK Mirror Service (http://www.mirror.ac.uk)  |  Lancaster University

Both indexed...

But... When I try EXPLAIN SELECT * FROM detail WHERE master_id = XXXXXX;
PostgreSQL uses seq scan (why?)

It's very confusing.

Em Seg, 2004-03-22 às 18:34, Sam Barnett-Cormack escreveu:
> On Mon, 22 Mar 2004, Tom Lane wrote:
>
> > Ricardo Vaz Mannrich <rvm_l1@silcom.com.br> writes:
> > > Is FOREIGN KEY so slow like that?
> >
> > Not normally.  Have you checked that the referencing and referenced
> > columns are of the same datatype?  Have you done ANALYZE on both tables
> > recently?
>
> Other question that seems, from my experience, to be relevant - are the
> referenced/referencing columns indexed?
--
Ricardo Vaz Mannrich <rvm_l1@silcom.com.br>

On Tue, Mar 23, 2004 at 08:32:30 -0300,
  Ricardo Vaz Mannrich <rvm_l1@silcom.com.br> wrote:
> Both indexed...
>
> But... When I try EXPLAIN SELECT * FROM detail WHERE master_id = XXXXXX;
> PostgreSQL uses seq scan (why?)

This has been discussed many times previously and if you want to understand
why things are this way search the archives.

Things will work better in 7.5.

For the time being quoting XXXXX will solve your problem as it will
allow the constant to get the same type as master_id.