I am running following queries from DBeaver app connected to Postgres.
Statement1:
-- create 1000 rows in table2
do $$
declare
begin
for r in 1..1000 loop
insert into table2 (id, table1_id, table2_col3, table2_col4, table2_col5)
values ( uuid_generate_v4(), '5d353232-aa7e-4556-9cd3-3bdae43c25d4' , 'data', 0, true);
end loop;
end;
$$;
Statement 2 and 3 create data in table3 based upon the rows in table2 (table3 have FK-PK relation to table2)
Statement 2:
insert into table3 (
id, table2_id , table3_col3 , table3_col4
)
select uuid_generate_v4(), id , 'data' , 'another data' from table2 where table1_id = '5d353232-aa7e-4556-9cd3-3bdae43c25d4'
on conflict do nothing ;
Statement 3:
insert into table3 (
id, table2_id , table3_col3 , table3_col4
)
select uuid_generate_v4(), id , 'different data' , 'another different data' from table2 where table1_id = '5d353232-aa7e-4556-9cd3-3bdae43c25d4'
on conflict do nothing ;
So after the call finishes, I will have 1000 rows in table2
and 2000 rows in table3
.
The question I have is, when I execute Statement 2 and 3 individually, it takes far less time(1/3) than when I run by selecting both statements and click execute button. What could be the reason behind it?
I have experienced similar problem when I have written the above statements inside Stored Procedure and made a call to it.
PS: The code written is for creating sample data in database.
Edit 1: Table DDL statements
CREATE TABLE IF NOT EXISTS table1
(
id UUID PRIMARY KEY,
);
CREATE TABLE IF NOT EXISTS table2
(
id UUID PRIMARY KEY,
table1_id UUID NOT NULL REFERENCES table1,
table2_col3 TEXT NOT NULL,
table2_col4 INT,
table2_col5 BOOLEAN NOT NULL DEFAULT TRUE
);
CREATE TABLE IF NOT EXISTS table3
(
id UUID PRIMARY KEY,
table2_id UUID REFERENCES table2,
table3_col3 VARCHAR(7) NOT NULL,
table3_col4 TEXT NOT NULL,
);
Edit 2:
I had run EXPLAIN (ANALYZE, BUFFERS) STATEMENT1; STATEMENT2;
Following is the output:
Insert on table3 (cost=0.00..47810.00 rows=0 width=0) (actual time=1431686.852..1431686.853 rows=0 loops=1)
Conflict Resolution: NOTHING
Tuples Inserted: 1000000
Conflicting Tuples: 0
Buffers: shared hit=19518252 read=4863344 dirtied=3647588 written=2997004
-> Subquery Scan on "*SELECT*" (cost=0.00..47810.00 rows=1000000 width=96) (actual time=0.701..20941.791 rows=1000000 loops=1)
Buffers: shared hit=2 read=10308 written=3064
-> Seq Scan on table2 (cost=0.00..37810.00 rows=1000000 width=96) (actual time=0.700..20458.909 rows=1000000 loops=1)
Filter: (table1_id = '7a528463-1133-4720-8550-1760703e1717'::uuid)
Buffers: shared hit=2 read=10308 written=3064
Planning:
Buffers: shared hit=62 read=3 dirtied=1
Planning Time: 3.310 ms
Trigger for constraint table3_table2_id_fkey: time=19972.740 calls=1000000
Execution Time: 1451736.223 ms
After that I ran EXPLAIN (ANALYZE, BUFFERS) STATEMENT1;
and I get following output:
Insert on table3 (cost=0.00..47810.00 rows=0 width=0) (actual time=1502673.065..1502673.065 rows=0 loops=1)
Conflict Resolution: NOTHING
Tuples Inserted: 1000000
Conflicting Tuples: 0
Buffers: shared hit=19404094 read=4964519 dirtied=3746289 written=3042279
-> Subquery Scan on "*SELECT*" (cost=0.00..47810.00 rows=1000000 width=96) (actual time=0.318..19945.951 rows=1000000 loops=1)
Buffers: shared read=10310 written=3462
-> Seq Scan on table2 (cost=0.00..37810.00 rows=1000000 width=96) (actual time=0.317..19435.714 rows=1000000 loops=1)
Filter: (table1_id = '7a528463-1133-4720-8550-1760703e1717'::uuid)
Buffers: shared read=10310 written=3462
Planning Time: 0.066 ms
Trigger for constraint table3_table2_id_fkey: time=18026.509 calls=1000000
Execution Time: 1520767.185 ms
I feel that in first case, it shows output of first or second statement not both, because the times are almost similar.
insert into ... select ... from generate_series(1,1000)
will also insert 1000 rows - but in a single statement and thus much faster