pg.ddx.io pgsql-admin@postgresql.org mailing list archive
help / color / mirror / Atom feedSlow Foreign Key
5+ messages / 4 participants
[nested] [flat]
* Slow Foreign Key
@ 2004-03-22 18:24 Ricardo Vaz Mannrich <rvm_l1@silcom.com.br>
2004-03-22 20:56 ` Re: Slow Foreign Key Tom Lane <tgl@sss.pgh.pa.us>
0 siblings, 1 reply; 5+ messages in thread
From: Ricardo Vaz Mannrich @ 2004-03-22 18:24 UTC (permalink / raw)
To: pgsql-admin
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>
^ permalink raw reply [nested|flat] 5+ messages in thread
* Re: Slow Foreign Key
2004-03-22 18:24 Slow Foreign Key Ricardo Vaz Mannrich <rvm_l1@silcom.com.br>
@ 2004-03-22 20:56 ` Tom Lane <tgl@sss.pgh.pa.us>
2004-03-22 21:34 ` Re: Slow Foreign Key Sam Barnett-Cormack <s.barnett-cormack@lancaster.ac.uk>
0 siblings, 1 reply; 5+ messages in thread
From: Tom Lane @ 2004-03-22 20:56 UTC (permalink / raw)
To: Ricardo Vaz Mannrich <rvm_l1@silcom.com.br>; +Cc: pgsql-admin
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
^ permalink raw reply [nested|flat] 5+ messages in thread
* Re: Slow Foreign Key
2004-03-22 18:24 Slow Foreign Key Ricardo Vaz Mannrich <rvm_l1@silcom.com.br>
2004-03-22 20:56 ` Re: Slow Foreign Key Tom Lane <tgl@sss.pgh.pa.us>
@ 2004-03-22 21:34 ` Sam Barnett-Cormack <s.barnett-cormack@lancaster.ac.uk>
2004-03-23 11:32 ` Re: Slow Foreign Key Ricardo Vaz Mannrich <rvm_l1@silcom.com.br>
0 siblings, 1 reply; 5+ messages in thread
From: Sam Barnett-Cormack @ 2004-03-22 21:34 UTC (permalink / raw)
To: Tom Lane <tgl@sss.pgh.pa.us>; +Cc: Ricardo Vaz Mannrich <rvm_l1@silcom.com.br>; pgsql-admin
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
^ permalink raw reply [nested|flat] 5+ messages in thread
* Re: Slow Foreign Key
2004-03-22 18:24 Slow Foreign Key Ricardo Vaz Mannrich <rvm_l1@silcom.com.br>
2004-03-22 20:56 ` Re: Slow Foreign Key Tom Lane <tgl@sss.pgh.pa.us>
2004-03-22 21:34 ` Re: Slow Foreign Key Sam Barnett-Cormack <s.barnett-cormack@lancaster.ac.uk>
@ 2004-03-23 11:32 ` Ricardo Vaz Mannrich <rvm_l1@silcom.com.br>
2004-03-23 15:53 ` Re: Slow Foreign Key Bruno Wolff III <bruno@wolff.to>
0 siblings, 1 reply; 5+ messages in thread
From: Ricardo Vaz Mannrich @ 2004-03-23 11:32 UTC (permalink / raw)
To: Sam Barnett-Cormack <s.barnett-cormack@lancaster.ac.uk>; +Cc: pgsql-admin
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>
^ permalink raw reply [nested|flat] 5+ messages in thread
* Re: Slow Foreign Key
2004-03-22 18:24 Slow Foreign Key Ricardo Vaz Mannrich <rvm_l1@silcom.com.br>
2004-03-22 20:56 ` Re: Slow Foreign Key Tom Lane <tgl@sss.pgh.pa.us>
2004-03-22 21:34 ` Re: Slow Foreign Key Sam Barnett-Cormack <s.barnett-cormack@lancaster.ac.uk>
2004-03-23 11:32 ` Re: Slow Foreign Key Ricardo Vaz Mannrich <rvm_l1@silcom.com.br>
@ 2004-03-23 15:53 ` Bruno Wolff III <bruno@wolff.to>
0 siblings, 0 replies; 5+ messages in thread
From: Bruno Wolff III @ 2004-03-23 15:53 UTC (permalink / raw)
To: Ricardo Vaz Mannrich <rvm_l1@silcom.com.br>; +Cc: Sam Barnett-Cormack <s.barnett-cormack@lancaster.ac.uk>; pgsql-admin
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.
^ permalink raw reply [nested|flat] 5+ messages in thread
end of thread, other threads:[~2004-03-23 15:53 UTC | newest]
Thread overview: 5+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2004-03-22 18:24 Slow Foreign Key Ricardo Vaz Mannrich <rvm_l1@silcom.com.br>
2004-03-22 20:56 ` Tom Lane <tgl@sss.pgh.pa.us>
2004-03-22 21:34 ` Sam Barnett-Cormack <s.barnett-cormack@lancaster.ac.uk>
2004-03-23 11:32 ` Ricardo Vaz Mannrich <rvm_l1@silcom.com.br>
2004-03-23 15:53 ` Bruno Wolff III <bruno@wolff.to>
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