
CREATE TABLE level1_table
(
      level1_no serial NOT NULL ,
      level1_node_name varchar(255),
       PRIMARY KEY (level1_no)
) WITHOUT OIDS;

CREATE TABLE level2_table
(
      level2_no serial NOT NULL ,
      parent_no int NOT NULL,
      level1_node_name varchar(255),
       PRIMARY KEY (level2_no)
) WITHOUT OIDS;

CREATE TABLE all_level_status
(
      level int NOT NULL,
      node_no int NOT NULL,
      status int,
       PRIMARY KEY (level, node_no)
) WITHOUT OIDS;

ALTER TABLE level2_table
        ADD FOREIGN KEY (parent_no)
        REFERENCES level1_table (level1_no)
        ON UPDATE RESTRICT
        ON DELETE RESTRICT
;

\timing on

--create test data
INSERT INTO level1_table(
            level1_no)
select generate_series(1,200);

INSERT INTO level2_table(
            level2_no, parent_no)
select
level2_no,level2_no/50+1 as parent_no
from
generate_series(1,9999) level2_no ;

INSERT INTO all_level_status(
            level, node_no, status)
select 1,level1_no,0
from level1_table;

INSERT INTO all_level_status(
            level, node_no, status)
select 2,level2_no,0
from level2_table;

INSERT INTO level1_table(
            level1_no)
select generate_series(201,1000000);


vacuum freeze analyze;
checkpoint;
vacuum analyze;
vacuum analyze;
checkpoint;

DELETE FROM level1_table WHERE level1_no BETWEEN 201 AND 1000000;
