Performance degradation with CTEs, switching from PG 11 to PG 15

[Date Prev][Date Next][Thread Prev][Thread Next][Date Index][Thread Index]

 



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






[Postgresql General]     [Postgresql PHP]     [PHP Users]     [PHP Home]     [PHP on Windows]     [Kernel Newbies]     [PHP Classes]     [PHP Books]     [PHP Databases]     [Yosemite]

  Powered by Linux