explain.depesz.com

PostgreSQL's explain analyze made readable

Result: A8IJ : Campain

Settings
# 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