0

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.

7
  • 1
    Because one INSERT inserting 1000 rows is faster than 1000 INSERTs inserting only one row. You don't need a loop for the first table insert into ... select ... from generate_series(1,1000) will also insert 1000 rows - but in a single statement and thus much faster
    – user1822
    Jun 14, 2022 at 13:08
  • You'd need to come up with a complete reproducible test case. Also, if the difference is in the millisecond range, as I guess, it could be random noise. Jun 14, 2022 at 15:14
  • @LaurenzAlbe I have added create table data and updated existing DML statements, to be close to actual data. My data is around hundred thousands. I tried with 1 million, and the time for Statement 2 was 7 mins. When I tried selecting Statement 2,3 & 4(Same data format as of Statement 3&2), the time went to 67 mins.
    – NiTiN
    Jun 14, 2022 at 15:39
  • @a_horse_with_no_name, Thanks. Statement 2, 3, 4.. and more is based on user input. If user says, 5, it goes on until Statement 6. Is there better way to do insert?
    – NiTiN
    Jun 14, 2022 at 15:46
  • 1
    @LaurenzAlbe I have added EDIT2 with the ouput.
    – NiTiN
    Jun 17, 2022 at 8:12

0

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge that you have read and understand our privacy policy and code of conduct.

Browse other questions tagged or ask your own question.