*UPSERT OPTIMIZE*

I noticed/tested that is much faster for INSERTS (have yet to test UPSERTS) to use a WHERE NOT EXISTS in addition to ON CONFLICT. Typically about 3x faster than just allowing the ON CONFLICT to handle existence checks. I think this may carry over into UPSERTS, making it likely faster to do an INSERT and then and UPDATE. Here is my test for inserts only...

--so i can keep rerunning
DROP TABLE if exists temp1;
DROP TABLE if exists temp2;

--create a billion rows
SELECT GENERATE_SERIES AS id INTO TEMP temp1
FROM GENERATE_SERIES(1, 10000000);

CREATE UNIQUE INDEX ux_id ON temp1(id);
ALTER TABLE temp1 CLUSTER ON ux_id;

--create a second table to insert from, with the same data
SELECT * INTO TEMP temp2
FROM temp1;

CREATE UNIQUE INDEX ux_id2 ON temp2(id);
ALTER TABLE temp2 CLUSTER ON ux_id2;

--test inserting with on conflict only
INSERT INTO temp1(id)
SELECT id
FROM temp2 ON conflict DO nothing;
--execution time: 14.71s (1million rows)

--test inserting with not exists and on conflict
INSERT INTO temp1(id)
SELECT t2.id
FROM temp2 t2
WHERE NOT EXISTS (SELECT 1 FROM temp1 t1 WHERE t2.id = t1.id)
ON conflict DO nothing;
--execution time: 5.78s (1million rows)

❌ ⏳...