I don't understand that EXPLAIN PLAN timings

Hello,

This EXPLAIN ANALYZE tells me the actual time was 11.xxx ms but the 
final Execution time says 493.xxx ms (this last one is true : about 1/2 
second).

I would like to optimize this query but with this inconsistency, it will 
be difficult. This query is really a function so I added the "params" 
CTE to do the tests.

Thanks for your help,

JC

PS: PostgreSQL 14.10 (Ubuntu 14.10-1.pgdg23.10+1) on 
x86_64-pc-linux-gnu, compiled by gcc (Ubuntu 13.2.0-4ubuntu3) 13.2.0, 64-bit

and JIT is disabled


Nested Loop Left Join  (cost=36411048684.11..36411051002.22 rows=54228 
width=116) (actual time=11.609..11.620 rows=0 loops=1)
   Join Filter: (u_g_internalvalidation.idinternalvalidation IS NOT NULL)
   CTE params
     ->  Result  (cost=0.00..0.01 rows=1 width=41) (actual 
time=0.001..0.001 rows=1 loops=1)
   CTE u
     ->  Nested Loop  (cost=0.25..12.77 rows=250 width=368) (never executed)
           ->  CTE Scan on params  (cost=0.00..0.02 rows=1 width=41) 
(never executed)
           ->  Function Scan on get_user  (cost=0.25..10.25 rows=250 
width=327) (never executed)
                 Filter: ((NOT isgroup) AND (NOT istemplate))
   CTE internalvalidation
     ->  Hash Join  (cost=13988.95..14336.16 rows=436 width=42) (actual 
time=11.601..11.606 rows=0 loops=1)
           Hash Cond: (files.idfile = i_1.idfile)
           ->  HashAggregate  (cost=13929.87..14039.58 rows=10971 
width=4) (actual time=11.480..11.527 rows=243 loops=1)
                 Group Key: files.idfile
                 Batches: 1  Memory Usage: 417kB
                 ->  Append  (cost=0.00..13902.45 rows=10971 width=4) 
(actual time=6.593..11.423 rows=243 loops=1)
                       ->  Nested Loop  (cost=0.00..10139.01 rows=10766 
width=4) (actual time=5.143..5.145 rows=0 loops=1)
                             Join Filter: ((files.idfile = 
(unnest(params_2.fn_idfile))) OR ((params_1.fn_idrealm = 
files.idrealminitial) AND params_1.fn_canseesubfiles))
                             ->  Seq Scan on files (cost=0.00..2985.74 
rows=40874 width=8) (actual time=0.002..2.396 rows=40766 loops=1)
                             ->  Materialize  (cost=0.00..0.35 rows=10 
width=9) (actual time=0.000..0.000 rows=0 loops=40766)
                                   ->  Nested Loop (cost=0.00..0.30 
rows=10 width=9) (actual time=0.003..0.003 rows=0 loops=1)
                                         ->  CTE Scan on params 
params_1  (cost=0.00..0.02 rows=1 width=5) (actual time=0.002..0.003 
rows=0 loops=1)
                                               Filter: (fn_idfile IS NOT 
NULL)
                                               Rows Removed by Filter: 1
                                         ->  ProjectSet (cost=0.00..0.08 
rows=10 width=4) (never executed)
                                               ->  CTE Scan on params 
params_2  (cost=0.00..0.02 rows=1 width=32) (never executed)
                       ->  Nested Loop  (cost=0.00..3598.87 rows=205 
width=4) (actual time=1.448..6.267 rows=243 loops=1)
                             Join Filter: (((params_3.fn_idfile IS NULL) 
AND (files_1.idrealm = params_3.fn_idrealm)) OR ((files_1.idrealminitial 
= params_3.fn_idrealm) AND params_3.fn_canseesubfiles))
                             Rows Removed by Join Filter: 40523
                             ->  CTE Scan on params params_3 
(cost=0.00..0.02 rows=1 width=37) (actual time=0.001..0.001 rows=1 loops=1)
                                   Filter: ((fn_idfile IS NULL) OR 
fn_canseesubfiles)
                             ->  Seq Scan on files files_1 
(cost=0.00..2985.74 rows=40874 width=12) (actual time=0.002..1.946 
rows=40766 loops=1)
           ->  Hash  (cost=58.94..58.94 rows=11 width=42) (actual 
time=0.059..0.060 rows=6 loops=1)
                 Buckets: 1024  Batches: 1  Memory Usage: 9kB
                 ->  Nested Loop Left Join  (cost=0.29..58.94 rows=11 
width=42) (actual time=0.031..0.055 rows=6 loops=1)
                       ->  Seq Scan on internalvalidations i_1 
(cost=0.00..9.81 rows=11 width=26) (actual time=0.012..0.033 rows=6 loops=1)
                             Filter: isvisible
                             Rows Removed by Filter: 453
                       ->  Memoize  (cost=0.29..7.58 rows=1 width=20) 
(actual time=0.003..0.003 rows=1 loops=6)
                             Cache Key: i_1.idclaimer
                             Cache Mode: logical
                             Hits: 5  Misses: 1  Evictions: 0 Overflows: 
0  Memory Usage: 1kB
                             ->  Index Scan using users_pkey on users 
users_1  (cost=0.28..7.57 rows=1 width=20) (actual time=0.012..0.013 
rows=1 loops=1)
                                   Index Cond: (iduser = i_1.idclaimer)
   CTE u_g_internalvalidation_without_u_status
     ->  Recursive Union  (cost=23.65..7914563.16 rows=220606936 
width=19) (never executed)
           ->  Nested Loop  (cost=23.65..9280.10 rows=1936 width=19) 
(never executed)
                 Join Filter: (uhi.iduser = gu.iduser)
                 ->  Nested Loop  (cost=23.40..80.70 rows=733 width=15) 
(never executed)
                       ->  Hash Join  (cost=23.11..42.43 rows=733 
width=10) (never executed)
                             Hash Cond: 
(internalvalidation.idinternalvalidation = uhi.idinternalvalidation)
                             ->  CTE Scan on internalvalidation 
(cost=0.00..8.72 rows=436 width=4) (never executed)
                             ->  Hash  (cost=13.05..13.05 rows=805 
width=10) (never executed)
                                   ->  Seq Scan on 
users_has_internalvalidations uhi  (cost=0.00..13.05 rows=805 width=10) 
(never executed)
                       ->  Memoize  (cost=0.29..1.67 rows=1 width=5) 
(never executed)
                             Cache Key: uhi.iduser
                             Cache Mode: logical
                             ->  Index Scan using users_pkey on users v  
(cost=0.28..1.66 rows=1 width=5) (never executed)
                                   Index Cond: (iduser = uhi.iduser)
                 ->  Function Scan on get_user gu (cost=0.25..12.75 
rows=4 width=5) (never executed)
                       Filter: ((canvalidate OR v.isgroup) AND (v.iduser 
= iduser))
           ->  Nested Loop  (cost=750.89..349314.43 rows=22060500 
width=19) (never executed)
                 ->  Merge Join  (cost=750.63..1200.96 rows=29414 
width=18) (never executed)
                       Merge Cond: (uhg.idgroup = ugiwus.idusergroup)
                       ->  Sort  (cost=126.96..131.52 rows=1823 width=8) 
(never executed)
                             Sort Key: uhg.idgroup
                             ->  Seq Scan on users_has_groups uhg 
(cost=0.00..28.23 rows=1823 width=8) (never executed)
                       ->  Sort  (cost=623.67..631.74 rows=3227 
width=18) (never executed)
                             Sort Key: ugiwus.idusergroup
                             ->  WorkTable Scan on 
u_g_internalvalidation_without_u_status ugiwus  (cost=0.00..435.60 
rows=3227 width=18) (never executed)
                                   Filter: (isgroup AND (level < 25))
                 ->  Memoize  (cost=0.26..10.26 rows=750 width=5) (never 
executed)
                       Cache Key: uhg.iduser
                       Cache Mode: binary
                       ->  Function Scan on get_user gu_1 
(cost=0.25..10.25 rows=750 width=5) (never executed)
                             Filter: (canvalidate OR isgroup)
   ->  Hash Join  (cost=36403119772.02..36403120996.38 rows=436 
width=120) (actual time=11.609..11.611 rows=0 loops=1)
         Hash Cond: (ug1.idinternalvalidation = i.idinternalvalidation)
         ->  Merge Left Join (cost=36403119757.85..36403120966.35 
rows=200 width=75) (never executed)
               Merge Cond: (ug1.idinternalvalidation = 
u_g_internalvalidation.idinternalvalidation)
               ->  GroupAggregate (cost=36397604572.80..36397605775.80 
rows=200 width=70) (never executed)
                     Group Key: ug1.idinternalvalidation
                     ->  Sort (cost=36397604572.80..36397604772.80 
rows=80000 width=27) (never executed)
                           Sort Key: ug1.idinternalvalidation
                           ->  Hash Left Join 
(cost=36397596247.48..36397598057.72 rows=80000 width=27) (never executed)
                                 Hash Cond: (ug1.idusergroup = users.iduser)
                                 ->  HashAggregate 
(cost=36397595908.85..36397596708.85 rows=80000 width=43) (never executed)
                                       Group Key: 
ug1.idinternalvalidation, ug1.idusergroup, ug1.atleastonemustvalidate
                                       ->  Merge Left Join 
(cost=77330317.34..21265069958.96 rows=1210602075991 width=13) (never 
executed)
                                             Merge Cond: 
((ug1.idinternalvalidation = ug2.idinternalvalidation) AND (ug1.iduser = 
ug2.iduser))
                                             ->  Sort 
(cost=38754984.35..39306501.69 rows=220606936 width=15) (never executed)
                                                   Sort Key: 
ug1.idinternalvalidation, ug1.iduser
                                                   ->  CTE Scan on 
u_g_internalvalidation_without_u_status ug1 (cost=0.00..4412138.72 
rows=220606936 width=15) (never executed)
                                             ->  Materialize 
(cost=38575332.99..39672852.50 rows=219503901 width=10) (never executed)
                                                   ->  Sort 
(cost=38575332.99..39124092.74 rows=219503901 width=10) (never executed)
                                                         Sort Key: 
ug2.idinternalvalidation, ug2.iduser
                                                         ->  CTE Scan on 
u_g_internalvalidation_without_u_status ug2 (cost=0.00..4412138.72 
rows=219503901 width=10) (never executed)
Filter: (hasvalidate IS NOT NULL)
                                 ->  Hash  (cost=289.95..289.95 
rows=3895 width=20) (never executed)
                                       ->  Seq Scan on users 
(cost=0.00..289.95 rows=3895 width=20) (never executed)
               ->  Sort  (cost=5515185.04..5515185.54 rows=200 width=5) 
(never executed)
                     Sort Key: u_g_internalvalidation.idinternalvalidation
                     ->  Subquery Scan on u_g_internalvalidation  
(cost=5515173.40..5515177.40 rows=200 width=5) (never executed)
                           ->  HashAggregate 
(cost=5515173.40..5515175.40 rows=200 width=5) (never executed)
                                 Group Key: ugi.idinternalvalidation
                                 ->  CTE Scan on 
u_g_internalvalidation_without_u_status ugi (cost=0.00..4412138.72 
rows=220606936 width=5) (never executed)
         ->  Hash  (cost=8.72..8.72 rows=436 width=49) (actual 
time=11.603..11.603 rows=0 loops=1)
               Buckets: 1024  Batches: 1  Memory Usage: 8kB
               ->  CTE Scan on internalvalidation i (cost=0.00..8.72 
rows=436 width=49) (actual time=11.603..11.603 rows=0 loops=1)
   ->  CTE Scan on u  (cost=0.00..5.00 rows=125 width=4) (never executed)
         Filter: canvalidate
Planning Time: 1.037 ms
Execution Time: 493.815 ms


And here is the query in case it can help :


explain analyze
WITH RECURSIVE
     params AS (
         SELECT
             4 AS fn_iduser
             , 1 AS fn_idrealm
             , NULL::INT[] AS fn_idfile
             , TRUE AS fn_canseesubfiles
     ),
     r AS (
     SELECT realms.*
     FROM realms
     CROSS JOIN params
     WHERE idrealm=fn_idrealm
)
,u AS (
     SELECT *
     FROM PARAMS
     CROSS JOIN get_user(fn_iduser)
     WHERE NOT isgroup
     AND NOT istemplate
)
,allfiles AS (
     SELECT files.idfile --idfile devrait suffire
     FROM params
     CROSS JOIN (
         SELECT UNNEST(fn_idfile) as idfile FROM params
     ) AS unnestedfiles
     JOIN files ON files.idfile = unnestedfiles.idfile OR (
         -- see sub files
         fn_idrealm = files.idrealminitial
         AND
         fn_canseesubfiles
     )
     WHERE fn_idfile IS NOT NULL

     UNION

     SELECT files.idfile --idfile devrait suffire
     FROM files
     CROSS JOIN params
     WHERE fn_idfile IS NULL
     AND files.idrealm = fn_idrealm OR ( files.idrealminitial = 
fn_idrealm AND fn_canseesubfiles )
)
,internalvalidation as (
     SELECT i.*, users.name
     FROM allfiles pfu
     JOIN internalvalidations i ON i.idfile = pfu.idfile AND i.isvisible
     LEFT JOIN users ON users.iduser = i.idclaimer
)
,u_g_internalvalidation_without_u_status AS (
     SELECT uhi.idinternalvalidation
             ,uhi.iduser as idusergroup
             ,uhi.hasvalidate
             ,uhi.atleastonemustvalidate
             ,v.iduser
             ,v.isgroup
             , 0 level
     FROM internalvalidation
     JOIN users_has_internalvalidations uhi ON uhi.idinternalvalidation 
= internalvalidation.idinternalvalidation
     JOIN users v on uhi.iduser = v.iduser
     JOIN get_user(v.iduser) gu ON gu.iduser = v.iduser AND ( 
gu.canvalidate OR v.isgroup )

     UNION

     SELECT
         ugiwus.idinternalvalidation
         ,ugiwus.iduser as idusergroup
         ,ugiwus.hasvalidate
         ,ugiwus.atleastonemustvalidate
         ,gu.iduser
         ,gu.isgroup
         , ugiwus.level + 1
     FROM u_g_internalvalidation_without_u_status ugiwus
     JOIN users_has_groups uhg ON uhg.idgroup = ugiwus.idusergroup AND 
ugiwus.isgroup
     JOIN get_user(uhg.iduser) gu ON ( gu.canvalidate OR gu.isgroup )
     WHERE ugiwus.level < 25
)
--    SELECT * FROM u_g_internalvalidation_without_u_status;
,u_g_internalvalidation AS (
     SELECT ugi.idinternalvalidation, 
bool_or(COALESCE(ugi.hasvalidate,false)) currentuserhasvalidate--, 
bool_or( ugi.iduser = u.iduser ) /* à quoi sert ce champ ? */
     FROM u_g_internalvalidation_without_u_status ugi
--    CROSS JOIN u /* et donc à quoi sert cette table ? */
--    WHERE u.iduser = ugi.iduser
     GROUP BY ugi.idinternalvalidation
)
,regroup_users as (
     SELECT -- regroup users by group etc ...
         ug1.idinternalvalidation
         ,ug1.idusergroup
         ,BOOL_OR( COALESCE( ug2.hasvalidate, ug1.hasvalidate,false)) 
FILTER ( WHERE NOT ug1.isgroup OR ug2.isgroup ) AS atleastoneuserhasvalidate
         ,BOOL_AND( COALESCE( ug2.hasvalidate, ug1.hasvalidate,false)) 
FILTER ( WHERE NOT ug1.isgroup OR ug2.isgroup ) AS everyuserhasvalidate
         ,ARRAY_AGG( DISTINCT ug1.iduser) AS iduser
         ,ug1.atleastonemustvalidate
     FROM u_g_internalvalidation_without_u_status ug1
     LEFT JOIN u_g_internalvalidation_without_u_status ug2 ON 
ug2.idinternalvalidation = ug1.idinternalvalidation AND ug2.iduser = 
ug1.iduser AND ug2.hasvalidate IS NOT NULL
     GROUP BY 
ug1.idinternalvalidation,ug1.idusergroup,ug1.atleastonemustvalidate
)
,regroup_internvalvalidations AS (
     SELECT
         r.idinternalvalidation
         ,ARRAY_AGG(r.idusergroup) AS iduser
         ,ARRAY_AGG(users.name) AS name
         ,BOOL_OR(v.validate) AS atleastoneuserhasvalidate
         ,BOOL_AND(v.validate) AS everyusershasvalidate
     FROM regroup_users r
     LEFT JOIN users ON users.iduser = r.idusergroup
     CROSS JOIN LATERAL (
         SELECT CASE
             WHEN r.atleastonemustvalidate THEN r.atleastoneuserhasvalidate
             ELSE r.everyuserhasvalidate
         END AS validate
     ) AS v
     GROUP BY r.idinternalvalidation
)
SELECT
     ri.idinternalvalidation
     ,ri.iduser
     ,ri.name
     ,ri.atleastoneuserhasvalidate
     ,ri.everyusershasvalidate
     ,i.ttl
     ,i.idfile
     ,i.idclaimer
     ,i.name AS claimername
     ,CASE WHEN i.atleastonemustvalidate THEN 
ri.atleastoneuserhasvalidate ELSE ri.everyusershasvalidate END AS 
internalvalidationisdone
     ,CASE WHEN u.iduser IS NULL THEN NULL::BOOLEAN ELSE 
u_g_internalvalidation.currentuserhasvalidate END AS currentuserhasvalidate
FROM regroup_internvalvalidations ri
JOIN internalvalidation i ON i.idinternalvalidation = 
ri.idinternalvalidation
LEFT JOIN u_g_internalvalidation ON 
u_g_internalvalidation.idinternalvalidation = ri.idinternalvalidation
LEFT JOIN u ON u_g_internalvalidation.idinternalvalidation IS NOT NULL 
AND u.canvalidate