explain.depesz.com

PostgreSQL's explain analyze made readable

Result: KuOI

Settings
# exclusive inclusive rows x rows loops node
1. 53.701 126,984.780 ↓ 20.9 4,609 1

Hash Left Join (cost=18,229.19..18,446.99 rows=220 width=54) (actual time=126,835.138..126,984.780 rows=4,609 loops=1)

  • Hash Cond: (t1.u_pkey = t3.u_codcliente)
2.          

CTE last_collected

3. 195.742 399.817 ↓ 236.4 8,746 1

HashAggregate (cost=10,711.68..10,712.05 rows=37 width=12) (actual time=391.921..399.817 rows=8,746 loops=1)

  • Group Key: cgps.u_codcliente
4. 204.075 204.075 ↑ 1.0 11,025 1

Index Scan using cgps_s_relativo_s_cnpj_cpf_data_salvo on cgps (cost=0.55..10,656.10 rows=11,116 width=12) (actual time=0.019..204.075 rows=11,025 loops=1)

  • Index Cond: (s_relativo = 'COLETA WEB'::text)
5.          

CTE coordinates

6. 227.690 126,099.567 ↓ 8,746.0 8,746 1

Nested Loop (cost=178.91..6,628.52 rows=1 width=20) (actual time=727.991..126,099.567 rows=8,746 loops=1)

7. 183.116 725.363 ↓ 236.4 8,746 1

HashAggregate (cost=0.93..1.29 rows=37 width=12) (actual time=675.165..725.363 rows=8,746 loops=1)

  • Group Key: t1_1_1.u_codcliente, t1_1_1.t_data_coleta
8. 542.247 542.247 ↓ 236.4 8,746 1

CTE Scan on last_collected t1_1_1 (cost=0.00..0.74 rows=37 width=12) (actual time=391.926..542.247 rows=8,746 loops=1)

9. 717.172 125,146.514 ↑ 1.0 1 8,746

Bitmap Heap Scan on cgps t2_1 (cost=177.99..179.10 rows=1 width=28) (actual time=14.306..14.309 rows=1 loops=8,746)

  • Recheck Cond: ((u_codcliente = t1_1_1.u_codcliente) AND (s_relativo = 'COLETA WEB'::text))
  • Filter: (t1_1_1.t_data_coleta = t_data_coleta)
  • Rows Removed by Filter: 0
  • Heap Blocks: exact=9553
10. 787.140 124,429.342 ↓ 0.0 0 8,746

BitmapAnd (cost=177.99..177.99 rows=1 width=0) (actual time=14.227..14.227 rows=0 loops=8,746)

11. 2,238.976 2,238.976 ↑ 1.6 125 8,746

Bitmap Index Scan on cgps_u_codcliente (cost=0.00..3.04 rows=201 width=0) (actual time=0.256..0.256 rows=125 loops=8,746)

  • Index Cond: (u_codcliente = t1_1_1.u_codcliente)
12. 121,403.226 121,403.226 ↑ 1.0 11,025 8,746

Bitmap Index Scan on cgps_s_relativo_s_cnpj_cpf_data_salvo (cost=0.00..171.92 rows=11,116 width=0) (actual time=13.881..13.881 rows=11,025 loops=8,746)

  • Index Cond: (s_relativo = 'COLETA WEB'::text)
13.          

Initplan (forHash Left Join)

14. 0.000 1.619 ↑ 2.0 1 1

Subquery Scan on view_t_configuration_raio (cost=510.32..510.36 rows=2 width=32) (actual time=1.616..1.619 rows=1 loops=1)

15.          

Initplan (forSubquery Scan)

16. 0.028 0.028 ↑ 1.0 1 1

Result (cost=0.00..0.26 rows=1 width=0) (actual time=0.028..0.028 rows=1 loops=1)

17. 0.004 1.616 ↑ 2.0 1 1

Unique (cost=510.06..510.08 rows=2 width=32) (actual time=1.614..1.616 rows=1 loops=1)

18. 0.017 1.612 ↑ 2.0 1 1

Sort (cost=510.06..510.06 rows=2 width=32) (actual time=1.611..1.612 rows=1 loops=1)

  • Sort Key: v1.u_codmaq, v1.s_valor, v1.s_parametro
  • Sort Method: quicksort Memory: 25kB
19. 0.005 1.595 ↑ 2.0 1 1

Append (cost=0.00..510.05 rows=2 width=32) (actual time=0.629..1.595 rows=1 loops=1)

20. 0.773 0.773 ↑ 1.0 1 1

Seq Scan on t_configuration v1 (cost=0.00..177.12 rows=1 width=32) (actual time=0.627..0.773 rows=1 loops=1)

  • Filter: ((u_codmaq <> 0) AND (s_parametro = 'radius_limit_area'::text) AND (u_codmaq = $4))
  • Rows Removed by Filter: 4520
21. 0.008 0.817 ↓ 0.0 0 1

Nested Loop Anti Join (cost=0.14..332.91 rows=1 width=32) (actual time=0.817..0.817 rows=0 loops=1)

  • Join Filter: (t2_1_1.u_codmaq = v1_1.u_codmaq)
22. 0.008 0.404 ↑ 1.0 1 1

Nested Loop (cost=0.14..167.09 rows=1 width=32) (actual time=0.213..0.404 rows=1 loops=1)

23. 0.388 0.388 ↑ 1.0 1 1

Seq Scan on t_configuration t1_2 (cost=0.00..165.81 rows=1 width=28) (actual time=0.200..0.388 rows=1 loops=1)

  • Filter: ((u_codmaq = 0) AND (s_parametro = 'radius_limit_area'::text))
  • Rows Removed by Filter: 4520
24. 0.008 0.008 ↑ 1.0 1 1

Index Only Scan using t_vendedor_u_codmaq_uni on t_vendedor v1_1 (cost=0.14..1.26 rows=1 width=4) (actual time=0.007..0.008 rows=1 loops=1)

  • Index Cond: (u_codmaq = $4)
  • Heap Fetches: 0
25. 0.405 0.405 ↑ 1.0 1 1

Seq Scan on t_configuration t2_1_1 (cost=0.00..165.81 rows=1 width=4) (actual time=0.405..0.405 rows=1 loops=1)

  • Filter: ((u_codmaq = $4) AND (s_parametro = 'radius_limit_area'::text))
  • Rows Removed by Filter: 3603
26. 50.640 568.750 ↓ 20.9 4,609 1

Nested Loop (cost=378.22..594.09 rows=220 width=38) (actual time=472.778..568.750 rows=4,609 loops=1)

27. 45.902 476.629 ↓ 20.9 4,609 1

HashAggregate (cost=377.94..380.14 rows=220 width=37) (actual time=472.768..476.629 rows=4,609 loops=1)

  • Group Key: t1_1.s_codvendedor, t1_1.s_codcliente, t1_1.d_limite_credito, t1_1.d_descpetalao, t1_1.u_prazoentrega, t2.u_codmaq, t3_1.u_pkey
28.          

Initplan (forHashAggregate)

29. 0.253 0.253 ↑ 1.0 1 1

Seq Scan on t_vendedor (cost=0.00..21.32 rows=1 width=4) (actual time=0.148..0.253 rows=1 loops=1)

  • Filter: (u_codmaq = syncon.func_ler_variavel('u_codmaq'::text))
  • Rows Removed by Filter: 65
30. 155.517 430.474 ↓ 20.9 4,609 1

Nested Loop (cost=0.71..352.77 rows=220 width=37) (actual time=0.315..430.474 rows=4,609 loops=1)

31. 4.719 58.334 ↓ 20.9 4,609 1

Nested Loop (cost=0.43..141.02 rows=220 width=33) (actual time=0.305..58.334 rows=4,609 loops=1)

32. 0.270 0.270 ↑ 1.0 1 1

Index Scan using t_vendedor_s_codigo_uni on t_vendedor t2 (cost=0.14..2.36 rows=1 width=8) (actual time=0.267..0.270 rows=1 loops=1)

  • Index Cond: (s_codigo = $6)
  • Filter: (u_bloqueado = 0)
33. 53.345 53.345 ↓ 20.9 4,609 1

Index Scan using t_orgven_vendedor_cliente_s_codvendedor on t_orgven_vendedor_cliente t1_1 (cost=0.29..136.45 rows=220 width=29) (actual time=0.034..53.345 rows=4,609 loops=1)

  • Index Cond: (s_codvendedor = $6)
34. 216.623 216.623 ↑ 1.0 1 4,609

Index Scan using t_cliente_pkey on t_cliente t3_1 (cost=0.28..0.95 rows=1 width=9) (actual time=0.033..0.047 rows=1 loops=4,609)

  • Index Cond: (s_codcliente = t1_1.s_codcliente)
35. 41.481 41.481 ↑ 1.0 1 4,609

Index Scan using t_cliente_pkey on t_cliente t1 (cost=0.28..0.95 rows=1 width=38) (actual time=0.006..0.009 rows=1 loops=4,609)

  • Index Cond: (s_codcliente = t1_1.s_codcliente)
36. 168.755 126,360.710 ↓ 8,746.0 8,746 1

Hash (cost=0.02..0.02 rows=1 width=20) (actual time=126,360.710..126,360.710 rows=8,746 loops=1)

  • Buckets: 1024 Batches: 1 Memory Usage: 438kB
37. 126,191.955 126,191.955 ↓ 8,746.0 8,746 1

CTE Scan on coordinates t3 (cost=0.00..0.02 rows=1 width=20) (actual time=727.995..126,191.955 rows=8,746 loops=1)