#	exclusive	inclusive	rows x	rows	loops	node
1.	4.068	4,729.786	↑ 1,835,405.3	3	1	→ GroupAggregate (cost=3,246,250.02..3,370,139.88 rows=5,506,216 width=572) (actual time=4,728.929..4,729.786 rows=3 loops=1) Group Key: msg.id_campaign, (COALESCE(sp.name, st.name)), (COALESCE(sp.color, st.color))
2.						CTE statuspro
3.	0.000	1,308.838	↓ 8.9	390,457	1	→ Gather Merge (cost=34,395.02..39,512.61 rows=43,862 width=588) (actual time=899.094..1,308.838 rows=390,457 loops=1) Workers Planned: 2 Workers Launched: 2
4.	925.830	2,320.707	↓ 5.9	130,152	3	→ Sort (cost=33,395.00..33,449.82 rows=21,931 width=588) (actual time=697.758..773.569 rows=130,152 loops=3) Sort Key: mst.id Sort Method: external sort Disk: 7656kB
5.	1,175.253	1,394.877	↓ 5.9	130,152	3	→ Hash Join (cost=6.56..28,431.20 rows=21,931 width=588) (actual time=0.565..464.959 rows=130,152 loops=3) Hash Cond: (((COALESCE(mst.delivery_status_code, (mst.cod_status)::character varying))::text = (sp_1.provider_code)::text) AND (mst.id_provider = sp_1.id_provider))
6.	218.544	218.544	↑ 1.2	270,975	3	→ Parallel Seq Scan on messages_status mst (cost=0.00..23,079.60 rows=338,060 width=67) (actual time=0.011..72.848 rows=270,975 loops=3)
7.	0.168	1.080	↓ 1.1	121	3	→ Hash (cost=4.86..4.86 rows=113 width=567) (actual time=0.359..0.360 rows=121 loops=3) Buckets: 1024 Batches: 1 Memory Usage: 19kB
8.	0.282	0.912	↓ 1.1	121	3	→ Hash Join (cost=1.36..4.86 rows=113 width=567) (actual time=0.238..0.304 rows=121 loops=3) Hash Cond: (sp_1.id_status = st_1.id_status)
9.	0.306	0.306	↓ 1.1	121	3	→ Seq Scan on status_providers sp_1 (cost=0.00..3.13 rows=113 width=23) (actual time=0.089..0.102 rows=121 loops=3)
10.	0.063	0.324	↓ 1.1	18	3	→ Hash (cost=1.16..1.16 rows=16 width=552) (actual time=0.108..0.108 rows=18 loops=3) Buckets: 1024 Batches: 1 Memory Usage: 10kB
11.	0.261	0.261	↓ 1.1	18	3	→ Seq Scan on status st_1 (cost=0.00..1.16 rows=16 width=552) (actual time=0.081..0.087 rows=18 loops=3)
12.	10.196	4,725.718	↑ 220.2	25,004	1	→ Sort (cost=3,206,737.41..3,220,502.95 rows=5,506,216 width=564) (actual time=4,724.847..4,725.718 rows=25,004 loops=1) Sort Key: (COALESCE(sp.name, st.name)), (COALESCE(sp.color, st.color)) Sort Method: quicksort Memory: 4016kB
13.	143.895	4,715.522	↑ 220.2	25,004	1	→ Merge Left Join (cost=32,606.43..143,091.47 rows=5,506,216 width=564) (actual time=4,583.959..4,715.522 rows=25,004 loops=1) Merge Cond: ((btrim((msg.ncode_op)::text)) = (btrim(sp.ident_sms_op)))
14.	191.001	212.188	↑ 1.0	25,004	1	→ Sort (cost=21,672.48..21,735.25 rows=25,107 width=601) (actual time=202.619..212.188 rows=25,004 loops=1) Sort Key: (btrim((msg.ncode_op)::text)) Sort Method: external sort Disk: 2840kB
15.	13.479	21.187	↑ 1.0	25,004	1	→ Hash Join (cost=386.66..15,866.83 rows=25,107 width=601) (actual time=2.473..21.187 rows=25,004 loops=1) Hash Cond: (msg.id_status = st.id_status)
16.	5.687	7.681	↑ 1.0	25,004	1	→ Bitmap Heap Scan on messages msg (cost=385.30..15,782.11 rows=25,107 width=57) (actual time=2.413..7.681 rows=25,004 loops=1) Recheck Cond: (id_campaign = '6355e880-4815-415e-be3c-2ecad07678f4'::uuid) Heap Blocks: exact=3487
17.	1.994	1.994	↑ 1.0	25,004	1	→ Bitmap Index Scan on "IX_messages_id_campaign" (cost=0.00..379.02 rows=25,107 width=0) (actual time=1.994..1.994 rows=25,004 loops=1) Index Cond: (id_campaign = '6355e880-4815-415e-be3c-2ecad07678f4'::uuid)
18.	0.009	0.027	↓ 1.1	18	1	→ Hash (cost=1.16..1.16 rows=16 width=552) (actual time=0.027..0.027 rows=18 loops=1) Buckets: 1024 Batches: 1 Memory Usage: 10kB
19.	0.018	0.018	↓ 1.1	18	1	→ Seq Scan on status st (cost=0.00..1.16 rows=16 width=552) (actual time=0.013..0.018 rows=18 loops=1)
20.	34.685	4,359.439	↓ 8.5	371,183	1	→ Materialize (cost=10,933.95..11,153.26 rows=43,862 width=580) (actual time=3,903.662..4,359.439 rows=371,183 loops=1)
21.	2,506.053	4,324.754	↓ 8.5	371,183	1	→ Sort (cost=10,933.95..11,043.61 rows=43,862 width=580) (actual time=3,903.652..4,324.754 rows=371,183 loops=1) Sort Key: (btrim(sp.ident_sms_op)) Sort Method: external merge Disk: 45512kB
22.	1,818.701	1,818.701	↓ 8.9	390,457	1	→ CTE Scan on statuspro sp (cost=0.00..877.24 rows=43,862 width=580) (actual time=899.103..1,818.701 rows=390,457 loops=1)

Planning time	:	3.026 ms
Execution time	:	4,750.308 ms

GroupAggregate  (cost=3246250.02..3370139.88 rows=5506216 width=572) (actual time=4728.929..4729.786 rows=3 loops=1)
  Group Key: msg.id_campaign, (COALESCE(sp.name, st.name)), (COALESCE(sp.color, st.color))
  CTE statuspro
    ->  Gather Merge  (cost=34395.02..39512.61 rows=43862 width=588) (actual time=899.094..1308.838 rows=390457 loops=1)
          Workers Planned: 2
          Workers Launched: 2
          ->  Sort  (cost=33395.00..33449.82 rows=21931 width=588) (actual time=697.758..773.569 rows=130152 loops=3)
                Sort Key: mst.id
                Sort Method: external sort  Disk: 7656kB
                ->  Hash Join  (cost=6.56..28431.20 rows=21931 width=588) (actual time=0.565..464.959 rows=130152 loops=3)
                      Hash Cond: (((COALESCE(mst.delivery_status_code, (mst.cod_status)::character varying))::text = (sp_1.provider_code)::text) AND (mst.id_provider = sp_1.id_provider))
                      ->  Parallel Seq Scan on messages_status mst  (cost=0.00..23079.60 rows=338060 width=67) (actual time=0.011..72.848 rows=270975 loops=3)
                      ->  Hash  (cost=4.86..4.86 rows=113 width=567) (actual time=0.359..0.360 rows=121 loops=3)
                            Buckets: 1024  Batches: 1  Memory Usage: 19kB
                            ->  Hash Join  (cost=1.36..4.86 rows=113 width=567) (actual time=0.238..0.304 rows=121 loops=3)
                                  Hash Cond: (sp_1.id_status = st_1.id_status)
                                  ->  Seq Scan on status_providers sp_1  (cost=0.00..3.13 rows=113 width=23) (actual time=0.089..0.102 rows=121 loops=3)
                                  ->  Hash  (cost=1.16..1.16 rows=16 width=552) (actual time=0.108..0.108 rows=18 loops=3)
                                        Buckets: 1024  Batches: 1  Memory Usage: 10kB
                                        ->  Seq Scan on status st_1  (cost=0.00..1.16 rows=16 width=552) (actual time=0.081..0.087 rows=18 loops=3)
  ->  Sort  (cost=3206737.41..3220502.95 rows=5506216 width=564) (actual time=4724.847..4725.718 rows=25004 loops=1)
        Sort Key: (COALESCE(sp.name, st.name)), (COALESCE(sp.color, st.color))
        Sort Method: quicksort  Memory: 4016kB
        ->  Merge Left Join  (cost=32606.43..143091.47 rows=5506216 width=564) (actual time=4583.959..4715.522 rows=25004 loops=1)
              Merge Cond: ((btrim((msg.ncode_op)::text)) = (btrim(sp.ident_sms_op)))
              ->  Sort  (cost=21672.48..21735.25 rows=25107 width=601) (actual time=202.619..212.188 rows=25004 loops=1)
                    Sort Key: (btrim((msg.ncode_op)::text))
                    Sort Method: external sort  Disk: 2840kB
                    ->  Hash Join  (cost=386.66..15866.83 rows=25107 width=601) (actual time=2.473..21.187 rows=25004 loops=1)
                          Hash Cond: (msg.id_status = st.id_status)
                          ->  Bitmap Heap Scan on messages msg  (cost=385.30..15782.11 rows=25107 width=57) (actual time=2.413..7.681 rows=25004 loops=1)
                                Recheck Cond: (id_campaign = '6355e880-4815-415e-be3c-2ecad07678f4'::uuid)
                                Heap Blocks: exact=3487
                                ->  Bitmap Index Scan on "IX_messages_id_campaign"  (cost=0.00..379.02 rows=25107 width=0) (actual time=1.994..1.994 rows=25004 loops=1)
                                      Index Cond: (id_campaign = '6355e880-4815-415e-be3c-2ecad07678f4'::uuid)
                          ->  Hash  (cost=1.16..1.16 rows=16 width=552) (actual time=0.027..0.027 rows=18 loops=1)
                                Buckets: 1024  Batches: 1  Memory Usage: 10kB
                                ->  Seq Scan on status st  (cost=0.00..1.16 rows=16 width=552) (actual time=0.013..0.018 rows=18 loops=1)
              ->  Materialize  (cost=10933.95..11153.26 rows=43862 width=580) (actual time=3903.662..4359.439 rows=371183 loops=1)
                    ->  Sort  (cost=10933.95..11043.61 rows=43862 width=580) (actual time=3903.652..4324.754 rows=371183 loops=1)
                          Sort Key: (btrim(sp.ident_sms_op))
                          Sort Method: external merge  Disk: 45512kB
                          ->  CTE Scan on statuspro sp  (cost=0.00..877.24 rows=43862 width=580) (actual time=899.103..1818.701 rows=390457 loops=1)
Planning time: 3.026 ms
Execution time: 4750.308 ms

Per node type stats

node type	count	sum of times	% of query
Bitmap Heap Scan	1	5.687 ms	0.1 %
Bitmap Index Scan	1	1.994 ms	0.0 %
CTE Scan	1	1,818.701 ms	38.5 %
Gather Merge	1	0.000 ms	0.0 %
GroupAggregate	1	4.068 ms	0.1 %
Hash	3	0.240 ms	0.0 %
Hash Join	3	1,189.014 ms	25.1 %
Materialize	1	34.685 ms	0.7 %
Merge Left Join	1	143.895 ms	3.0 %
Parallel Seq Scan	1	218.544 ms	4.6 %
Seq Scan	3	0.585 ms	0.0 %
Sort	4	3,633.080 ms	76.8 %

Per table stats

Table name	Scan count	Total time	% of query
scan type	count	sum of times	% of table
messages	1	5.687 ms	0.1 %
Bitmap Heap Scan	1	5.687 ms	100.0 %
messages_status	1	218.544 ms	4.6 %
Parallel Seq Scan	1	218.544 ms	100.0 %
status	2	0.279 ms	0.0 %
Seq Scan	2	0.279 ms	100.0 %
status_providers	1	0.306 ms	0.0 %
Seq Scan	1	0.306 ms	100.0 %

explain.depesz.com

PostgreSQL's explain analyze made readable

Result: A8IJ : Campain

Color mode:

Visible columns:

Per node type stats

Per table stats