Thread: Performance degradation with CTEs, switching from PG 11 to PG 15
Hello,
I just switched from PG11 to PG15 on our production server (Version is
15.5). Just made a vacuum full analyze on the DB.
I have a relatively simple query that used to be fast and is now taking
very long (from less than 10 seconds to 3mn+)
If I remove a WHERE condition changes the calculation time dramatically.
The result is not exactly the same but that extra filtering seems very
long...
Also, adding "materialized" to both "withcwrack" and "withcwrack0" CTEs
gets the result in acceptable timings (a few seconds). The problem with
this is that we have some clients with older versions of PG and I guess
blindly adding the "materialized" keyword will cause errors.
Is there anything I can do to prevent that kind of behaviour ? I'm a
little afraid to have to review all the queries in my softwares to keep
good performances with PG 15 ? Maybe there's a way to configure the
server so that CTEs are materialized by default ? Not ideal but I could
slowly refine queries to enforce "not materialized" and benefit from the
improvement without affecting all our users.
Thanks for your inputs.
JC
Here is the query:
explain (analyze,buffers)
WITH myselect AS (
SELECT DISTINCT og.idoeu
FROM oegroupes og
WHERE (og.idgroupe = 4470)
)
, withcwrack0 AS (
SELECT idoeu, idthirdparty, ackcode
FROM (
SELECT imd.idoeu,
imd.idthirdparty,
imd.ackcode,
RANK() OVER (PARTITION BY imd.idoeu,
imd.idthirdparty ORDER BY imd.idimport DESC) AS rang
FROM importdetails imd
WHERE imd.ackcode NOT IN ('RA', '')
) x
WHERE x.rang = 1
)
, withcwrack AS (
SELECT idoeu,
STRING_AGG(DISTINCT tp.nom, ', ' ORDER BY
tp.nom) FILTER (WHERE ackcode IN ('AS', 'AC', 'NP', 'DU')) AS cwrackok,
STRING_AGG(DISTINCT tp.nom, ', ' ORDER BY
tp.nom) FILTER (WHERE ackcode IN ('CO', 'RJ', 'RC')) AS cwracknotok
FROM withcwrack0
JOIN thirdparty tp USING (idthirdparty)
GROUP BY idoeu
)
SELECT DISTINCT og.idoegroupe,
og.idoeu,
o.titrelong,
o.created,
o.datedepotsacem,
s.nom AS companyname,
na.aggname AS actorsnames,
COALESCE(TRIM(o.repnom1), '') || COALESCE(' / ' ||
TRIM(o.repnom2), '') ||
COALESCE(' / ' || TRIM(o.repnom3), '') AS
actorsnamesinfosrepart,
o.cocv AS favcode,
o.contrattiredufilm,
o.interprete,
o.codecocv,
o.idsociete,
o.idimport,
o.donotexport,
o.observations,
withcwrack.cwracknotok AS cwracknotok,
withcwrack.cwrackok AS cwrackok,
oghl.idgroupe IS NOT NULL AS list_highlight1
FROM oegroupes og
JOIN myselect ON myselect.idoeu = og.idoeu
JOIN oeu o ON o.idoeu = og.idoeu
LEFT JOIN societes s ON s.idsociete = o.idsociete
LEFT JOIN nomsad na ON na.idoeu = o.idoeu
LEFT JOIN withcwrack ON withcwrack.idoeu = o.idoeu
LEFT JOIN oegroupes oghl ON o.idoeu = oghl.idoeu AND oghl.idgroupe = NULL
-- Commenting out the following line makes the query fast :
WHERE (og.idgroupe=4470)
Fast version (without the final where) :
Unique (cost=8888.76..8906.76 rows=360 width=273) (actual
time=343.424..345.687 rows=3004 loops=1)
Buffers: shared hit=26366
-> Sort (cost=8888.76..8889.66 rows=360 width=273) (actual
time=343.422..343.742 rows=3004 loops=1)
Sort Key: og.idoegroupe, og.idoeu, o.titrelong, o.created,
o.datedepotsacem, s.nom, na.aggname, (((COALESCE(TRIM(BOTH FROM
o.repnom1), ''::text) || COALESCE((' / '::text || TRIM(BOTH FROM
o.repnom2)), ''::text)) || COALESCE((' / '::text || TRIM(BOTH FROM
o.repnom3)), ''::text))), o.cocv, o.contrattiredufilm, o.interprete,
(codecocv(o.*)), o.idsociete, o.idimport, o.donotexport, o.observations,
(string_agg(DISTINCT (tp.nom)::text, ', '::text ORDER BY (tp.nom)::text)
FILTER (WHERE ((x.ackcode)::text = ANY ('{CO,RJ,RC}'::text[])))),
(string_agg(DISTINCT (tp.nom)::text, ', '::text ORDER BY (tp.nom)::text)
FILTER (WHERE ((x.ackcode)::text = ANY ('{AS,AC,NP,DU}'::text[])))),
((idgroupe IS NOT NULL))
Sort Method: quicksort Memory: 524kB
Buffers: shared hit=26366
-> Nested Loop Left Join (cost=6811.39..8873.48 rows=360
width=273) (actual time=291.636..340.755 rows=3004 loops=1)
Join Filter: false
Buffers: shared hit=26355
-> Nested Loop (cost=6811.39..8773.58 rows=360
width=2964) (actual time=290.747..301.506 rows=3004 loops=1)
Join Filter: (og_1.idoeu = og.idoeu)
Buffers: shared hit=14173
-> Hash Left Join (cost=6810.97..8718.89 rows=75
width=2964) (actual time=290.726..293.678 rows=1453 loops=1)
Hash Cond: (o.idsociete = s.idsociete)
Buffers: shared hit=6810
-> Hash Right Join (cost=6809.36..8717.06
rows=75 width=2953) (actual time=290.689..292.781 rows=1453 loops=1)
Hash Cond: (na.idoeu = o.idoeu)
Buffers: shared hit=6809
-> Seq Scan on nomsad na
(cost=0.00..1592.24 rows=83924 width=41) (actual time=0.011..9.667
rows=83924 loops=1)
Buffers: shared hit=753
-> Hash (cost=6808.42..6808.42
rows=75 width=2916) (actual time=263.634..263.641 rows=1453 loops=1)
Buckets: 2048 (originally 1024)
Batches: 1 (originally 1) Memory Usage: 515kB
Buffers: shared hit=6056
-> Merge Left Join
(cost=5108.25..6808.42 rows=75 width=2916) (actual time=256.175..262.913
rows=1453 loops=1)
Merge Cond: (o.idoeu = x.idoeu)
Buffers: shared hit=6056
-> Nested Loop
(cost=268.28..852.37 rows=75 width=2852) (actual time=0.995..7.211
rows=1453 loops=1)
Buffers: shared hit=4375
-> Unique
(cost=267.99..268.37 rows=75 width=4) (actual time=0.962..1.693
rows=1453 loops=1)
Buffers: shared
hit=16
-> Sort
(cost=267.99..268.18 rows=75 width=4) (actual time=0.959..1.132
rows=1453 loops=1)
Sort Key:
og_1.idoeu
Sort
Method: quicksort Memory: 49kB
Buffers: shared hit=16
-> Bitmap
Heap Scan on oegroupes og_1 (cost=5.00..265.66 rows=75 width=4) (actual
time=0.183..0.684 rows=1453 loops=1)
Recheck Cond: (idgroupe = 4470)
Heap Blocks: exact=10
Buffers: shared hit=16
-> Bitmap Index Scan on ix_oegroupes_idgr_idoeu_unique2
(cost=0.00..4.99 rows=75 width=0) (actual time=0.156..0.156 rows=1453
loops=1)
Index Cond: (idgroupe = 4470)
Buffers: shared hit=6
-> Index Scan using
oeu_pkey on oeu o (cost=0.29..7.78 rows=1 width=2848) (actual
time=0.003..0.003 rows=1 loops=1453)
Index Cond:
(idoeu = og_1.idoeu)
Buffers: shared
hit=4359
-> GroupAggregate
(cost=4839.96..5953.90 rows=157 width=68) (actual time=52.418..251.636
rows=27905 loops=1)
Group Key: x.idoeu
Buffers: shared hit=1681
-> Nested Loop
(cost=4839.96..5948.97 rows=158 width=24) (actual time=52.369..136.128
rows=28325 loops=1)
Buffers: shared
hit=1681
-> Subquery
Scan on x (cost=4839.81..5943.32 rows=158 width=10) (actual
time=52.341..108.978 rows=28325 loops=1)
Filter: (x.rang = 1)
Buffers: shared hit=1669
->
WindowAgg (cost=4839.81..5549.21 rows=31529 width=22) (actual
time=52.340..101.941 rows=28325 loops=1)
Run Condition: (rank() OVER (?) <= 1)
Buffers: shared hit=1669
-> Sort (cost=4839.81..4918.63 rows=31529 width=14) (actual
time=52.321..56.410 rows=31526 loops=1)
Sort Key: imd.idoeu, imd.idthirdparty, imd.idimport DESC
Sort Method: quicksort Memory: 2493kB
Buffers: shared hit=1669
-> Seq Scan on importdetails imd (cost=0.00..2483.90 rows=31529
width=14) (actual time=0.028..34.438 rows=31526 loops=1)
" Filter: ((ackcode)::text <> ALL ('{RA,""""}'::text[]))"
Rows Removed by Filter: 33666
Buffers: shared hit=1669
-> Memoize
(cost=0.15..0.30 rows=1 width=22) (actual time=0.000..0.000 rows=1
loops=28325)
Cache
Key: x.idthirdparty
Cache
Mode: logical
Hits:
28319 Misses: 6 Evictions: 0 Overflows: 0 Memory Usage: 1kB
Buffers: shared hit=12
-> Index
Scan using providers_pkey on thirdparty tp (cost=0.14..0.29 rows=1
width=22) (actual time=0.009..0.009 rows=1 loops=6)
Index Cond: (idthirdparty = x.idthirdparty)
Buffers: shared hit=12
-> Hash (cost=1.27..1.27 rows=27 width=15)
(actual time=0.024..0.025 rows=27 loops=1)
Buckets: 1024 Batches: 1 Memory
Usage: 10kB
Buffers: shared hit=1
-> Seq Scan on societes s
(cost=0.00..1.27 rows=27 width=15) (actual time=0.009..0.014 rows=27
loops=1)
Buffers: shared hit=1
-> Index Scan using ix_oegroupes_idoeu on
oegroupes og (cost=0.42..0.67 rows=5 width=8) (actual time=0.002..0.003
rows=2 loops=1453)
Index Cond: (idoeu = o.idoeu)
Buffers: shared hit=7363
-> Result (cost=0.00..0.00 rows=0 width=4) (actual
time=0.000..0.000 rows=0 loops=3004)
One-Time Filter: false
Planning:
Buffers: shared hit=40
Planning Time: 3.240 ms
Execution Time: 346.193 ms
Slow version :
Unique (cost=8408.54..8408.59 rows=1 width=273) (actual
time=220347.876..220348.736 rows=1453 loops=1)
Buffers: shared hit=15544
-> Sort (cost=8408.54..8408.54 rows=1 width=273) (actual
time=220347.875..220347.998 rows=1453 loops=1)
Sort Key: og.idoegroupe, og.idoeu, o.titrelong, o.created,
o.datedepotsacem, s.nom, na.aggname, (((COALESCE(TRIM(BOTH FROM
o.repnom1), ''::text) || COALESCE((' / '::text || TRIM(BOTH FROM
o.repnom2)), ''::text)) || COALESCE((' / '::text || TRIM(BOTH FROM
o.repnom3)), ''::text))), o.cocv, o.contrattiredufilm, o.interprete,
(codecocv(o.*)), o.idsociete, o.idimport, o.donotexport, o.observations,
(string_agg(DISTINCT (tp.nom)::text, ', '::text ORDER BY (tp.nom)::text)
FILTER (WHERE ((x.ackcode)::text = ANY ('{CO,RJ,RC}'::text[])))),
(string_agg(DISTINCT (tp.nom)::text, ', '::text ORDER BY (tp.nom)::text)
FILTER (WHERE ((x.ackcode)::text = ANY ('{AS,AC,NP,DU}'::text[])))),
((idgroupe IS NOT NULL))
Sort Method: quicksort Memory: 255kB
Buffers: shared hit=15544
-> Nested Loop Left Join (cost=5383.80..8408.53 rows=1
width=273) (actual time=288.376..220345.536 rows=1453 loops=1)
Join Filter: false
Buffers: shared hit=15544
-> Nested Loop Left Join (cost=5383.80..8408.25 rows=1
width=2964) (actual time=287.986..220284.827 rows=1453 loops=1)
Join Filter: (x.idoeu = o.idoeu)
Rows Removed by Join Filter: 40545965
Buffers: shared hit=9731
-> Nested Loop Left Join (cost=543.83..2450.82
rows=1 width=2904) (actual time=56.081..68.044 rows=1453 loops=1)
Buffers: shared hit=8050
-> Hash Right Join (cost=543.70..2450.66
rows=1 width=2893) (actual time=56.066..61.414 rows=1453 loops=1)
Hash Cond: (na.idoeu = o.idoeu)
Buffers: shared hit=5144
-> Seq Scan on nomsad na
(cost=0.00..1592.24 rows=83924 width=41) (actual time=0.013..15.785
rows=83924 loops=1)
Buffers: shared hit=753
-> Hash (cost=543.68..543.68 rows=1
width=2856) (actual time=15.342..15.347 rows=1453 loops=1)
Buckets: 2048 (originally 1024)
Batches: 1 (originally 1) Memory Usage: 521kB
Buffers: shared hit=4391
-> Nested Loop
(cost=275.35..543.68 rows=1 width=2856) (actual time=2.628..13.995
rows=1453 loops=1)
Buffers: shared hit=4391
-> Hash Join
(cost=275.06..535.91 rows=1 width=12) (actual time=2.593..4.334
rows=1453 loops=1)
Hash Cond: (og.idoeu
= og_1.idoeu)
Buffers: shared hit=32
-> Bitmap Heap Scan
on oegroupes og (cost=5.00..265.66 rows=75 width=8) (actual
time=0.181..0.614 rows=1453 loops=1)
Recheck Cond:
(idgroupe = 4470)
Heap Blocks:
exact=10
Buffers: shared
hit=16
-> Bitmap Index
Scan on ix_oegroupes_idgr_idoeu_unique2 (cost=0.00..4.99 rows=75
width=0) (actual time=0.158..0.158 rows=1453 loops=1)
Index
Cond: (idgroupe = 4470)
Buffers: shared hit=6
-> Hash
(cost=269.12..269.12 rows=75 width=4) (actual time=2.394..2.396
rows=1453 loops=1)
Buckets: 2048
(originally 1024) Batches: 1 (originally 1) Memory Usage: 68kB
Buffers: shared
hit=16
-> Unique
(cost=267.99..268.37 rows=75 width=4) (actual time=0.894..1.942
rows=1453 loops=1)
Buffers: shared hit=16
-> Sort
(cost=267.99..268.18 rows=75 width=4) (actual time=0.891..1.151
rows=1453 loops=1)
Sort Key: og_1.idoeu
Sort Method: quicksort Memory: 49kB
Buffers: shared hit=16
-> Bitmap Heap Scan on oegroupes og_1 (cost=5.00..265.66 rows=75
width=4) (actual time=0.139..0.658 rows=1453 loops=1)
Recheck Cond: (idgroupe = 4470)
Heap Blocks: exact=10
Buffers: shared hit=16
-> Bitmap Index Scan on ix_oegroupes_idgr_idoeu_unique2
(cost=0.00..4.99 rows=75 width=0) (actual time=0.121..0.122 rows=1453
loops=1)
Index Cond: (idgroupe = 4470)
Buffers: shared hit=6
-> Index Scan using
oeu_pkey on oeu o (cost=0.29..7.78 rows=1 width=2848) (actual
time=0.005..0.005 rows=1 loops=1453)
Index Cond: (idoeu =
og_1.idoeu)
Buffers: shared hit=4359
-> Index Scan using societes_pkey on
societes s (cost=0.14..0.16 rows=1 width=15) (actual time=0.003..0.003
rows=1 loops=1453)
Index Cond: (idsociete = o.idsociete)
Buffers: shared hit=2906
-> GroupAggregate (cost=4839.96..5953.90 rows=157
width=68) (actual time=0.034..148.224 rows=27905 loops=1453)
Group Key: x.idoeu
Buffers: shared hit=1681
-> Nested Loop (cost=4839.96..5948.97
rows=158 width=24) (actual time=0.026..61.006 rows=28325 loops=1453)
Buffers: shared hit=1681
-> Subquery Scan on x
(cost=4839.81..5943.32 rows=158 width=10) (actual time=0.025..40.825
rows=28325 loops=1453)
Filter: (x.rang = 1)
Buffers: shared hit=1669
-> WindowAgg
(cost=4839.81..5549.21 rows=31529 width=22) (actual time=0.025..35.958
rows=28325 loops=1453)
Run Condition: (rank() OVER
(?) <= 1)
Buffers: shared hit=1669
-> Sort
(cost=4839.81..4918.63 rows=31529 width=14) (actual time=0.023..3.132
rows=31526 loops=1453)
Sort Key: imd.idoeu,
imd.idthirdparty, imd.idimport DESC
Sort Method:
quicksort Memory: 2493kB
Buffers: shared hit=1669
-> Seq Scan on
importdetails imd (cost=0.00..2483.90 rows=31529 width=14) (actual
time=0.021..22.590 rows=31526 loops=1)
" Filter:
((ackcode)::text <> ALL ('{RA,""""}'::text[]))"
Rows Removed by
Filter: 33666
Buffers: shared
hit=1669
-> Memoize (cost=0.15..0.30 rows=1
width=22) (actual time=0.000..0.000 rows=1 loops=41156225)
Cache Key: x.idthirdparty
Cache Mode: logical
Hits: 41156219 Misses: 6
Evictions: 0 Overflows: 0 Memory Usage: 1kB
Buffers: shared hit=12
-> Index Scan using
providers_pkey on thirdparty tp (cost=0.14..0.29 rows=1 width=22)
(actual time=0.006..0.006 rows=1 loops=6)
Index Cond: (idthirdparty =
x.idthirdparty)
Buffers: shared hit=12
-> Result (cost=0.00..0.00 rows=0 width=4) (actual
time=0.000..0.000 rows=0 loops=1453)
One-Time Filter: false
Planning:
Buffers: shared hit=40
Planning Time: 3.302 ms
Execution Time: 220349.106 ms
With materialized :
Unique (cost=8428.96..8429.01 rows=1 width=273) (actual
time=8422.790..8423.717 rows=1453 loops=1)
Buffers: shared hit=15537
CTE withcwrack0
-> Subquery Scan on x (cost=4839.81..5943.32 rows=158 width=10)
(actual time=33.309..85.155 rows=28325 loops=1)
Filter: (x.rang = 1)
Buffers: shared hit=1669
-> WindowAgg (cost=4839.81..5549.21 rows=31529 width=22)
(actual time=33.307..77.580 rows=28325 loops=1)
Run Condition: (rank() OVER (?) <= 1)
Buffers: shared hit=1669
-> Sort (cost=4839.81..4918.63 rows=31529 width=14)
(actual time=33.291..37.192 rows=31526 loops=1)
Sort Key: imd.idoeu, imd.idthirdparty,
imd.idimport DESC
Sort Method: quicksort Memory: 2493kB
Buffers: shared hit=1669
-> Seq Scan on importdetails imd
(cost=0.00..2483.90 rows=31529 width=14) (actual time=0.024..22.104
rows=31526 loops=1)
" Filter: ((ackcode)::text <> ALL
('{RA,""""}'::text[]))"
Rows Removed by Filter: 33666
Buffers: shared hit=1669
CTE withcwrack
-> GroupAggregate (cost=17.42..22.75 rows=158 width=68) (actual
time=118.918..236.104 rows=27905 loops=1)
Group Key: withcwrack0.idoeu
Buffers: shared hit=1672
-> Sort (cost=17.42..17.81 rows=158 width=80) (actual
time=118.874..122.458 rows=28325 loops=1)
Sort Key: withcwrack0.idoeu
Sort Method: quicksort Memory: 2320kB
Buffers: shared hit=1672
-> Hash Join (cost=8.06..11.65 rows=158 width=80)
(actual time=33.447..110.595 rows=28325 loops=1)
Hash Cond: (withcwrack0.idthirdparty =
tp.idthirdparty)
Buffers: shared hit=1672
-> CTE Scan on withcwrack0 (cost=0.00..3.16
rows=158 width=66) (actual time=33.311..97.238 rows=28325 loops=1)
Buffers: shared hit=1669
-> Hash (cost=5.25..5.25 rows=225 width=22)
(actual time=0.121..0.121 rows=225 loops=1)
Buckets: 1024 Batches: 1 Memory Usage: 21kB
Buffers: shared hit=3
-> Seq Scan on thirdparty tp
(cost=0.00..5.25 rows=225 width=22) (actual time=0.014..0.063 rows=225
loops=1)
Buffers: shared hit=3
-> Sort (cost=2462.88..2462.89 rows=1 width=273) (actual
time=8422.789..8422.925 rows=1453 loops=1)
Sort Key: og.idoegroupe, og.idoeu, o.titrelong, o.created,
o.datedepotsacem, s.nom, na.aggname, (((COALESCE(TRIM(BOTH FROM
o.repnom1), ''::text) || COALESCE((' / '::text || TRIM(BOTH FROM
o.repnom2)), ''::text)) || COALESCE((' / '::text || TRIM(BOTH FROM
o.repnom3)), ''::text))), o.cocv, o.contrattiredufilm, o.interprete,
(codecocv(o.*)), o.idsociete, o.idimport, o.donotexport, o.observations,
withcwrack.cwracknotok, withcwrack.cwrackok, ((idgroupe IS NOT NULL))
Sort Method: quicksort Memory: 255kB
Buffers: shared hit=15537
-> Nested Loop Left Join (cost=550.47..2462.87 rows=1
width=273) (actual time=310.118..8421.261 rows=1453 loops=1)
Join Filter: false
Buffers: shared hit=15537
-> Nested Loop Left Join (cost=550.47..2462.59 rows=1
width=2964) (actual time=309.673..8392.068 rows=1453 loops=1)
Join Filter: (withcwrack.idoeu = o.idoeu)
Rows Removed by Join Filter: 40545965
Buffers: shared hit=9724
-> Nested Loop Left Join (cost=550.47..2457.46
rows=1 width=2904) (actual time=54.495..60.810 rows=1453 loops=1)
Buffers: shared hit=8052
-> Hash Right Join (cost=550.34..2457.30
rows=1 width=2893) (actual time=54.471..57.459 rows=1453 loops=1)
Hash Cond: (na.idoeu = o.idoeu)
Buffers: shared hit=5146
-> Seq Scan on nomsad na
(cost=0.00..1592.24 rows=83924 width=41) (actual time=0.012..14.855
rows=83924 loops=1)
Buffers: shared hit=753
-> Hash (cost=550.32..550.32 rows=1
width=2856) (actual time=14.905..14.909 rows=1453 loops=1)
Buckets: 2048 (originally 1024)
Batches: 1 (originally 1) Memory Usage: 521kB
Buffers: shared hit=4393
-> Nested Loop
(cost=278.71..550.32 rows=1 width=2856) (actual time=2.513..13.598
rows=1453 loops=1)
Buffers: shared hit=4393
-> Hash Join
(cost=278.41..542.54 rows=1 width=12) (actual time=2.483..4.219
rows=1453 loops=1)
Hash Cond: (og.idoeu
= og_1.idoeu)
Buffers: shared hit=34
-> Bitmap Heap Scan
on oegroupes og (cost=5.01..268.94 rows=76 width=8) (actual
time=0.171..0.573 rows=1453 loops=1)
Recheck Cond:
(idgroupe = 4470)
Heap Blocks:
exact=10
Buffers: shared
hit=17
-> Bitmap Index
Scan on ix_oegroupes_idgr_idoeu_unique2 (cost=0.00..4.99 rows=76
width=0) (actual time=0.150..0.150 rows=1453 loops=1)
Index Cond: (idgroupe = 4470)
Buffers: shared hit=7
-> Hash
(cost=272.45..272.45 rows=76 width=4) (actual time=2.303..2.305
rows=1453 loops=1)
Buckets: 2048
(originally 1024) Batches: 1 (originally 1) Memory Usage: 68kB
Buffers: shared
hit=17
-> Unique
(cost=271.31..271.69 rows=76 width=4) (actual time=0.800..1.855
rows=1453 loops=1)
Buffers: shared hit=17
-> Sort (cost=271.31..271.50 rows=76 width=4) (actual
time=0.798..1.069 rows=1453 loops=1)
Sort Key: og_1.idoeu
Sort Method: quicksort Memory: 49kB
Buffers: shared hit=17
-> Bitmap Heap Scan on oegroupes og_1 (cost=5.01..268.94 rows=76
width=4) (actual time=0.128..0.572 rows=1453 loops=1)
Recheck Cond: (idgroupe = 4470)
Heap Blocks: exact=10
Buffers: shared hit=17
-> Bitmap Index Scan on ix_oegroupes_idgr_idoeu_unique2
(cost=0.00..4.99 rows=76 width=0) (actual time=0.113..0.113 rows=1453
loops=1)
Index Cond: (idgroupe = 4470)
Buffers: shared hit=7
-> Index Scan using
oeu_pkey on oeu o (cost=0.29..7.78 rows=1 width=2848) (actual
time=0.005..0.005 rows=1 loops=1453)
Index Cond: (idoeu =
og_1.idoeu)
Buffers: shared hit=4359
-> Index Scan using societes_pkey on
societes s (cost=0.14..0.16 rows=1 width=15) (actual time=0.001..0.001
rows=1 loops=1453)
Index Cond: (idsociete = o.idsociete)
Buffers: shared hit=2906
-> CTE Scan on withcwrack (cost=0.00..3.16
rows=158 width=68) (actual time=0.082..3.122 rows=27905 loops=1453)
Buffers: shared hit=1672
-> Result (cost=0.00..0.00 rows=0 width=4) (actual
time=0.000..0.000 rows=0 loops=1453)
One-Time Filter: false
Planning:
Buffers: shared hit=38
Planning Time: 2.927 ms
Execution Time: 8424.587 ms
On Wed, Nov 22, 2023 at 6:39 PM Jean-Christophe Boggio <postgresql@thefreecat.org> wrote: > > Hello, > > I just switched from PG11 to PG15 on our production server (Version is > 15.5). Just made a vacuum full analyze on the DB. Note that "vacuum full" is not recommended practice in most situations. Among the downsides, it removes the visibility map, which is necessary to allow index-only scans. Plain vacuum should always be used except for certain dire situations. Before proceeding further, please perform a plain vacuum on the DB. After that, check if there are still problems with your queries. > Also, adding "materialized" to both "withcwrack" and "withcwrack0" CTEs > gets the result in acceptable timings (a few seconds). The problem with > this is that we have some clients with older versions of PG and I guess > blindly adding the "materialized" keyword will cause errors. Yes, meaning 11 and earlier don't recognize that keyword keyword. > Is there anything I can do to prevent that kind of behaviour ? I'm a > little afraid to have to review all the queries in my softwares to keep > good performances with PG 15 ? Maybe there's a way to configure the > server so that CTEs are materialized by default ? There is no such a way. It would be surely be useful for some users to have a way to slowly migrate query plans to new planner versions, but that's not how it works today.
Re: Performance degradation with CTEs, switching from PG 11 to PG 15
From
Jean-Christophe Boggio
Date:
John, Le 22/11/2023 à 14:30, John Naylor a écrit : > Note that "vacuum full" is not recommended practice in most > situations. Among the downsides, it removes the visibilitymap, > which is necessary to allow index-only scans. Plain vacuum should > always be used except for certain dire situations. Before proceeding > further, please perform a plain vacuum on the DB. After that, check > if there are still problems with your queries. Did both VACUUM ANALYZE and VACUUM (which one did you recommend exactly?) and things go much faster now, thanks a lot. I will also check why autovacuum did not do its job. >> Is there anything I can do to prevent that kind of behaviour ? I'm >> a little afraid to have to review all the queriesin my softwares >> to keep good performances with PG 15 ? Maybe there's a way to >> configure the server so that CTEs are materialized by default ? > > There is no such a way. It would be surely be useful for some users > to have a way to slowly migrate query plans to new planner versions, > but that's not how it works today. Thanks for your input so I know I did not miss a parameter. And yes, that would be handy. Best regards,
Am 22.11.23 um 12:38 schrieb Jean-Christophe Boggio: > > > Also, adding "materialized" to both "withcwrack" and "withcwrack0" > CTEs gets the result in acceptable timings (a few seconds). The > problem with this is that we have some clients with older versions of > PG and I guess blindly adding the "materialized" keyword will cause > errors. > yeah, prior to 11 CTEs are a optimizer barrier. You can try to rewrite the queries to not using CTEs - or upgrade. If i were you i would upgrade. Regards, Andreas -- Andreas Kretschmer - currently still (garden leave) Technical Account Manager (TAM) www.enterprisedb.com
Re: Performance degradation with CTEs, switching from PG 11 to PG 15
From
Jean-Christophe Boggio
Date:
Andreas, Le 22/11/2023 à 15:25, Andreas Kretschmer a écrit : > Am 22.11.23 um 12:38 schrieb Jean-Christophe Boggio: >> Also, adding "materialized" to both "withcwrack" and "withcwrack0" >> CTEs gets the result in acceptable timings (a few seconds). The >> problem with this is that we have some clients with older versions >> of PG and I guess blindly adding the "materialized" keyword will >> cause errors. > yeah, prior to 11 CTEs are a optimizer barrier. You can try to > rewrite the queries to not using CTEs - or upgrade. If i were you i > would upgrade. I did upgrade :-) But we have many users for which we don't decide on when they do upgrade so we have to keep compatibility with most versions of PG and in that particular case (non-existence of the materialized keyword for PG 11 and before) it is a real problem. Best regards, JC
Jean-Christophe Boggio <postgresql@thefreecat.org> writes:
> I did upgrade :-) But we have many users for which we don't decide on
> when they do upgrade so we have to keep compatibility with most versions
> of PG and in that particular case (non-existence of the materialized
> keyword for PG 11 and before) it is a real problem.
PG 11 is out of support as of earlier this month, so your users really
need to be prioritizing getting onto more modern versions.
regards, tom lane