explain.depesz.com

PostgreSQL's explain analyze made readable

Result: lB0y

Settings
# exclusive inclusive rows x rows loops node
1. 17.522 12,756.805 ↓ 30.4 44,180 1

Subquery Scan on q (cost=969,855.63..969,899.19 rows=1,452 width=913) (actual time=12,702.985..12,756.805 rows=44,180 loops=1)

  • Filter: q.sem_retorno
2. 11.546 12,739.283 ↓ 15.2 44,180 1

Unique (cost=969,855.63..969,870.15 rows=2,904 width=977) (actual time=12,702.982..12,739.283 rows=44,180 loops=1)

3. 117.694 12,727.737 ↓ 16.8 48,760 1

Sort (cost=969,855.63..969,862.89 rows=2,904 width=977) (actual time=12,702.980..12,727.737 rows=48,760 loops=1)

  • Sort Key: e.ema_env_codigo DESC, gec.ema_con_codigo DESC
  • Sort Method: external merge Disk: 22,864kB
4. 490.891 12,610.043 ↓ 16.8 48,760 1

Hash Left Join (cost=140,213.35..969,688.60 rows=2,904 width=977) (actual time=10,267.161..12,610.043 rows=48,760 loops=1)

  • Hash Cond: (c.rem_com_codigo = gse.cod_arq_rem_consumidores)
5. 180.056 2,818.225 ↓ 16.8 48,760 1

Hash Right Join (cost=38,197.55..867,422.33 rows=2,904 width=1,623) (actual time=966.192..2,818.225 rows=48,760 loops=1)

  • Hash Cond: ("*SELECT* 1".rem_com_codigo = c.rem_com_codigo)
6. 2.247 1,687.082 ↑ 37.2 14,058 1

Append (cost=0.71..818,510.67 rows=522,972 width=36) (actual time=0.132..1,687.082 rows=14,058 loops=1)

7. 0.376 796.183 ↑ 189.1 1,383 1

Subquery Scan on *SELECT* 1 (cost=0.71..409,255.33 rows=261,486 width=36) (actual time=0.131..796.183 rows=1,383 loops=1)

8. 1.702 795.807 ↑ 189.1 1,383 1

Nested Loop (cost=0.71..406,640.47 rows=261,486 width=40) (actual time=0.131..795.807 rows=1,383 loops=1)

9. 6.800 787.925 ↑ 169.2 1,545 1

Subquery Scan on enrichment_empresa (cost=0.70..11,142.89 rows=261,486 width=36) (actual time=0.117..787.925 rows=1,545 loops=1)

  • Filter: (enrichment_empresa.contato IS NOT NULL)
  • Rows Removed by Filter: 38,330
10. 734.001 781.125 ↑ 6.6 39,875 1

Nested Loop (cost=0.70..8,514.89 rows=262,800 width=40) (actual time=0.106..781.125 rows=39,875 loops=1)

11. 0.022 0.022 ↑ 1.0 1 1

Index Scan using arq_rem_arquivo_rem_arq_status_idx on arq_rem_arquivo arq (cost=0.28..8.29 rows=1 width=4) (actual time=0.021..0.022 rows=1 loops=1)

  • Index Cond: (rem_arq_status = 'PR'::bpchar)
12. 47.102 47.102 ↓ 12.2 44,180 1

Index Scan using arq_rem_consumidores_cod_arq_rem_arquivo_idx on arq_rem_consumidores arc (cost=0.43..7,143.25 rows=3,621 width=358) (actual time=0.014..47.102 rows=44,180 loops=1)

  • Index Cond: (cod_arq_rem_arquivo = arq.rem_arq_codigo)
13. 6.180 6.180 ↑ 1.0 1 1,545

Function Scan on jsonb_array_elements emails (cost=0.01..1.50 rows=1 width=32) (actual time=0.003..0.004 rows=1 loops=1,545)

  • Filter: ((value ->> 'ordem'::text) = '1'::text)
  • Rows Removed by Filter: 0
14. 2.509 888.652 ↑ 20.6 12,675 1

Subquery Scan on *SELECT* 2 (cost=0.71..409,255.33 rows=261,486 width=36) (actual time=0.877..888.652 rows=12,675 loops=1)

15. 33.945 886.143 ↑ 20.6 12,675 1

Nested Loop (cost=0.71..406,640.47 rows=261,486 width=40) (actual time=0.875..886.143 rows=12,675 loops=1)

16. 8.763 775.538 ↑ 6.8 38,330 1

Subquery Scan on enrichment_pessoa (cost=0.70..11,142.89 rows=261,486 width=36) (actual time=0.370..775.538 rows=38,330 loops=1)

  • Filter: (enrichment_pessoa.contato IS NOT NULL)
  • Rows Removed by Filter: 1,545
17. 717.049 766.775 ↑ 6.6 39,875 1

Nested Loop (cost=0.70..8,514.89 rows=262,800 width=40) (actual time=0.170..766.775 rows=39,875 loops=1)

18. 0.046 0.046 ↑ 1.0 1 1

Index Scan using arq_rem_arquivo_rem_arq_status_idx on arq_rem_arquivo arq_1 (cost=0.28..8.29 rows=1 width=4) (actual time=0.045..0.046 rows=1 loops=1)

  • Index Cond: (rem_arq_status = 'PR'::bpchar)
19. 49.680 49.680 ↓ 12.2 44,180 1

Index Scan using arq_rem_consumidores_cod_arq_rem_arquivo_idx on arq_rem_consumidores arc_1 (cost=0.43..7,143.25 rows=3,621 width=358) (actual time=0.051..49.680 rows=44,180 loops=1)

  • Index Cond: (cod_arq_rem_arquivo = arq_1.rem_arq_codigo)
20. 76.660 76.660 ↓ 0.0 0 38,330

Function Scan on jsonb_array_elements emails_1 (cost=0.01..1.50 rows=1 width=32) (actual time=0.002..0.002 rows=0 loops=38,330)

  • Filter: ((value ->> 'ordem'::text) = '1'::text)
  • Rows Removed by Filter: 0
21. 175.171 951.087 ↓ 16.8 48,760 1

Hash (cost=37,587.55..37,587.55 rows=2,904 width=1,591) (actual time=951.087..951.087 rows=48,760 loops=1)

  • Buckets: 4,096 (originally 4096) Batches: 32 (originally 2) Memory Usage: 4,067kB
22. 25.226 775.916 ↓ 16.8 48,760 1

Nested Loop (cost=2.41..37,587.55 rows=2,904 width=1,591) (actual time=1.369..775.916 rows=48,760 loops=1)

23. 37.895 529.790 ↓ 16.9 44,180 1

Nested Loop Left Join (cost=1.98..29,912.60 rows=2,609 width=1,311) (actual time=0.874..529.790 rows=44,180 loops=1)

24. 9.329 403.535 ↓ 16.9 44,180 1

Nested Loop (cost=1.56..22,372.53 rows=2,609 width=1,315) (actual time=0.842..403.535 rows=44,180 loops=1)

25. 31.101 261.666 ↓ 16.9 44,180 1

Nested Loop (cost=1.13..14,782.74 rows=2,609 width=964) (actual time=0.568..261.666 rows=44,180 loops=1)

26. 12.117 53.845 ↓ 16.8 44,180 1

Nested Loop (cost=0.70..7,187.75 rows=2,628 width=730) (actual time=0.025..53.845 rows=44,180 loops=1)

27. 0.015 0.015 ↑ 1.0 1 1

Index Scan using arq_rem_arquivo_rem_arq_status_idx on arq_rem_arquivo a (cost=0.28..8.29 rows=1 width=25) (actual time=0.014..0.015 rows=1 loops=1)

  • Index Cond: (rem_arq_status = 'PR'::bpchar)
28. 41.713 41.713 ↓ 12.2 44,180 1

Index Scan using arq_rem_consumidores_cod_arq_rem_arquivo_idx on arq_rem_consumidores c (cost=0.43..7,143.25 rows=3,621 width=709) (actual time=0.010..41.713 rows=44,180 loops=1)

  • Index Cond: (cod_arq_rem_arquivo = a.rem_arq_codigo)
29. 176.720 176.720 ↑ 1.0 1 44,180

Index Scan using ger_emails_enviados_cod_arq_rem_consumidores_idx on ger_emails_enviados e (cost=0.43..2.88 rows=1 width=234) (actual time=0.004..0.004 rows=1 loops=44,180)

  • Index Cond: (cod_arq_rem_consumidores = c.rem_com_codigo)
30. 132.540 132.540 ↑ 1.0 1 44,180

Index Scan using arq_rem_consumidores_pkey on arq_rem_consumidores consumidores (cost=0.43..2.90 rows=1 width=355) (actual time=0.002..0.003 rows=1 loops=44,180)

  • Index Cond: (rem_com_codigo = c.rem_com_codigo)
31. 88.360 88.360 ↑ 1.0 1 44,180

Index Only Scan using ger_emails_enviados_cod_arq_rem_consumidores_idx on ger_emails_enviados (cost=0.43..2.88 rows=1 width=4) (actual time=0.002..0.002 rows=1 loops=44,180)

  • Index Cond: (cod_arq_rem_consumidores = consumidores.rem_com_codigo)
  • Heap Fetches: 44,180
32. 220.900 220.900 ↑ 1.0 1 44,180

Index Scan using ger_emails_consulta_cod_ger_emails_enviados_idx on ger_emails_consulta gec (cost=0.43..2.93 rows=1 width=284) (actual time=0.004..0.005 rows=1 loops=44,180)

  • Index Cond: (cod_ger_emails_enviados = e.ema_env_codigo)
33. 35.050 9,300.927 ↓ 142.4 31,752 1

Hash (cost=102,013.01..102,013.01 rows=223 width=323) (actual time=9,300.927..9,300.927 rows=31,752 loops=1)

  • Buckets: 32,768 (originally 1024) Batches: 2 (originally 1) Memory Usage: 3,841kB
34. 97.986 9,265.877 ↓ 142.4 31,752 1

Hash Join (cost=7,220.60..102,013.01 rows=223 width=323) (actual time=40.748..9,265.877 rows=31,752 loops=1)

  • Hash Cond: (gse.cod_arq_rem_consumidores = c_1.rem_com_codigo)
35. 9,127.767 9,127.767 ↓ 1.0 149,133 1

Seq Scan on ger_sms_enviados gse (cost=0.00..94,231.71 rows=148,923 width=291) (actual time=0.189..9,127.767 rows=149,133 loops=1)

  • Filter: (cod_arq_ret_arquivo IS NULL)
  • Rows Removed by Filter: 187,427
36. 9.921 40.124 ↓ 16.8 44,180 1

Hash (cost=7,187.75..7,187.75 rows=2,628 width=4) (actual time=40.124..40.124 rows=44,180 loops=1)

  • Buckets: 65,536 (originally 4096) Batches: 1 (originally 1) Memory Usage: 2,066kB
37. 7.296 30.203 ↓ 16.8 44,180 1

Nested Loop (cost=0.70..7,187.75 rows=2,628 width=4) (actual time=0.021..30.203 rows=44,180 loops=1)

38. 0.011 0.011 ↑ 1.0 1 1

Index Scan using arq_rem_arquivo_rem_arq_status_idx on arq_rem_arquivo a_1 (cost=0.28..8.29 rows=1 width=4) (actual time=0.010..0.011 rows=1 loops=1)

  • Index Cond: (rem_arq_status = 'PR'::bpchar)
39. 22.896 22.896 ↓ 12.2 44,180 1

Index Scan using arq_rem_consumidores_cod_arq_rem_arquivo_idx on arq_rem_consumidores c_1 (cost=0.43..7,143.25 rows=3,621 width=8) (actual time=0.009..22.896 rows=44,180 loops=1)

  • Index Cond: (cod_arq_rem_arquivo = a_1.rem_arq_codigo)
Planning time : 15.152 ms
Execution time : 12,763.118 ms