Обсуждение: endless quere when upsert with ON CONFLICT clause
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?
Am 29.03.19 um 15:29 schrieb Stephan Schmidt: > > 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? > can you show us the explain (analyse) - plan? i have tried to reproduce, but it seems okay for me. test=*# create table bla (i int primary key, t text); CREATE TABLE test=*# insert into bla select s, 'name ' || s::text from generate_series(1, 100000) s; INSERT 0 100000 test=*# commit; COMMIT test=*# explain analyse with foo as (select x.* as i from generate_series(1, 1000) x) insert into bla select * from foo on conflict (i) do update set t=excluded.i::text; QUERY PLAN ------------------------------------------------------------------------------------------------------------------------------- Insert on bla (cost=10.00..30.00 rows=1000 width=36) (actual time=16.789..16.789 rows=0 loops=1) Conflict Resolution: UPDATE Conflict Arbiter Indexes: bla_pkey Tuples Inserted: 0 Conflicting Tuples: 1000 CTE foo -> Function Scan on generate_series x (cost=0.00..10.00 rows=1000 width=4) (actual time=0.214..0.443 rows=1000 loops=1) -> CTE Scan on foo (cost=0.00..20.00 rows=1000 width=36) (actual time=0.220..1.124 rows=1000 loops=1) Planning Time: 0.104 ms Execution Time: 16.860 ms (10 rows) test=*# explain analyse with foo as (select x.* + 10000000 as i from generate_series(1, 1000) x) insert into bla select * from foo on conflict (i) do update set t=excluded.i::text; QUERY PLAN ------------------------------------------------------------------------------------------------------------------------------- Insert on bla (cost=12.50..32.50 rows=1000 width=36) (actual time=13.424..13.424 rows=0 loops=1) Conflict Resolution: UPDATE Conflict Arbiter Indexes: bla_pkey Tuples Inserted: 1000 Conflicting Tuples: 0 CTE foo -> Function Scan on generate_series x (cost=0.00..12.50 rows=1000 width=4) (actual time=0.079..0.468 rows=1000 loops=1) -> CTE Scan on foo (cost=0.00..20.00 rows=1000 width=36) (actual time=0.081..1.325 rows=1000 loops=1) Planning Time: 0.052 ms Execution Time: 13.471 ms (10 rows) test=*# as you can see, no big difference between the 2 plans. Regards, Andreas -- 2ndQuadrant - The PostgreSQL Support Company. www.2ndQuadrant.com