Re: SELECT INTO large FKyed table is slow

[Mladen Gogala <mladen.gogala@xxxxxxxxxxx>]

Mario Splivalo wrote:

Yes, as Mladen Gogala had advised. No noticable change in performance -
it's still slow :)
  

Declaring constraints as deferrable doesn't do anything as such, you have to actually set the constraints deferred to have an effect. You have to do it within a transaction block. If done outside of the transaction block, there is no effect:

This is what happens when "set constraints" is issued outside the transaction block:

< constraint test1_pk primary key(col1) deferrable);ÂÂÂÂÂÂÂÂÂÂÂ
NOTICE:Â CREATE TABLE / PRIMARY KEY will create implicit index "test1_pk" for table "test1"
CREATE TABLE
Time: 41.218 ms
scott=# set constraints all deferred;ÂÂÂÂÂÂÂÂÂÂÂÂÂÂÂÂÂÂÂÂÂÂÂÂÂÂ
SET CONSTRAINTS
Time: 0.228 ms
scott=# begin;ÂÂÂÂÂÂÂÂÂÂÂÂÂÂÂÂÂÂÂÂÂÂÂÂÂÂÂÂÂÂÂÂÂÂÂÂÂ
BEGIN
Time: 0.188 ms
scott=#Â insert into test1 values(1);ÂÂÂÂÂÂÂÂÂÂÂÂÂÂ
INSERT 0 1
Time: 0.929 ms
scott=#Â insert into test1 values(1);ÂÂ
ERROR:Â duplicate key value violates unique constraint "test1_pk"
DETAIL:Â Key (col1)=(1) already exists.
scott=# end;
ROLLBACK
Time: 0.267 ms
scott=#

It works like a charm when issued within the transaction block:

scott=# begin;ÂÂÂÂÂÂÂÂÂÂÂÂÂÂÂÂÂÂÂÂÂÂÂÂÂ
BEGIN
Time: 0.202 ms
scott=# set constraints all deferred;ÂÂ
SET CONSTRAINTS
Time: 0.196 ms
scott=#Â insert into test1 values(1);ÂÂ
INSERT 0 1
Time: 0.334 ms
scott=#Â insert into test1 values(1);ÂÂ
INSERT 0 1
Time: 0.327 ms
scott=# end;
ERROR:Â duplicate key value violates unique constraint "test1_pk"
DETAIL:Â Key (col1)=(1) already exists.
scott=#

I was able to insert the same value twice, it only failed at the end of the transaction.

But, just for the sake of clarification - I tought that DEFERRABLE would
matter if I do a lot of INSERTs, inside a FOR loop or something like
that. Since I'm doing INSERT INTO ... SELECT, does it makes any difference?
  

You cannot tell which part takes a long time, select or insert, without profiling. I certainly cannot do it over the internet.

-- 
 
Mladen Gogala 
Sr. Oracle DBA
1500 Broadway
New York, NY 10036
(212) 329-5251
http://www.vmsinfo.com 
The Leader in Integrated Media Intelligence Solutions