PostgreSQL version: 11.2
Operating system: Linux
Description:
We have a wuite complex CTE which collects data fast enough for us and has a ok execution plan.
When we insert the result into a table like
With _some_data AS (
SELECT….
), _some_other_data AS (
SELECT ….
)
INSERT INTO table1
SELECT *
FROM _some_other_data
;
It works quite well and we are happy with it’s performance (arround 10 seconds).
But as soon as we add an ON CONFLICT clause (like below) the queries runs for ages and doesnt seem to stop. We usually terminate it after 12 Hours
With _some_data AS (
SELECT….
), _some_other_data AS (
SELECT ….
)
INSERT INTO table1
SELECT *
FROM _some_other_data
ON CONFLICT (column1, column2) DO
UPDATE
SET column1 = excluded.columnA,
column2 = excluded.columnB,
.
.
.
;
Where is the Problem?