PEV2 1.15.0
Execution time: 10s 439ms
Planning time: 0.94ms
Triggers: N/A
Main Query Plan
#1 Append
#15 ├ HashAggregate
#16 │└ Nested Loop
#17 │ ├ CTE Scan
#18 │ └ Function Scan
#19 ├ HashAggregate
#20 │└ Nested Loop
#21 │ ├ CTE Scan
#22 │ └ Function Scan
#23 ├ HashAggregate
#24 │└ Nested Loop
#25 │ ├ CTE Scan
#26 │ └ Function Scan
#27 └ HashAggregate
#28  └ Nested Loop
#29   ├ CTE Scan
#30   └ Function Scan
CTE all_moves_stage_task
#2 WindowAgg
#3 └ Sort
#4  └ WindowAgg
#5   └ Sort
#6    └ Nested Loop
#7     ├ Nested Loop
#8     │├ Nested Loop
#9     ││├ Nested Loop
#10     │││├ Index Scan
#11     │││└ Index Scan
#12     ││└ Index Scan
#13     │└ Index Only Scan
#14     └ Index Scan

Append

#1

HashAggregate

#15
by t.task_id, t.project_id, t.display_project_id, t.stage_id, d.d, t.user_id, t.date_assign, t.date_deadline, t.partner_id, 'day'::text, 1

HashAggregate

#19
by t_1.task_id, t_1.project_id, t_1.display_project_id, t_1.stage_id, date_trunc('week'::text, d_1.d), t_1.user_id, t_1.date_assign, t_1.date_deadline, t_1.partner_id, 'week'::text, 1

HashAggregate

#23
by t_2.task_id, t_2.project_id, t_2.display_project_id, t_2.stage_id, date_trunc('month'::text, d_2.d), t_2.user_id, t_2.date_assign, t_2.date_deadline, t_2.partner_id, 'month'::text, 1

HashAggregate

#27
by t_3.task_id, t_3.project_id, t_3.display_project_id, t_3.stage_id, date_trunc('year'::text, d_3.d), t_3.user_id, t_3.date_assign, t_3.date_deadline, t_3.partner_id, 'year'::text, 1

Nested Loop

#16

Nested Loop

#20

Nested Loop

#24

Nested Loop

#28

CTE Scan

#17
on t

Function Scan

#18
on generate_series as d

CTE Scan

#21
on t_1

Function Scan

#22
on generate_series as d_1

CTE Scan

#25
on t_2

Function Scan

#26
on generate_series as d_2

CTE Scan

#29
on t_3

Function Scan

#30
on generate_series as d_3
CTE all_moves_stage_task

WindowAgg

#2

Sort

#3
by mm.res_id, mm.id DESC

WindowAgg

#4

Sort

#5
by mm.res_id, mm.id

Nested Loop

#6

Nested Loop

#7

Index Scan

#14
on project_task as pt
using project_task_pkey

Nested Loop

#8

Index Only Scan

#13
on project_task_type as current_stage
using project_task_type_pkey

Nested Loop

#9

Index Scan

#12
on mail_message as mm
using mailgate_message_pkey

Index Scan

#10
on ir_model_fields as imf
using ir_model_fields_name_unique

Index Scan

#11
on mail_tracking_value as mtv
using mail_tracking_value_field_idx
                                                                                                            QUERY PLAN                                                                                                            
----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
 Append  (cost=228729.65..228901.14 rows=800 width=80) (actual time=10124.081..10406.517 rows=103857 loops=1)
   CTE all_moves_stage_task
     ->  WindowAgg  (cost=228679.04..228680.81 rows=59 width=60) (actual time=9229.352..9751.992 rows=1400206 loops=1)
           ->  Sort  (cost=228679.04..228679.19 rows=59 width=80) (actual time=9229.342..9324.861 rows=1400206 loops=1)
                 Sort Key: mm.res_id, mm.id DESC
                 Sort Method: external merge  Disk: 128184kB
                 ->  WindowAgg  (cost=228676.13..228677.31 rows=59 width=80) (actual time=8300.946..8777.831 rows=1400206 loops=1)
                       ->  Sort  (cost=228676.13..228676.28 rows=59 width=72) (actual time=8300.940..8401.294 rows=1400206 loops=1)
                             Sort Key: mm.res_id, mm.id
                             Sort Method: external merge  Disk: 120288kB
                             ->  Nested Loop  (cost=2.25..228674.39 rows=59 width=72) (actual time=0.029..7634.749 rows=1400206 loops=1)
                                   ->  Nested Loop  (cost=1.82..228579.94 rows=65 width=20) (actual time=0.024..5182.823 rows=1435072 loops=1)
                                         ->  Nested Loop  (cost=1.54..228492.80 rows=113 width=20) (actual time=0.019..3869.907 rows=1661945 loops=1)
                                               ->  Nested Loop  (cost=0.98..225482.08 rows=1815 width=8) (actual time=0.015..802.823 rows=1661945 loops=1)
                                                     ->  Index Scan using ir_model_fields_name_unique on ir_model_fields imf  (cost=0.41..2.43 rows=1 width=4) (actual time=0.007..0.008 rows=1 loops=1)
                                                           Index Cond: (((model)::text = 'project.task'::text) AND ((name)::text = 'stage_id'::text))
                                                     ->  Index Scan using mail_tracking_value_field_idx on mail_tracking_value mtv  (cost=0.56..223083.90 rows=239575 width=12) (actual time=0.007..701.326 rows=1661945 loops=1)
                                                           Index Cond: (field = imf.id)
                                               ->  Index Scan using mailgate_message_pkey on mail_message mm  (cost=0.57..1.66 rows=1 width=16) (actual time=0.002..0.002 rows=1 loops=1661945)
                                                     Index Cond: (id = mtv.mail_message_id)
                                                     Filter: (((message_type)::text = 'notification'::text) AND ((model)::text = 'project.task'::text))
                                         ->  Index Only Scan using project_task_type_pkey on project_task_type current_stage  (cost=0.28..0.77 rows=1 width=4) (actual time=0.001..0.001 rows=1 loops=1661945)
                                               Index Cond: (id = mtv.old_value_integer)
                                               Heap Fetches: 541401
                                   ->  Index Scan using project_task_pkey on project_task pt  (cost=0.42..1.45 rows=1 width=52) (actual time=0.001..0.001 rows=1 loops=1435072)
                                         Index Cond: (id = mm.res_id)
                                         Filter: active
                                         Rows Removed by Filter: 0
   ->  HashAggregate  (cost=48.83..50.83 rows=200 width=80) (actual time=10124.080..10139.896 rows=88235 loops=1)
         Group Key: t.task_id, t.project_id, t.display_project_id, t.stage_id, d.d, t.user_id, t.date_assign, t.date_deadline, t.partner_id, 'day'::text, 1
         ->  Nested Loop  (cost=0.01..21.33 rows=1000 width=80) (actual time=9420.375..10098.923 rows=88235 loops=1)
               ->  CTE Scan on all_moves_stage_task t  (cost=0.00..1.33 rows=1 width=52) (actual time=9420.264..10084.102 rows=447 loops=1)
                     Filter: (project_id = 1521)
                     Rows Removed by Filter: 1399759
               ->  Function Scan on generate_series d  (cost=0.01..10.01 rows=1000 width=8) (actual time=0.014..0.019 rows=197 loops=447)
   ->  HashAggregate  (cost=51.33..53.83 rows=200 width=80) (actual time=90.615..92.786 rows=12555 loops=1)
         Group Key: t_1.task_id, t_1.project_id, t_1.display_project_id, t_1.stage_id, date_trunc('week'::text, d_1.d), t_1.user_id, t_1.date_assign, t_1.date_deadline, t_1.partner_id, 'week'::text, 1
         ->  Nested Loop  (cost=0.01..23.83 rows=1000 width=80) (actual time=19.688..87.538 rows=12555 loops=1)
               ->  CTE Scan on all_moves_stage_task t_1  (cost=0.00..1.33 rows=1 width=52) (actual time=19.663..84.324 rows=447 loops=1)
                     Filter: (project_id = 1521)
                     Rows Removed by Filter: 1399759
               ->  Function Scan on generate_series d_1  (cost=0.01..10.01 rows=1000 width=8) (actual time=0.002..0.003 rows=28 loops=447)
   ->  HashAggregate  (cost=51.33..53.83 rows=200 width=80) (actual time=86.617..87.006 rows=2857 loops=1)
         Group Key: t_2.task_id, t_2.project_id, t_2.display_project_id, t_2.stage_id, date_trunc('month'::text, d_2.d), t_2.user_id, t_2.date_assign, t_2.date_deadline, t_2.partner_id, 'month'::text, 1
         ->  Nested Loop  (cost=0.01..23.83 rows=1000 width=80) (actual time=20.015..85.900 rows=2857 loops=1)
               ->  CTE Scan on all_moves_stage_task t_2  (cost=0.00..1.33 rows=1 width=52) (actual time=20.006..85.081 rows=447 loops=1)
                     Filter: (project_id = 1521)
                     Rows Removed by Filter: 1399759
               ->  Function Scan on generate_series d_2  (cost=0.01..10.01 rows=1000 width=8) (actual time=0.001..0.001 rows=6 loops=447)
   ->  HashAggregate  (cost=51.33..53.83 rows=200 width=80) (actual time=83.296..83.322 rows=210 loops=1)
         Group Key: t_3.task_id, t_3.project_id, t_3.display_project_id, t_3.stage_id, date_trunc('year'::text, d_3.d), t_3.user_id, t_3.date_assign, t_3.date_deadline, t_3.partner_id, 'year'::text, 1
         ->  Nested Loop  (cost=0.01..23.83 rows=1000 width=80) (actual time=19.459..83.231 rows=210 loops=1)
               ->  CTE Scan on all_moves_stage_task t_3  (cost=0.00..1.33 rows=1 width=52) (actual time=19.453..82.976 rows=447 loops=1)
                     Filter: (project_id = 1521)
                     Rows Removed by Filter: 1399759
               ->  Function Scan on generate_series d_3  (cost=0.01..10.01 rows=1000 width=8) (actual time=0.000..0.000 rows=0 loops=447)
 Planning time: 0.940 ms
 Execution time: 10438.778 ms
(58 rows)

CREATE OR REPLACE VIEW nse AS (
            WITH all_moves_stage_task AS (
                SELECT pt.id as task_id,
                       pt.project_id,
                       pt.display_project_id,
                       COALESCE(LAG(date_end) OVER (PARTITION BY mm.res_id ORDER BY mm.id), pt.create_date) as date_begin,
                       CASE
                         -- last change live for 1 year
                         WHEN LAG(mm.id) OVER (PARTITION BY mm.res_id ORDER BY mm.id DESC) IS NULL THEN (CURRENT_DATE + interval '1 year')::date
                         ELSE mm.date
                       END as date_end,
                       COALESCE(current_stage.id, pt.stage_id) as stage_id,
                       pt.user_id,
                       pt.date_assign,
                       pt.date_deadline,
                       pt.partner_id
                  FROM project_task pt
             LEFT JOIN mail_message mm ON mm.res_id = pt.id
                                      AND mm.message_type = 'notification'
                                      AND mm.model = 'project.task'
                  JOIN mail_tracking_value mtv ON mm.id = mtv.mail_message_id
                  JOIN ir_model_fields imf ON mtv.field = imf.id
                                          AND imf.model = 'project.task'
                                          AND imf.name = 'stage_id'
                  JOIN project_task_type current_stage ON mtv.old_value_integer = current_stage.id
                 WHERE pt.active
            )
SELECT DISTINCT task_id,
       project_id,
       display_project_id,
       stage_id,
       d,
       user_id,
       date_assign,
       date_deadline,
       partner_id,
       'day' AS group_by,
       1 AS nb_tasks
  FROM all_moves_stage_task t
  JOIN LATERAL generate_series(t.date_begin,t.date_end-interval '1 day', '1 day') d ON TRUE

UNION ALL

SELECT DISTINCT task_id,
       project_id,
       display_project_id,
       stage_id,
       date_trunc('week', d),
       user_id,
       date_assign,
       date_deadline,
       partner_id,
       'week' AS group_by,
       1 AS nb_tasks
  FROM all_moves_stage_task t
  JOIN LATERAL generate_series(t.date_begin,t.date_end-interval '1 week', '1 week') d ON TRUE

UNION ALL

SELECT DISTINCT task_id,
       project_id,
       display_project_id,
       stage_id,
       date_trunc('month', d),
       user_id,
       date_assign,
       date_deadline,
       partner_id,
       'month' AS group_by,
       1 AS nb_tasks
  FROM all_moves_stage_task t
  JOIN LATERAL generate_series(t.date_begin,t.date_end-interval '1 month', '1 month') d ON TRUE

UNION ALL

SELECT DISTINCT task_id,
       project_id,
       display_project_id,
       stage_id,
       date_trunc('year', d),
       user_id,
       date_assign,
       date_deadline,
       partner_id,
       'year' AS group_by,
       1 AS nb_tasks
  FROM all_moves_stage_task t
  JOIN LATERAL generate_series(t.date_begin,t.date_end-interval '1 year', '1 year') d ON TRUE

);

explain analyse select * from nse where  project_id=1521
;
Table Count Time
mail_message 1 3s 324ms 32%
#12 Index Scan 3s 324ms 32%
project_task_type 1 1s 662ms 16%
#13 Index Only Scan 1s 662ms 16%
project_task 1 1s 435ms 14%
#14 Index Scan 1s 435ms 14%
mail_tracking_value 1 701ms 7%
#11 Index Scan 701ms 7%
ir_model_fields 1 0.008ms 0%
#10 Index Scan 0.008ms 0%
Function Count Time
generate_series 4 10.3ms 0%
#18 Function Scan 8.49ms 0%
#22 Function Scan 1.34ms 0%
#26 Function Scan 0.447ms 0%
#30 Function Scan 0ms 0%
Node Type Count Time
CTE Scan 4 10s 336ms 99%
#17 CTE Scan 10s 84.1ms 97%
#25 CTE Scan 85.1ms 1%
#21 CTE Scan 84.3ms 1%
#29 CTE Scan 83ms 1%
Index Scan 4 5s 460ms 52%
#12 Index Scan 3s 324ms 32%
#14 Index Scan 1s 435ms 14%
#11 Index Scan 701ms 7%
#10 Index Scan 0.008ms 0%
Index Only Scan 1 1s 662ms 16%
#13 Index Only Scan 1s 662ms 16%
Sort 2 1s 314ms 13%
#5 Sort 767ms 7%
#3 Sort 547ms 5%
WindowAgg 2 804ms 8%
#2 WindowAgg 427ms 4%
#4 WindowAgg 377ms 4%
Nested Loop 8 521ms 5%
#6 Nested Loop 411ms 4%
#9 Nested Loop 101ms 1%
#16 Nested Loop 6.33ms 0%
#20 Nested Loop 1.87ms 0%
#24 Nested Loop 0.372ms 0%
#28 Nested Loop 0.255ms 0%
#8 Nested Loop 0ms 0%
#7 Nested Loop 0ms 0%
HashAggregate 4 47.4ms 0%
#15 HashAggregate 41ms 0%
#19 HashAggregate 5.25ms 0%
#23 HashAggregate 1.11ms 0%
#27 HashAggregate 0.091ms 0%
Function Scan 4 10.3ms 0%
#18 Function Scan 8.49ms 0%
#22 Function Scan 1.34ms 0%
#26 Function Scan 0.447ms 0%
#30 Function Scan 0ms 0%
Append 1 3.51ms 0%
#1 Append 3.51ms 0%
Index Count Time
mailgate_message_pkey 1 3s 324ms 32%
#12 Index Scan 3s 324ms 32%
project_task_type_pkey 1 1s 662ms 16%
#13 Index Only Scan 1s 662ms 16%
project_task_pkey 1 1s 435ms 14%
#14 Index Scan 1s 435ms 14%
mail_tracking_value_field_idx 1 701ms 7%
#11 Index Scan 701ms 7%
ir_model_fields_name_unique 1 0.008ms 0%
#10 Index Scan 0.008ms 0%