explain.depesz.com

PostgreSQL's explain analyze made readable

Result: 6CZh

Settings

Optimization(s) for this plan:

# exclusive inclusive rows x rows loops node
1. 154.468 4,825.758 ↑ 220.6 25,004 1

Merge Left Join (cost=74,259.57..184,915.52 rows=5,514,753 width=1,428) (actual time=4,668.228..4,825.758 rows=25,004 loops=1)

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

CTE statuspro

3. 0.000 1,320.555 ↓ 8.9 390,614 1

Gather Merge (cost=34,446.54..39,572.07 rows=43,930 width=592) (actual time=885.333..1,320.555 rows=390,614 loops=1)

  • Workers Planned: 2
  • Workers Launched: 2
4. 1,227.969 2,357.259 ↓ 5.9 130,205 3

Sort (cost=33,446.52..33,501.43 rows=21,965 width=592) (actual time=706.633..785.753 rows=130,205 loops=3)

  • Sort Key: mst.id
  • Sort Method: external sort Disk: 10920kB
5. 978.264 1,129.290 ↓ 5.9 130,205 3

Hash Join (cost=6.56..28,475.92 rows=21,965 width=592) (actual time=0.613..376.430 rows=130,205 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. 149.733 149.733 ↑ 1.2 271,081 3

Parallel Seq Scan on messages_status mst (cost=0.00..23,115.92 rows=338,592 width=67) (actual time=0.015..49.911 rows=271,081 loops=3)

7. 0.243 1.293 ↓ 1.1 121 3

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

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

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

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

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

10. 0.075 0.414 ↓ 1.1 18 3

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

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

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

12. 181.479 206.412 ↑ 1.0 25,004 1

Sort (cost=24,088.81..24,151.57 rows=25,107 width=945) (actual time=183.768..206.412 rows=25,004 loops=1)

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

Nested Loop (cost=386.95..16,120.41 rows=25,107 width=945) (actual time=2.432..24.933 rows=25,004 loops=1)

14. 0.025 0.025 ↑ 1.0 1 1

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

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

Hash Join (cost=386.66..15,866.83 rows=25,107 width=885) (actual time=2.402..16.926 rows=25,004 loops=1)

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

Bitmap Heap Scan on messages msg (cost=385.30..15,782.11 rows=25,107 width=369) (actual time=2.371..8.352 rows=25,004 loops=1)

  • Recheck Cond: (id_campaign = '6355e880-4815-415e-be3c-2ecad07678f4'::uuid)
  • Heap Blocks: exact=3487
17. 1.970 1.970 ↑ 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.969..1.970 rows=25,004 loops=1)

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

Hash (cost=1.16..1.16 rows=16 width=520) (actual time=0.017..0.017 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.008..0.010 rows=18 loops=1)

20. 34.533 4,464.878 ↓ 8.5 371,334 1

Materialize (cost=10,598.69..10,818.34 rows=43,930 width=552) (actual time=4,017.527..4,464.878 rows=371,334 loops=1)

21. 2,571.702 4,430.345 ↓ 8.5 371,334 1

Sort (cost=10,598.69..10,708.52 rows=43,930 width=552) (actual time=4,017.513..4,430.345 rows=371,334 loops=1)

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

CTE Scan on statuspro sp (cost=0.00..878.60 rows=43,930 width=552) (actual time=885.344..1,858.643 rows=390,614 loops=1)

Planning time : 0.656 ms
Execution time : 4,847.277 ms