Re: endless quere when upsert with ON CONFLICT clause

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