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 по дате отправления:
Следующее
От: Jinho JungДата:
Сообщение: Need advice: Parallel query execution introduces performance regression