explain.depesz.com

PostgreSQL's explain analyze made readable

Result: hIG : Optimization for: plan #6CZh

Settings

Optimization path:

# exclusive inclusive rows x rows loops node
1. 144.362 4,691.367 ↑ 223.3 25,004 1

Merge Left Join (cost=74,488.00..186,520.47 rows=5,583,428 width=1,428) (actual time=4,539.385..4,691.367 rows=25,004 loops=1)

  • Merge Cond: ((btrim((msg.ncode_op)::text)) = (btrim(sp.ident_sms_op)))
2.          

CTE statuspro

3. 0.000 1,243.794 ↓ 8.9 390,626 1

Gather Merge (cost=34,456.81..39,586.30 rows=43,964 width=592) (actual time=897.661..1,243.794 rows=390,626 loops=1)

  • Workers Planned: 2
  • Workers Launched: 2
4. 1,132.935 2,270.910 ↓ 5.9 130,209 3

Sort (cost=33,456.79..33,511.74 rows=21,982 width=592) (actual time=680.677..756.970 rows=130,209 loops=3)

  • Sort Key: mst.id
  • Sort Method: external sort Disk: 11800kB
5. 990.381 1,137.975 ↓ 5.9 130,209 3

Hash Join (cost=6.56..28,482.79 rows=21,982 width=592) (actual time=0.530..379.325 rows=130,209 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. 146.508 146.508 ↑ 1.2 271,089 3

Parallel Seq Scan on messages_status mst (cost=0.00..23,118.58 rows=338,858 width=67) (actual time=0.010..48.836 rows=271,089 loops=3)

7. 0.192 1.086 ↓ 1.1 121 3

Hash (cost=4.86..4.86 rows=113 width=571) (actual time=0.362..0.362 rows=121 loops=3)

  • Buckets: 1024 Batches: 1 Memory Usage: 20kB
8. 0.306 0.894 ↓ 1.1 121 3

Hash Join (cost=1.36..4.86 rows=113 width=571) (actual time=0.230..0.298 rows=121 loops=3)

  • Hash Cond: (sp_1.id_status = st_1.id_status)
9. 0.297 0.297 ↓ 1.1 121 3

Seq Scan on status_providers sp_1 (cost=0.00..3.13 rows=113 width=23) (actual time=0.086..0.099 rows=121 loops=3)

10. 0.048 0.291 ↓ 1.1 18 3

Hash (cost=1.16..1.16 rows=16 width=552) (actual time=0.097..0.097 rows=18 loops=3)

  • Buckets: 1024 Batches: 1 Memory Usage: 10kB
11. 0.243 0.243 ↓ 1.1 18 3

Seq Scan on status st_1 (cost=0.00..1.16 rows=16 width=552) (actual time=0.076..0.081 rows=18 loops=3)

12. 187.363 213.960 ↑ 1.0 25,004 1

Sort (cost=24,293.31..24,356.81 rows=25,400 width=945) (actual time=192.711..213.960 rows=25,004 loops=1)

  • Sort Key: (btrim((msg.ncode_op)::text))
  • Sort Method: external merge Disk: 7536kB
13. 8.682 26.597 ↑ 1.0 25,004 1

Nested Loop (cost=389.22..16,229.62 rows=25,400 width=945) (actual time=2.644..26.597 rows=25,004 loops=1)

14. 0.030 0.030 ↑ 1.0 1 1

Index Scan using "PK_campaigns" on campaigns cmp (cost=0.29..2.51 rows=1 width=76) (actual time=0.027..0.030 rows=1 loops=1)

  • Index Cond: (id = '6355e880-4815-415e-be3c-2ecad07678f4'::uuid)
15. 8.981 17.885 ↑ 1.0 25,004 1

Hash Join (cost=388.93..15,973.12 rows=25,400 width=885) (actual time=2.609..17.885 rows=25,004 loops=1)

  • Hash Cond: (msg.id_status = st.id_status)
16. 6.799 8.883 ↑ 1.0 25,004 1

Bitmap Heap Scan on messages msg (cost=387.57..15,887.41 rows=25,400 width=369) (actual time=2.578..8.883 rows=25,004 loops=1)

  • Recheck Cond: (id_campaign = '6355e880-4815-415e-be3c-2ecad07678f4'::uuid)
  • Heap Blocks: exact=3487
17. 2.084 2.084 ↑ 1.0 25,004 1

Bitmap Index Scan on "IX_messages_id_campaign" (cost=0.00..381.22 rows=25,400 width=0) (actual time=2.084..2.084 rows=25,004 loops=1)

  • Index Cond: (id_campaign = '6355e880-4815-415e-be3c-2ecad07678f4'::uuid)
18. 0.011 0.021 ↓ 1.1 18 1

Hash (cost=1.16..1.16 rows=16 width=520) (actual time=0.021..0.021 rows=18 loops=1)

  • Buckets: 1024 Batches: 1 Memory Usage: 10kB
19. 0.010 0.010 ↓ 1.1 18 1

Seq Scan on status st (cost=0.00..1.16 rows=16 width=520) (actual time=0.005..0.010 rows=18 loops=1)

20. 32.969 4,333.045 ↓ 8.4 371,346 1

Materialize (cost=10,608.39..10,828.21 rows=43,964 width=552) (actual time=3,905.998..4,333.045 rows=371,346 loops=1)

21. 2,518.119 4,300.076 ↓ 8.4 371,346 1

Sort (cost=10,608.39..10,718.30 rows=43,964 width=552) (actual time=3,905.984..4,300.076 rows=371,346 loops=1)

  • Sort Key: (btrim(sp.ident_sms_op))
  • Sort Method: external merge Disk: 44040kB
22. 1,781.957 1,781.957 ↓ 8.9 390,626 1

CTE Scan on statuspro sp (cost=0.00..879.28 rows=43,964 width=552) (actual time=897.672..1,781.957 rows=390,626 loops=1)

Planning time : 0.730 ms
Execution time : 4,720.817 ms