endless quere when upsert with ON CONFLICT clause

Stephan Schmidt <schmidt@xxxxxxxxxx> · Fri, 29 Mar 2019 14:29:02 +0000

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?