Re: endless quere when upsert with ON CONFLICT clause

Поиск
Список
Период
Сортировка
От Andreas Kretschmer
Тема Re: endless quere when upsert with ON CONFLICT clause
Дата
Msg-id 181ab4c1-f2d1-3e58-abbe-e8e8dd5dc3a0@a-kretschmer.de
обсуждение исходный текст
Ответ на endless quere when upsert with ON CONFLICT clause  (Stephan Schmidt <schmidt@dltmail.de>)
Список pgsql-performance

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




В списке pgsql-performance по дате отправления:

Предыдущее
От: Stephan Schmidt
Дата:
Сообщение: endless quere when upsert with ON CONFLICT clause
Следующее
От: Jinho Jung
Дата:
Сообщение: Need advice: Parallel query execution introduces performance regression