--
-- INSERT UPDATE, test explain command, comes from merge_explain and merge_explain_pretty
--
-- initial
CREATE SCHEMA test_insert_update_008;
SET current_schema = test_insert_update_008;
-- SET enable_upsert_to_merge=ON to test the upsert implemented by merge,
-- real upsert will be tested in specialized case.
SET enable_upsert_to_merge TO ON;
CREATE TABLE products_base
(
product_id INTEGER DEFAULT 0,
product_name VARCHAR(60) DEFAULT 'null',
category VARCHAR(60) DEFAULT 'unknown',
total INTEGER DEFAULT '0'
);
INSERT INTO products_base VALUES (1501, 'vivitar 35mm', 'electrncs', 100);
INSERT INTO products_base VALUES (1502, 'olympus is50', 'electrncs', 100);
INSERT INTO products_base VALUES (1600, 'play gym', 'toys', 100);
INSERT INTO products_base VALUES (1601, 'lamaze', 'toys', 100);
INSERT INTO products_base VALUES (1666, 'harry potter', 'dvd', 100);
CREATE TABLE newproducts_base
(
product_id INTEGER DEFAULT 0,
product_name VARCHAR(60) DEFAULT 'null',
category VARCHAR(60) DEFAULT 'unknown',
total INTEGER DEFAULT '0'
);
INSERT INTO newproducts_base VALUES (1502, 'olympus camera', 'electrncs', 200);
INSERT INTO newproducts_base VALUES (1601, 'lamaze', 'toys', 200);
INSERT INTO newproducts_base VALUES (1666, 'harry potter', 'toys', 200);
INSERT INTO newproducts_base VALUES (1700, 'wait interface', 'books', 200);
ANALYZE products_base;
ANALYZE newproducts_base;
--
-- row table
--
CREATE TABLE products_row
(
product_id INTEGER DEFAULT 0 PRIMARY KEY,
product_name VARCHAR(60) DEFAULT 'null',
category VARCHAR(60) DEFAULT 'unknown',
total INTEGER DEFAULT '0'
);
NOTICE: CREATE TABLE / PRIMARY KEY will create implicit index "products_row_pkey" for table "products_row"
CREATE TABLE newproducts_row
(
product_id INTEGER DEFAULT 0 PRIMARY KEY,
product_name VARCHAR(60) DEFAULT 'null',
category VARCHAR(60) DEFAULT 'unknown',
total INTEGER DEFAULT '0'
);
NOTICE: CREATE TABLE / PRIMARY KEY will create implicit index "newproducts_row_pkey" for table "newproducts_row"
INSERT INTO products_row SELECT * FROM products_base;
INSERT INTO newproducts_row SELECT * FROM newproducts_base;
ANALYZE products_row;
ANALYZE newproducts_row;
SET explain_perf_mode = normal;
-- explain verbose
EXPLAIN (VERBOSE on, COSTS off)
INSERT INTO products_row
SELECT product_id, product_name, category, total FROM newproducts_row
ON DUPLICATE KEY UPDATE
product_name = excluded.product_name,
category = excluded.category,
total = excluded.total;
QUERY PLAN
-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
Merge on test_insert_update_008.products_row
-> Hash Right Join
Output: newproducts_row.product_id, newproducts_row.product_name, newproducts_row.category, newproducts_row.total, products_row.product_id, products_row.product_name, products_row.category, products_row.total, products_row.ctid, newproducts_row.ctid
Hash Cond: (products_row.product_id = newproducts_row.product_id)
-> Seq Scan on test_insert_update_008.products_row
Output: products_row.product_id, products_row.product_name, products_row.category, products_row.total, products_row.ctid
-> Hash
Output: newproducts_row.product_id, newproducts_row.product_name, newproducts_row.category, newproducts_row.total, newproducts_row.ctid
-> Seq Scan on test_insert_update_008.newproducts_row
Output: newproducts_row.product_id, newproducts_row.product_name, newproducts_row.category, newproducts_row.total, newproducts_row.ctid
(10 rows)
EXPLAIN (VERBOSE on, COSTS off)
INSERT INTO products_row
SELECT newproducts_row.product_id,
newproducts_row.product_name,
newproducts_row.category,
newproducts_row.total
FROM newproducts_row, products_row
WHERE products_row.total + newproducts_row.total < 1000
ON DUPLICATE KEY UPDATE
product_name = excluded.product_name,
category = excluded.category,
total = excluded.total;
QUERY PLAN
-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
Merge on test_insert_update_008.products_row
-> Nested Loop
Output: newproducts_row.product_id, newproducts_row.product_name, newproducts_row.category, newproducts_row.total, products_row.product_id, products_row.product_name, products_row.category, products_row.total, products_row.ctid, newproducts_row.ctid, test_insert_update_008.products_row.ctid
Join Filter: ((test_insert_update_008.products_row.total + newproducts_row.total) < 1000)
-> Seq Scan on test_insert_update_008.products_row
Output: test_insert_update_008.products_row.ctid, test_insert_update_008.products_row.total
-> Materialize
Output: newproducts_row.product_id, newproducts_row.product_name, newproducts_row.category, newproducts_row.total, newproducts_row.ctid, products_row.product_id, products_row.product_name, products_row.category, products_row.total, products_row.ctid
-> Hash Right Join
Output: newproducts_row.product_id, newproducts_row.product_name, newproducts_row.category, newproducts_row.total, newproducts_row.ctid, products_row.product_id, products_row.product_name, products_row.category, products_row.total, products_row.ctid
Hash Cond: (products_row.product_id = newproducts_row.product_id)
-> Seq Scan on test_insert_update_008.products_row
Output: products_row.product_id, products_row.product_name, products_row.category, products_row.total, products_row.ctid
-> Hash
Output: newproducts_row.product_id, newproducts_row.product_name, newproducts_row.category, newproducts_row.total, newproducts_row.ctid
-> Seq Scan on test_insert_update_008.newproducts_row
Output: newproducts_row.product_id, newproducts_row.product_name, newproducts_row.category, newproducts_row.total, newproducts_row.ctid
(17 rows)
EXPLAIN (VERBOSE on, COSTS off)
INSERT INTO products_row
SELECT product_id, product_name, category, total
FROM newproducts_row WHERE product_id IS NOT NULL AND product_name IS NOT NULL
ON DUPLICATE KEY UPDATE
product_name = excluded.product_name,
category = excluded.category,
total = excluded.total;
QUERY PLAN
-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
Merge on test_insert_update_008.products_row
-> Hash Right Join
Output: newproducts_row.product_id, newproducts_row.product_name, newproducts_row.category, newproducts_row.total, products_row.product_id, products_row.product_name, products_row.category, products_row.total, products_row.ctid, newproducts_row.ctid
Hash Cond: (products_row.product_id = newproducts_row.product_id)
-> Seq Scan on test_insert_update_008.products_row
Output: products_row.product_id, products_row.product_name, products_row.category, products_row.total, products_row.ctid
-> Hash
Output: newproducts_row.product_id, newproducts_row.product_name, newproducts_row.category, newproducts_row.total, newproducts_row.ctid
-> Seq Scan on test_insert_update_008.newproducts_row
Output: newproducts_row.product_id, newproducts_row.product_name, newproducts_row.category, newproducts_row.total, newproducts_row.ctid
Filter: ((newproducts_row.product_id IS NOT NULL) AND (newproducts_row.product_name IS NOT NULL))
(11 rows)
-- explain analyze
BEGIN;
EXPLAIN (ANALYZE on, COSTS off, TIMING off)
INSERT INTO products_row
SELECT product_id, product_name, category, total FROM newproducts_row
ON DUPLICATE KEY UPDATE
product_name = excluded.product_name,
category = excluded.category,
total = excluded.total;
QUERY PLAN
---------------------------------------------------------------------------
Merge on products_row (actual rows=4 loops=1)
Merge Inserted: 1
Merge Updated: 3
-> Hash Right Join (actual rows=4 loops=1)
Hash Cond: (products_row.product_id = newproducts_row.product_id)
-> Seq Scan on products_row (actual rows=5 loops=1)
-> Hash (actual rows=4 loops=1)
Buckets: 32768 Batches: 1 Memory Usage: 1kB
-> Seq Scan on newproducts_row (actual rows=4 loops=1)
--? Total runtime: .* ms
(10 rows)
ROLLBACK;
-- explain performance
\o insert_update_explain.txt
BEGIN;
EXPLAIN PERFORMANCE
INSERT INTO products_row
SELECT product_id, product_name, category, total FROM newproducts_row
ON DUPLICATE KEY UPDATE
product_name = excluded.product_name,
category = excluded.category,
total = excluded.total;
ROLLBACK;
\o
-- explain analyze
BEGIN;
EXPLAIN (ANALYZE on, COSTS off, TIMING off)
INSERT INTO products_row
SELECT product_id, product_name, category, total FROM newproducts_row
ON DUPLICATE KEY UPDATE
product_name = excluded.product_name,
category = excluded.category,
total = excluded.total;
QUERY PLAN
---------------------------------------------------------------------------
Merge on products_row (actual rows=4 loops=1)
Merge Inserted: 1
Merge Updated: 3
-> Hash Right Join (actual rows=4 loops=1)
Hash Cond: (products_row.product_id = newproducts_row.product_id)
-> Seq Scan on products_row (actual rows=5 loops=1)
-> Hash (actual rows=4 loops=1)
Buckets: 32768 Batches: 1 Memory Usage: 1kB
-> Seq Scan on newproducts_row (actual rows=4 loops=1)
--? Total runtime: .* ms
(10 rows)
ROLLBACK;
-- pretty mode performance
SET explain_perf_mode = pretty;
-- explain verbose
EXPLAIN (VERBOSE on, COSTS off)
INSERT INTO products_row
SELECT product_id, product_name, category, total FROM newproducts_row
ON DUPLICATE KEY UPDATE
product_name = excluded.product_name,
category = excluded.category,
total = excluded.total;
QUERY PLAN
-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
Merge on test_insert_update_008.products_row
-> Hash Right Join
Output: newproducts_row.product_id, newproducts_row.product_name, newproducts_row.category, newproducts_row.total, products_row.product_id, products_row.product_name, products_row.category, products_row.total, products_row.ctid, newproducts_row.ctid
Hash Cond: (products_row.product_id = newproducts_row.product_id)
-> Seq Scan on test_insert_update_008.products_row
Output: products_row.product_id, products_row.product_name, products_row.category, products_row.total, products_row.ctid
-> Hash
Output: newproducts_row.product_id, newproducts_row.product_name, newproducts_row.category, newproducts_row.total, newproducts_row.ctid
-> Seq Scan on test_insert_update_008.newproducts_row
Output: newproducts_row.product_id, newproducts_row.product_name, newproducts_row.category, newproducts_row.total, newproducts_row.ctid
(10 rows)
-- explain analyze
BEGIN;
EXPLAIN (ANALYZE on, COSTS off, TIMING off)
INSERT INTO products_row
SELECT product_id, product_name, category, total FROM newproducts_row
ON DUPLICATE KEY UPDATE
product_name = excluded.product_name,
category = excluded.category,
total = excluded.total;
QUERY PLAN
---------------------------------------------------------------------------
Merge on products_row (actual rows=4 loops=1)
Merge Inserted: 1
Merge Updated: 3
-> Hash Right Join (actual rows=4 loops=1)
Hash Cond: (products_row.product_id = newproducts_row.product_id)
-> Seq Scan on products_row (actual rows=5 loops=1)
-> Hash (actual rows=4 loops=1)
Buckets: 32768 Batches: 1 Memory Usage: 1kB
-> Seq Scan on newproducts_row (actual rows=4 loops=1)
--? Total runtime: .* ms
(10 rows)
ROLLBACK;
-- explain analyze
BEGIN;
EXPLAIN (ANALYZE on, COSTS off, TIMING off)
INSERT INTO products_row
SELECT product_id, product_name, category, total FROM newproducts_row
ON DUPLICATE KEY UPDATE
product_name = excluded.product_name,
category = excluded.category,
total = excluded.total;
QUERY PLAN
---------------------------------------------------------------------------
Merge on products_row (actual rows=4 loops=1)
Merge Inserted: 1
Merge Updated: 3
-> Hash Right Join (actual rows=4 loops=1)
Hash Cond: (products_row.product_id = newproducts_row.product_id)
-> Seq Scan on products_row (actual rows=5 loops=1)
-> Hash (actual rows=4 loops=1)
Buckets: 32768 Batches: 1 Memory Usage: 1kB
-> Seq Scan on newproducts_row (actual rows=4 loops=1)
--? Total runtime: .* ms
(10 rows)
ROLLBACK;
-- explain performance
\o insert_update_explain_pretty.txt
BEGIN;
EXPLAIN PERFORMANCE
INSERT INTO products_row
SELECT product_id, product_name, category, total FROM newproducts_row
ON DUPLICATE KEY UPDATE
product_name = excluded.product_name,
category = excluded.category,
total = excluded.total;
ROLLBACK;
SET explain_perf_mode = run;
BEGIN;
EXPLAIN PERFORMANCE
INSERT INTO products_row
SELECT product_id, product_name, category, total FROM newproducts_row
ON DUPLICATE KEY UPDATE
product_name = excluded.product_name,
category = excluded.category,
total = excluded.total;
ROLLBACK;
SET explain_perf_mode = summary;
BEGIN;
EXPLAIN PERFORMANCE
INSERT INTO products_row
SELECT product_id, product_name, category, total FROM newproducts_row
ON DUPLICATE KEY UPDATE
product_name = excluded.product_name,
category = excluded.category,
total = excluded.total;
ROLLBACK;
\o
CREATE TABLE item
(
a INT DEFAULT 3,
item_id NUMERIC(18,10),
item_name VARCHAR(100),
item_level NUMERIC(39,0),
item_desc VARCHAR(250),
item_subclass_cd VARCHAR(50),
item_type_cd VARCHAR(50),
inventory_ind CHAR(300),
vendor_party_id SMALLINT,
commodity_cd VARCHAR(50),
brand_cd VARCHAR(50),
item_available CHAR(100),
CONSTRAINT u_item_index UNIQUE (item_subclass_cd, vendor_party_id)
)
PARTITION BY RANGE (vendor_party_id)
(
PARTITION item_1 VALUES LESS THAN (0),
PARTITION item_2 VALUES LESS THAN (1),
PARTITION item_3 VALUES LESS THAN (2),
PARTITION item_4 VALUES LESS THAN (3),
PARTITION item_5 VALUES LESS THAN (6),
PARTITION item_6 VALUES LESS THAN (8),
PARTITION item_7 VALUES LESS THAN (10),
PARTITION item_8 VALUES LESS THAN (15),
PARTITION item_9 VALUES LESS THAN (MAXVALUE)
) ENABLE ROW MOVEMENT;
NOTICE: CREATE TABLE / UNIQUE will create implicit index "u_item_index" for table "item"
CREATE TABLE region
(
a INT DEFAULT 8,
region_cd VARCHAR(50),
region_name VARCHAR(100),
division_cd VARCHAR(50),
region_mgr_associate_id number(18,9)
);
CREATE TABLE associate_benefit_expense
(
a INT DEFAULT 44,
period_end_dt DATE,
associate_expns_type_cd VARCHAR(50),
associate_party_id INTEGER,
benefit_hours_qty decimal(38,11),
benefit_cost_amt number(38,4)
)
PARTITION BY RANGE (associate_expns_type_cd)
(
PARTITION associate_benefit_expense_1 VALUES LESS THAN ('B'),
PARTITION associate_benefit_expense_2 VALUES LESS THAN ('E'),
PARTITION associate_benefit_expense_3 VALUES LESS THAN ('G'),
PARTITION associate_benefit_expense_4 VALUES LESS THAN ('I'),
PARTITION associate_benefit_expense_5 VALUES LESS THAN ('L'),
PARTITION associate_benefit_expense_6 VALUES LESS THAN ('N'),
PARTITION associate_benefit_expense_7 VALUES LESS THAN ('P'),
PARTITION associate_benefit_expense_8 VALUES LESS THAN ('Q'),
PARTITION associate_benefit_expense_9 VALUES LESS THAN ('R'),
PARTITION associate_benefit_expense_10 VALUES LESS THAN ('T'),
PARTITION associate_benefit_expense_11 VALUES LESS THAN ('U'),
PARTITION associate_benefit_expense_12 VALUES LESS THAN ('V'),
PARTITION associate_benefit_expense_13 VALUES LESS THAN (MAXVALUE)
) ENABLE ROW MOVEMENT;
INSERT INTO item (ITEM_ID, ITEM_NAME, ITEM_DESC, ITEM_SUBCLASS_CD, ITEM_TYPE_CD, INVENTORY_IND, VENDOR_PARTY_ID, COMMODITY_CD, BRAND_CD,ITEM_AVAILABLE) VALUES (0.12, ' ' , 'A' , NULL, 'TGK' , 'A' , 2, 'A' , 'A' , 'Y');
INSERT INTO item (ITEM_ID, ITEM_NAME, ITEM_DESC, ITEM_SUBCLASS_CD, ITEM_TYPE_CD, INVENTORY_IND, VENDOR_PARTY_ID, COMMODITY_CD, BRAND_CD,ITEM_AVAILABLE) VALUES (1.3, 'B' , NULL, 'B' , 'B' , NULL, 1, 'B' , NULL , 'N');
INSERT INTO item (ITEM_ID, ITEM_NAME, ITEM_DESC, ITEM_SUBCLASS_CD, ITEM_TYPE_CD, INVENTORY_IND, VENDOR_PARTY_ID, COMMODITY_CD, BRAND_CD,ITEM_AVAILABLE) VALUES (2.23, 'C' , 'C' , NULL, 'C' , 'C' , 2, 'C' , 'C' , 'N');
INSERT INTO item (ITEM_ID, ITEM_NAME, ITEM_DESC, ITEM_SUBCLASS_CD, ITEM_TYPE_CD, INVENTORY_IND, VENDOR_PARTY_ID, COMMODITY_CD, BRAND_CD,ITEM_AVAILABLE) VALUES (3.33, 'D' , 'D' , 'PT' , NULL, 'D' , 3, 'D' , 'D' , 'N');
INSERT INTO item (ITEM_ID, ITEM_NAME, ITEM_DESC, ITEM_SUBCLASS_CD, ITEM_TYPE_CD, INVENTORY_IND, VENDOR_PARTY_ID, COMMODITY_CD, BRAND_CD,ITEM_AVAILABLE) VALUES (4.98, ' ' , NULL, 'E' , 'E' , 'E' , 4, 'E' , 'E' , 'Y');
INSERT INTO item (ITEM_ID, ITEM_NAME, ITEM_DESC, ITEM_SUBCLASS_CD, ITEM_TYPE_CD, INVENTORY_IND, VENDOR_PARTY_ID, COMMODITY_CD, BRAND_CD,ITEM_AVAILABLE) VALUES (5.01, NULL, 'F' , ' ' , 'F' , 'F' , 5, 'F' , 'F' , 'N');
INSERT INTO item (ITEM_ID, ITEM_NAME, ITEM_DESC, ITEM_SUBCLASS_CD, ITEM_TYPE_CD, INVENTORY_IND, VENDOR_PARTY_ID, COMMODITY_CD, BRAND_CD,ITEM_AVAILABLE) VALUES (6, 'G' , 'G' , 'G' , '_D' , 'G' , 6, 'G' , NULL ,'N');
INSERT INTO item (ITEM_ID, ITEM_NAME, ITEM_DESC, ITEM_SUBCLASS_CD, ITEM_TYPE_CD, INVENTORY_IND, VENDOR_PARTY_ID, COMMODITY_CD, BRAND_CD,ITEM_AVAILABLE) VALUES (0.7, NULL, NULL, NULL, 'H' , 'H' , 7, NULL, 'G' , 'Y');
INSERT INTO item (ITEM_ID, ITEM_NAME, ITEM_DESC, ITEM_SUBCLASS_CD, ITEM_TYPE_CD, INVENTORY_IND, VENDOR_PARTY_ID, COMMODITY_CD, BRAND_CD,ITEM_AVAILABLE) VALUES (0.08, 'I' , ' ' , ' T ' , NULL, 'I' , 8, 'I' , '' , 'N');
INSERT INTO item (ITEM_ID, ITEM_NAME, ITEM_DESC, ITEM_SUBCLASS_CD, ITEM_TYPE_CD, INVENTORY_IND, VENDOR_PARTY_ID, COMMODITY_CD, BRAND_CD,ITEM_AVAILABLE) VALUES (9.12, ' ' , 'J' , ' PP' , 'J' , 'J' , 9, 'J' , NULL , 'Y');
INSERT INTO item (ITEM_ID, ITEM_NAME, ITEM_DESC, ITEM_SUBCLASS_CD, ITEM_TYPE_CD, INVENTORY_IND, VENDOR_PARTY_ID, COMMODITY_CD, BRAND_CD,ITEM_AVAILABLE) VALUES (10.10, NULL, ' ' , 'A' , 'A' , 'A' , 2, NULL, 'A','Y');
--INSERT INTO item (ITEM_ID, ITEM_NAME, ITEM_DESC, ITEM_SUBCLASS_CD, ITEM_TYPE_CD, INVENTORY_IND, VENDOR_PARTY_ID, COMMODITY_CD, BRAND_CD,ITEM_AVAILABLE) VALUES (11.11, 'B' , 'B' , 'B' , 'BCDAA' , NULL, 1, 'B' , 'B','N');
INSERT INTO item (ITEM_ID, ITEM_NAME, ITEM_DESC, ITEM_SUBCLASS_CD, ITEM_TYPE_CD, INVENTORY_IND, VENDOR_PARTY_ID, COMMODITY_CD, BRAND_CD,ITEM_AVAILABLE) VALUES (12.02, 'D' , NULL, NULL, 'C' , 'C' , 2, 'C' , 'C','N');
INSERT INTO item (ITEM_ID, ITEM_NAME, ITEM_DESC, ITEM_SUBCLASS_CD, ITEM_TYPE_CD, INVENTORY_IND, VENDOR_PARTY_ID, COMMODITY_CD, BRAND_CD,ITEM_AVAILABLE) VALUES (13.99, NULL, ' ' , 'D' , 'D' , 'D' , 3, 'D' , 'D','Y');
--INSERT INTO item (ITEM_ID, ITEM_NAME, ITEM_DESC, ITEM_SUBCLASS_CD, ITEM_TYPE_CD, INVENTORY_IND, VENDOR_PARTY_ID, COMMODITY_CD, BRAND_CD,ITEM_AVAILABLE) VALUES (14, 'G' , 'E' , 'E' , NULL, 'E' , 4, 'E' , 'E','N');
INSERT INTO item (ITEM_ID, ITEM_NAME, ITEM_DESC, ITEM_SUBCLASS_CD, ITEM_TYPE_CD, INVENTORY_IND, VENDOR_PARTY_ID, COMMODITY_CD, BRAND_CD,ITEM_AVAILABLE) VALUES (15, 'F' , ' ' , 'C' , 'CLEANING' , 'F' , 5, 'F' , 'F','Y');
INSERT INTO item (ITEM_ID, ITEM_NAME, ITEM_DESC, ITEM_SUBCLASS_CD, ITEM_TYPE_CD, INVENTORY_IND, VENDOR_PARTY_ID, COMMODITY_CD, BRAND_CD,ITEM_AVAILABLE) VALUES (16, '' , 'Z' , NULL, 'G' , 'G' , 6, 'G' , NULL,'N');
INSERT INTO item (ITEM_ID, ITEM_NAME, ITEM_DESC, ITEM_SUBCLASS_CD, ITEM_TYPE_CD, INVENTORY_IND, VENDOR_PARTY_ID, COMMODITY_CD, BRAND_CD,ITEM_AVAILABLE) VALUES (17, NULL, '' , ' PAPER' , 'H' , '' , 7, NULL, NULL,'Y');
INSERT INTO item (ITEM_ID, ITEM_NAME, ITEM_DESC, ITEM_SUBCLASS_CD, ITEM_TYPE_CD, INVENTORY_IND, VENDOR_PARTY_ID, COMMODITY_CD, BRAND_CD,ITEM_AVAILABLE) VALUES (19, ' ' , 'B' , '' , '' , 'I' , 8, 'I' , NULL,'N');
INSERT INTO item (ITEM_ID, ITEM_NAME, ITEM_DESC, ITEM_SUBCLASS_CD, ITEM_TYPE_CD, INVENTORY_IND, VENDOR_PARTY_ID, COMMODITY_CD, BRAND_CD,ITEM_AVAILABLE) VALUES (20 , 'A' , 'J' , 'J' , 'J' , NULL, 9, 'J' , 'G','Y');
/*--REGION--*/
INSERT INTO REGION (REGION_CD, REGION_NAME, DIVISION_CD, REGION_MGR_ASSOCIATE_ID) VALUES ('A', 'A ', 'A', 0.123433);
INSERT INTO REGION (REGION_CD, REGION_NAME, DIVISION_CD, REGION_MGR_ASSOCIATE_ID) VALUES ('B', 'B', 'B', NULL);
INSERT INTO REGION (REGION_CD, REGION_NAME, DIVISION_CD, REGION_MGR_ASSOCIATE_ID) VALUES ('C', 'C', 'C', 2.232008908);
INSERT INTO REGION (REGION_CD, REGION_NAME, DIVISION_CD, REGION_MGR_ASSOCIATE_ID) VALUES ('D', ' DD', 'D', 3.878789);
INSERT INTO REGION (REGION_CD, REGION_NAME, DIVISION_CD, REGION_MGR_ASSOCIATE_ID) VALUES ('E', 'A', 'E', 4.89060603);
INSERT INTO REGION (REGION_CD, REGION_NAME, DIVISION_CD, REGION_MGR_ASSOCIATE_ID) VALUES ('F', 'F', 'F', 5.82703827);
INSERT INTO REGION (REGION_CD, REGION_NAME, DIVISION_CD, REGION_MGR_ASSOCIATE_ID) VALUES ('G', 'G', 'TTT', NULL);
INSERT INTO REGION (REGION_CD, REGION_NAME, DIVISION_CD, REGION_MGR_ASSOCIATE_ID) VALUES ('H', 'H', 'G', 7.3829083);
INSERT INTO REGION (REGION_CD, REGION_NAME, DIVISION_CD, REGION_MGR_ASSOCIATE_ID) VALUES ('I', 'C', 'M', 8.983989);
INSERT INTO REGION (REGION_CD, REGION_NAME, DIVISION_CD, REGION_MGR_ASSOCIATE_ID) VALUES ('J', 'J', 'G', NULL);
INSERT INTO REGION (REGION_CD, REGION_NAME, DIVISION_CD, REGION_MGR_ASSOCIATE_ID) VALUES ('K', ' ', 'C', 2.232008908);
INSERT INTO REGION (REGION_CD, REGION_NAME, DIVISION_CD, REGION_MGR_ASSOCIATE_ID) VALUES ('L', 'D', 'X', 3.878789);
INSERT INTO REGION (REGION_CD, REGION_NAME, DIVISION_CD, REGION_MGR_ASSOCIATE_ID) VALUES ('M', 'TTTTTT ', 'D' , 4.89060603);
INSERT INTO REGION (REGION_CD, REGION_NAME, DIVISION_CD, REGION_MGR_ASSOCIATE_ID) VALUES ('N', 'G' , 'B' , NULL);
INSERT INTO REGION (REGION_CD, REGION_NAME, DIVISION_CD, REGION_MGR_ASSOCIATE_ID) VALUES ('O' , 'G', 'F', 6.6703972);
INSERT INTO ASSOCIATE_BENEFIT_EXPENSE (PERIOD_END_DT, ASSOCIATE_EXPNS_TYPE_CD, ASSOCIATE_PARTY_ID, BENEFIT_HOURS_QTY, BENEFIT_COST_AMT) VALUES (DATE '1970-01-01', 'A', 5, 0.5 , 0.5);
INSERT INTO ASSOCIATE_BENEFIT_EXPENSE (PERIOD_END_DT, ASSOCIATE_EXPNS_TYPE_CD, ASSOCIATE_PARTY_ID, BENEFIT_HOURS_QTY, BENEFIT_COST_AMT) VALUES (DATE '1973-01-01', 'B', 1, NULL, 1.0);
INSERT INTO ASSOCIATE_BENEFIT_EXPENSE (PERIOD_END_DT, ASSOCIATE_EXPNS_TYPE_CD, ASSOCIATE_PARTY_ID, BENEFIT_HOURS_QTY, BENEFIT_COST_AMT) VALUES (DATE '1976-01-01', 'C', 2, 2.0 , NULL);
INSERT INTO ASSOCIATE_BENEFIT_EXPENSE (PERIOD_END_DT, ASSOCIATE_EXPNS_TYPE_CD, ASSOCIATE_PARTY_ID, BENEFIT_HOURS_QTY, BENEFIT_COST_AMT) VALUES (DATE '1979-01-01', 'D', 3, 3.0 , 3.0);
INSERT INTO ASSOCIATE_BENEFIT_EXPENSE (PERIOD_END_DT, ASSOCIATE_EXPNS_TYPE_CD, ASSOCIATE_PARTY_ID, BENEFIT_HOURS_QTY, BENEFIT_COST_AMT) VALUES (DATE '1982-01-01', 'E', 4, 4.0 , 4.0);
INSERT INTO ASSOCIATE_BENEFIT_EXPENSE (PERIOD_END_DT, ASSOCIATE_EXPNS_TYPE_CD, ASSOCIATE_PARTY_ID, BENEFIT_HOURS_QTY, BENEFIT_COST_AMT) VALUES (DATE '1985-01-01', 'F', 5, 5.0 , 5.0);
INSERT INTO ASSOCIATE_BENEFIT_EXPENSE (PERIOD_END_DT, ASSOCIATE_EXPNS_TYPE_CD, ASSOCIATE_PARTY_ID, BENEFIT_HOURS_QTY, BENEFIT_COST_AMT) VALUES (DATE '1988-01-01', 'F', 6, NULL, 6.0);
INSERT INTO ASSOCIATE_BENEFIT_EXPENSE (PERIOD_END_DT, ASSOCIATE_EXPNS_TYPE_CD, ASSOCIATE_PARTY_ID, BENEFIT_HOURS_QTY, BENEFIT_COST_AMT) VALUES (DATE '1991-01-01', 'G', 6, NULL, NULL);
INSERT INTO ASSOCIATE_BENEFIT_EXPENSE (PERIOD_END_DT, ASSOCIATE_EXPNS_TYPE_CD, ASSOCIATE_PARTY_ID, BENEFIT_HOURS_QTY, BENEFIT_COST_AMT) VALUES (DATE '1994-01-01', 'G', 15, 8.0 , 8.0);
INSERT INTO ASSOCIATE_BENEFIT_EXPENSE (PERIOD_END_DT, ASSOCIATE_EXPNS_TYPE_CD, ASSOCIATE_PARTY_ID, BENEFIT_HOURS_QTY, BENEFIT_COST_AMT) VALUES (DATE '1997-01-01', 'G', 16, 9.0 , 9.0);
INSERT INTO ASSOCIATE_BENEFIT_EXPENSE (PERIOD_END_DT, ASSOCIATE_EXPNS_TYPE_CD, ASSOCIATE_PARTY_ID, BENEFIT_HOURS_QTY, BENEFIT_COST_AMT) VALUES (DATE '1983-01-03', 'I', 14, 4.0 , 4.0);
INSERT INTO ASSOCIATE_BENEFIT_EXPENSE (PERIOD_END_DT, ASSOCIATE_EXPNS_TYPE_CD, ASSOCIATE_PARTY_ID, BENEFIT_HOURS_QTY, BENEFIT_COST_AMT) VALUES (DATE '1984-01-01', 'GO', 15, 5.0 , NULL);
INSERT INTO ASSOCIATE_BENEFIT_EXPENSE (PERIOD_END_DT, ASSOCIATE_EXPNS_TYPE_CD, ASSOCIATE_PARTY_ID, BENEFIT_HOURS_QTY, BENEFIT_COST_AMT) VALUES (DATE '1985-05-01', 'I', 16, 6.0 , 6.0);
INSERT INTO ASSOCIATE_BENEFIT_EXPENSE (PERIOD_END_DT, ASSOCIATE_EXPNS_TYPE_CD, ASSOCIATE_PARTY_ID, BENEFIT_HOURS_QTY, BENEFIT_COST_AMT) VALUES (DATE '1990-01-01', 'TTT', 16, NULL, 7.0);
INSERT INTO ASSOCIATE_BENEFIT_EXPENSE (PERIOD_END_DT, ASSOCIATE_EXPNS_TYPE_CD, ASSOCIATE_PARTY_ID, BENEFIT_HOURS_QTY, BENEFIT_COST_AMT) VALUES (DATE '1992-02-01', 'A', 15, 8.0 , 8.0);
INSERT INTO ASSOCIATE_BENEFIT_EXPENSE (PERIOD_END_DT, ASSOCIATE_EXPNS_TYPE_CD, ASSOCIATE_PARTY_ID, BENEFIT_HOURS_QTY, BENEFIT_COST_AMT) VALUES (DATE '1997-02-01', 'G', 17, 9.0 , NULL);
INSERT INTO ASSOCIATE_BENEFIT_EXPENSE (PERIOD_END_DT, ASSOCIATE_EXPNS_TYPE_CD, ASSOCIATE_PARTY_ID, BENEFIT_HOURS_QTY, BENEFIT_COST_AMT) VALUES (DATE '1997-05-01', 'G' , 17, 9.0 , NULL);
ANALYZE item;
ANALYZE region;
ANALYZE associate_benefit_expense;
EXPLAIN (VERBOSE ON, COSTS OFF)
INSERT INTO item (item_level, item_subclass_cd, item_desc, vendor_party_id)
SELECT Table_001.REGION_MGR_ASSOCIATE_ID Column_003,
Table_002.associate_expns_type_cd Column_004,
CAST(Table_001.region_name AS VARCHAR) Column_005,
10 Column_006
-- 'o' Column_007,
-- 'F' Column_008,
-- pg_client_encoding() Column_009
FROM region Table_001, associate_benefit_expense Table_002
ON DUPLICATE KEY UPDATE item_level = -1000;
QUERY PLAN
-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
Merge on test_insert_update_008.item
-> Nested Loop
Output: table_001.region_mgr_associate_id, table_002.associate_expns_type_cd, table_001.region_name, 10, item.a, item.item_id, item.item_name, item.item_level, item.item_desc, item.item_subclass_cd, item.item_type_cd, item.inventory_ind, item.vendor_party_id, item.commodity_cd, item.brand_cd, item.item_available, item.ctid, item.tableoid, table_001.ctid, table_002.ctid, table_002.tableoid
-> Hash Left Join
Output: table_002.associate_expns_type_cd, table_002.ctid, table_002.tableoid, item.a, item.item_id, item.item_name, item.item_level, item.item_desc, item.item_subclass_cd, item.item_type_cd, item.inventory_ind, item.vendor_party_id, item.commodity_cd, item.brand_cd, item.item_available, item.ctid, item.tableoid
Hash Cond: ((table_002.associate_expns_type_cd)::text = (item.item_subclass_cd)::text)
-> Partition Iterator
Output: table_002.associate_expns_type_cd, table_002.ctid, table_002.tableoid
Iterations: 13
-> Partitioned Seq Scan on test_insert_update_008.associate_benefit_expense table_002
Output: table_002.associate_expns_type_cd, table_002.ctid, table_002.tableoid
Selected Partitions: 1..13
-> Hash
Output: item.a, item.item_id, item.item_name, item.item_level, item.item_desc, item.item_subclass_cd, item.item_type_cd, item.inventory_ind, item.vendor_party_id, item.commodity_cd, item.brand_cd, item.item_available, item.ctid, item.tableoid
-> Partition Iterator
Output: item.a, item.item_id, item.item_name, item.item_level, item.item_desc, item.item_subclass_cd, item.item_type_cd, item.inventory_ind, item.vendor_party_id, item.commodity_cd, item.brand_cd, item.item_available, item.ctid, item.tableoid
Iterations: 1
-> Partitioned Seq Scan on test_insert_update_008.item
Output: item.a, item.item_id, item.item_name, item.item_level, item.item_desc, item.item_subclass_cd, item.item_type_cd, item.inventory_ind, item.vendor_party_id, item.commodity_cd, item.brand_cd, item.item_available, item.ctid, item.tableoid
Filter: (item.vendor_party_id = 10)
Selected Partitions: 8
-> Materialize
Output: table_001.region_mgr_associate_id, table_001.region_name, table_001.ctid
-> Seq Scan on test_insert_update_008.region table_001
Output: table_001.region_mgr_associate_id, table_001.region_name, table_001.ctid
(25 rows)
EXPLAIN (VERBOSE ON, COSTS OFF)
INSERT INTO products_row VALUES(100)
ON DUPLICATE KEY UPDATE total=100;
QUERY PLAN
--------------------------------------------------------------------------------------------------------------------------------------------------------------------
Merge on test_insert_update_008.products_row
-> Hash Right Join
Output: "*VALUES*".column1, products_row.product_id, products_row.product_name, products_row.category, products_row.total, products_row.ctid, "*VALUES*".*
Hash Cond: (products_row.product_id = "*VALUES*".column1)
-> Seq Scan on test_insert_update_008.products_row
Output: products_row.product_id, products_row.product_name, products_row.category, products_row.total, products_row.ctid
-> Hash
Output: "*VALUES*".column1, "*VALUES*".*
-> Values Scan on "*VALUES*"
Output: "*VALUES*".column1, "*VALUES*".*
(10 rows)
SET enable_light_proxy=off;
EXPLAIN (VERBOSE ON, COSTS OFF)
INSERT INTO products_row VALUES(100)
ON DUPLICATE KEY UPDATE total=100;
QUERY PLAN
--------------------------------------------------------------------------------------------------------------------------------------------------------------------
Merge on test_insert_update_008.products_row
-> Hash Right Join
Output: "*VALUES*".column1, products_row.product_id, products_row.product_name, products_row.category, products_row.total, products_row.ctid, "*VALUES*".*
Hash Cond: (products_row.product_id = "*VALUES*".column1)
-> Seq Scan on test_insert_update_008.products_row
Output: products_row.product_id, products_row.product_name, products_row.category, products_row.total, products_row.ctid
-> Hash
Output: "*VALUES*".column1, "*VALUES*".*
-> Values Scan on "*VALUES*"
Output: "*VALUES*".column1, "*VALUES*".*
(10 rows)
EXPLAIN (VERBOSE ON, COSTS OFF)
INSERT INTO products_row VALUES(100)
ON DUPLICATE KEY UPDATE total=100;
QUERY PLAN
--------------------------------------------------------------------------------------------------------------------------------------------------------------------
Merge on test_insert_update_008.products_row
-> Hash Right Join
Output: "*VALUES*".column1, products_row.product_id, products_row.product_name, products_row.category, products_row.total, products_row.ctid, "*VALUES*".*
Hash Cond: (products_row.product_id = "*VALUES*".column1)
-> Seq Scan on test_insert_update_008.products_row
Output: products_row.product_id, products_row.product_name, products_row.category, products_row.total, products_row.ctid
-> Hash
Output: "*VALUES*".column1, "*VALUES*".*
-> Values Scan on "*VALUES*"
Output: "*VALUES*".column1, "*VALUES*".*
(10 rows)
EXPLAIN (VERBOSE ON, COSTS OFF)
INSERT INTO products_row VALUES(100)
ON DUPLICATE KEY UPDATE total=100;
QUERY PLAN
--------------------------------------------------------------------------------------------------------------------------------------------------------------------
Merge on test_insert_update_008.products_row
-> Hash Right Join
Output: "*VALUES*".column1, products_row.product_id, products_row.product_name, products_row.category, products_row.total, products_row.ctid, "*VALUES*".*
Hash Cond: (products_row.product_id = "*VALUES*".column1)
-> Seq Scan on test_insert_update_008.products_row
Output: products_row.product_id, products_row.product_name, products_row.category, products_row.total, products_row.ctid
-> Hash
Output: "*VALUES*".column1, "*VALUES*".*
-> Values Scan on "*VALUES*"
Output: "*VALUES*".column1, "*VALUES*".*
(10 rows)
RESET enable_light_proxy;
DROP SCHEMA test_insert_update_008 CASCADE;
NOTICE: drop cascades to 7 other objects
DETAIL: drop cascades to table products_base
drop cascades to table newproducts_base
drop cascades to table products_row
drop cascades to table newproducts_row
drop cascades to table item
drop cascades to table region
drop cascades to table associate_benefit_expense