Performance degradation with CTEs, switching from PG 11 to PG 15
| От | Jean-Christophe Boggio |
|---|---|
| Тема | Performance degradation with CTEs, switching from PG 11 to PG 15 |
| Дата | |
| Msg-id | 6245f4b6-e608-4d87-9cd8-d0a7a1b5f9d4@thefreecat.org обсуждение исходный текст |
| Ответы |
Re: Performance degradation with CTEs, switching from PG 11 to PG 15
(John Naylor <johncnaylorls@gmail.com>)
Re: Performance degradation with CTEs, switching from PG 11 to PG 15 (Andreas Kretschmer <andreas@a-kretschmer.de>) |
| Список | pgsql-performance |
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
В списке pgsql-performance по дате отправления:
Предыдущее
От: Frits HooglandДата:
Сообщение: Re: simple query running long time within a long transaction.
Следующее
От: John NaylorДата:
Сообщение: Re: Performance degradation with CTEs, switching from PG 11 to PG 15