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
