Rozdílný exekuční plán INSERT SELECT vs. SELECT

[petrs...@gmail.com]

Ahojte,

v pátek jsem narazil na něco, co mě překvapilo a potřeboval bych radu od zkušenějších. Máme takový lehce problematický kód, který občas výkonnostně zlobí. Vždycky jsem optimalizoval a od určité chvíle jsem už nevěděl, co a kde optimalizovat. 

No a v pátek mi to už nedalo a tak jsem se do toho ponořil a zjistil jsem, že problém je v tom, že samotný SELECT, který jsem si debugoval trvá sice 60 ms, ale když před ten SELECT hodím INSERT INTO, tak je tam úplně jiný exekuční plán a dotaz pak trvá 9.5 minuty. Výsledkem SELECTu je cca 4000 řádků, takže se nejedná o nijak velké množství.

Původně jsem myslel, že je problém teda v cílové tabulce (indexy apod.). Ale opravdu je tam úplně jiný exekuční plán a výsledkem je, že nám to pak dobíhá výrazně později.

Tady posílám oba dva exekuční plány:

• pouze SELECT: https://explain.dalibo.com/plan/d783640cac7ee837#
• INSERT se SELECTem: https://explain.dalibo.com/plan/22370ggc6008e44e#plan

Tady dotaz (při pomalém dotazu ještě včetně INSERT INTO.... Původně je generovaný dynamicky, ale jiný exekuční plán se vybere, i když to pustím jako statickou query:

select
  now(),
  666,
  now(),
  666,
  false,
  null :: timestamp with time zone,
  null :: bigint,
  a2.advisor_number,
  nlq.lead_category_id,
  nlq.notification_event_type_id,
  l.lead_type_id,
  null :: boolean,
  l.id,
  null :: timestamp with time zone,
  null :: timestamp with time zone,
  fm.id
from
  gpk.filter_market_notification fmn
  inner join sec_dwh.advisor a2 on fmn.ouid = a2.ouid
  and a2.delete_flag = false
  inner join gpk.filter_market fm on fmn.filter_market_id = fm.id
  and fm.delete_flag = false
  inner join public.notification_lead_queue nlq on nlq.lead_category_id = fm.category_id
  inner join notification.v_lead_info_filter l on l.id = nlq.lead_idx
  and l.lead_category_id = 6
  inner join gpk.campaign_group cg on l.campaign_id = cg.campaign_id
  and cg.delete_flag = false
  inner join gpk.advisor_group ag on cg.group_id = ag.group_id
  and ag.delete_flag = false
  and ag.advisor_id = a2.id
  left join gpk.group_blacklist gb on ag.group_id = gb.group_id
  and gb.advisor_id = a2.id
  and gb.delete_flag = false
  left join gpk.hidden_lead hl on l.id = hl.lead_id
  and hl.delete_flag = false
  and hl.ouid = fmn.ouid
where
  l.status_id = 1
  and gb.id is null
  and hl.id is null
  and fm.id = 1
  and fmn.notification
  and fmn.ouid = 12030000002864
  and fmn.delete_flag = false
  and (
    (true)
  );

Dokážete mi prosím někdo poradit v čem by mohl být problém. Případně jak to opravit? Zatím máme jen workaround, který vytvoří temp tabulku, naleje data a zase je dropne. To se děje několikrát. Ale v případě, že ten SELECT je použitý s CREATE TEMP TABLE, tak se vybere ten rychlý exekuční plán.

Díky moc předem!

[Pavel Stehule]

Ahoj

po 1. 9. 2025 v 12:42 odesílatel petrs...@gmail.com <petrs...@gmail.com> napsal:

jak konkretne vypada ten dotaz INSERT INTO?

muzes, pls, pro explain pouzit explain.depesz.com - ja ty obrazky neumim cist.

 

Díky moc předem!

--
Tuto zprávu jste obdrželi, protože jste přihlášeni k odběru skupiny „PostgreSQL-cz“ ve Skupinách Google.
Chcete-li zrušit odběr skupiny a přestat dostávat e‑maily ze skupiny, zašlete e-mail na adresu postgresql-c...@googlegroups.com.
Tuto diskuzi najdete na adrese https://groups.google.com/d/msgid/postgresql-cz/e2939b10-2618-41c4-954e-29de6c0da551n%40googlegroups.com.

[Tomas Vondra]

On 9/1/25 12:42, petrs...@gmail.com wrote:
> Ahojte,
>
> v pátek jsem narazil na něco, co mě překvapilo a potřeboval bych radu od
> zkušenějších. Máme takový lehce problematický kód, který občas
> výkonnostně zlobí. Vždycky jsem optimalizoval a od určité chvíle jsem už
> nevěděl, co a kde optimalizovat.
>
> No a v pátek mi to už nedalo a tak jsem se do toho ponořil a zjistil
> jsem, že problém je v tom, že samotný SELECT, který jsem si debugoval
> trvá sice 60 ms, ale když před ten SELECT hodím INSERT INTO, tak je tam
> úplně jiný exekuční plán a dotaz pak trvá 9.5 minuty. Výsledkem SELECTu
> je cca 4000 řádků, takže se nejedná o nijak velké množství.
>
> Původně jsem myslel, že je problém teda v cílové tabulce (indexy apod.).
> Ale opravdu je tam úplně jiný exekuční plán a výsledkem je, že nám to
> pak dobíhá výrazně později.
>
> Tady posílám oba dva exekuční plány:
>

> * pouze SELECT: https://explain.dalibo.com/plan/d783640cac7ee837#
> * INSERT se SELECTem: https://explain.dalibo.com/
> plan/22370ggc6008e44e#plan

Na 99% je to kombinace dvou faktorů

1) odhady jsou hodně nepřesné (v některých operacích o několik řádů)

2) INSERT nepoužívá paralelismus (není parallel-safe)

S nepřesnými odhady je plánování taková ruská ruleta, to může skončit
jakkoliv. S parallel query (pro čistý SELECT) se nejspíš vybere plán
který funguje celkem dobře, ale to je trochu náhoda. Ten INSERT
zablokuje parallel query, vybere se jiný plán, o trochu dražší, a ten
holt nefunguje tak dobře.

Jako experiment si můžete zkusit tohle (jednotlivě nebo dohromady):

1) vypnout paralelismus

SET max_parallel_workers_per_gather = 0;

2) vypnout nested loop joins

SET enable_nestloop = 0;

a uvidíte jestli ten SELECT bude stejně pomalý jako ten INSERT.


Jak to zrychlit ... těžká rada. Ideální by bylo zkusit zpřesnit ty
odhady, nějak. Ale ten dotaz není extra složitý, resp. ty podmínky
nejsou nějak komplikované.

Z toho co vidím je to spíš otázka toho že jsou tam složené podmínky. Ten
nejhorší odhad je pro join na podmínce

(ag.group_id = gb.group_id) AND (a2.id = gb.advisor_id)

a to je problém pokud je to nějak korelované. DB to prostě odhadne pro
každou podmínku, a pak to vynásobí. Ale pokud je tam korelace tak to
může být podhodnocené - a pak je plán špatný.

Jako experiment bych zkusil rozdělit ten dotaz na dvě části:

1) materialized view / temporary tabulku pro tuhle část dotazu (až po
ten hashjoin)

2) posbírat statistiky

3) upravený dostaz s částí nahrazenou tím matview / temp tabulkou z (1)

A uvidíte.

T.

[petrs...@gmail.com]

Takto vypadá celý INSERT INTO.:

insert into notification.tmp_nq_stage(
  created, created_by, updated, updated_by,
  delete_flag, deleted, deleted_by,
  advisor_id, lead_category_id, notification_event_type_id,
  lead_type_id, template_code, lead_id,
  message, notification_sent, filter_market_id
)

select
  now(),
  666,
  now(),
  666,
  false,

  null,
  null,
  a2.advisor_number,
  nlq.lead_category_id,
  nlq.notification_event_type_id,
  l.lead_type_id,
  null,
  l.id,
  null,
  null,

Tady pouze SELECT, ta rychlá varianta: https://explain.depesz.com/s/hoK2

Tady INSERT SELECT: https://explain.depesz.com/s/DkpO

Dne pondělí 1. září 2025 v 14:36:13 UTC+2 uživatel pavel....@gmail.com napsal:

[Pavel Stehule]

po 1. 9. 2025 v 14:57 odesílatel petrs...@gmail.com <petrs...@gmail.com> napsal:

jsou ty dotazy stejne - rychla varianta vraci 4K radku, pomala insertuje 0 radku?

 

Tuto diskuzi najdete na adrese https://groups.google.com/d/msgid/postgresql-cz/9c87a92d-9772-46c9-867e-7df3f324b711n%40googlegroups.com.

[petrs...@gmail.com]

jj, presne tak. SELECT vrati 4k radku, INSERT insertuje 4k radku.

Dne pondělí 1. září 2025 v 15:09:01 UTC+2 uživatel pavel....@gmail.com napsal:

[petrs...@gmail.com]

Díky! Tohle dává smysl. Vyzkouším

Dne pondělí 1. září 2025 v 14:55:37 UTC+2 uživatel Tomáš Vondra napsal:

[Pavel Stehule]

po 1. 9. 2025 v 15:08 odesílatel Pavel Stehule <pavel....@gmail.com> napsal:

Krome toho, co rikal Tomas - jsou tam hodne spatne odhady, tak ten dotaz ma akorat velikost, kdy by se mohl optimalizovat GQ nebo by tam mohlo dojit k redukci kvuli nizke join_collapse_limit?

Takze bych cvicne zkusil - SET GEQO TO OFF; SET JOIN_COLLAPSE_LIMIT TO 20; SET FROM_COLLAPSE_LIMIT TO 20;

[petrs...@gmail.com]

Tak tohle tomu vyrazne pomohlo:  SET GEQO TO OFF; SET JOIN_COLLAPSE_LIMIT TO 20; SET FROM_COLLAPSE_LIMIT TO 20;

Najednou jsme na 600 ms.

[Pavel Stehule]

po 1. 9. 2025 v 15:12 odesílatel petrs...@gmail.com <petrs...@gmail.com> napsal:

jj, presne tak. SELECT vrati 4k radku, INSERT insertuje 4k radku.

jsem zmateny, teprve dneska jsem si vsiml, ze EXPLAIN ANALYZE v INSERT node zobrazuje 0

╞══════════════════════════════════════════════════════════════════════════════════════════════════════════════════════╡
│ Insert on foo  (cost=0.00..0.10 rows=10 width=4) (actual time=0.160..0.161 rows=0 loops=1)                           │
│   ->  Function Scan on generate_series  (cost=0.00..0.10 rows=10 width=4) (actual time=0.029..0.032 rows=10 loops=1) │
│ Planning Time: 0.067 ms                               

to bych skoro povazoval za chybu

 

Tuto diskuzi najdete na adrese https://groups.google.com/d/msgid/postgresql-cz/45640711-3154-47af-a312-25bbdb1159ecn%40googlegroups.com.

[petrs...@gmail.com]

Díky moc oběma! 

Už je mi to jasný, dává to smysl a pohraju si už jen s těma variantama a kouknu na to blíž.

Dne pondělí 1. září 2025 v 15:17:33 UTC+2 uživatel pavel....@gmail.com napsal:

[Pavel Stehule]

po 1. 9. 2025 v 15:17 odesílatel petrs...@gmail.com <petrs...@gmail.com> napsal:

Tak tohle tomu vyrazne pomohlo:  SET GEQO TO OFF; SET JOIN_COLLAPSE_LIMIT TO 20; SET FROM_COLLAPSE_LIMIT TO 20; 

Najednou jsme na 600 ms.

ten rychly plan jede na 3 cpu, tak to uz by mohlo davat smysl.

 

--
Tuto zprávu jste obdrželi, protože jste přihlášeni k odběru skupiny „PostgreSQL-cz“ ve Skupinách Google.
Chcete-li zrušit odběr skupiny a přestat dostávat e‑maily ze skupiny, zašlete e-mail na adresu postgresql-c...@googlegroups.com.

Tuto diskuzi najdete na adrese https://groups.google.com/d/msgid/postgresql-cz/2dc84427-8c92-464b-8ba4-441cc178c72fn%40googlegroups.com.