endless quere when upsert with ON CONFLICT clause

From: Stephan Schmidt <schmidt(at)dltmail(dot)de>
To: "pgsql-performance(at)postgresql(dot)org" <pgsql-performance(at)postgresql(dot)org>
Subject: endless quere when upsert with ON CONFLICT clause
Date: 2019-03-29 14:29:02
Message-ID: SN6PR08MB4318D24D6410DE834B24472DE85A0@SN6PR08MB4318.namprd08.prod.outlook.com
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-performance

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?

Responses

Browse pgsql-performance by date

  From Date Subject
Next Message Merlin Moncure 2019-03-29 15:28:20 Re: LIMIT OFFSET with DB view vs plain SQL
Previous Message Laurenz Albe 2019-03-29 13:53:10 Re: LIMIT OFFSET with DB view vs plain SQL