explain.depesz.com

PostgreSQL's explain analyze made readable

Result: W3Nr

Settings
# exclusive inclusive rows x rows loops node
1. 0.008 26,513.249 ↑ 7.0 2 1

Nested Loop (cost=7,713,298.88..7,713,901.00 rows=14 width=8) (actual time=26,513.020..26,513.249 rows=2 loops=1)

2. 79.156 26,513.189 ↑ 8.0 2 1

Hash Join (cost=7,713,298.74..7,713,898.17 rows=16 width=8) (actual time=26,512.964..26,513.189 rows=2 loops=1)

  • Hash Cond: (c.mlid = o.mlid)
3. 1,687.155 26,433.665 ↓ 30.0 1,105,002 1

Sort (cost=7,713,214.53..7,713,306.72 rows=36,878 width=142) (actual time=26,302.471..26,433.665 rows=1,105,002 loops=1)

  • Sort Key: c.data, c.mlid
  • Sort Method: external sort Disk: 130944kB
4. 474.356 24,746.510 ↓ 30.0 1,105,002 1

Hash Join (cost=7,704,392.06..7,710,417.24 rows=36,878 width=142) (actual time=23,784.464..24,746.510 rows=1,105,002 loops=1)

  • Hash Cond: (c.forma_pgto = mm_1.grid)
5. 227.666 24,272.065 ↓ 10.2 1,105,003 1

Unique (cost=7,704,369.46..7,708,165.70 rows=108,464 width=73) (actual time=23,784.360..24,272.065 rows=1,105,003 loops=1)

6. 2,544.764 24,044.399 ↓ 10.2 1,105,006 1

Sort (cost=7,704,369.46..7,704,640.62 rows=108,464 width=73) (actual time=23,784.359..24,044.399 rows=1,105,006 loops=1)

  • Sort Key: c.empresa, c.data, c.mlid, c.usuario, c.pessoa, c.turno, c.forma_pgto, c.valor_venda, c.valor_forma_pgto, ((c.total_forma_pgto + CASE WHEN ((c.valor_venda - c.total_forma_pgto) > 0::double precision) THEN (c.valor_venda - c.total_forma_pgto) ELSE 0::double precision END)), (CASE WHEN ((c.valor_venda - c.total_forma_pgto) < 0::double precision) THEN abs((c.valor_venda - c.total_forma_pgto)) ELSE
  • Sort Method: external merge Disk: 122312kB
7. 32.405 21,499.635 ↓ 10.2 1,105,006 1

Append (cost=1,035,790.06..7,690,478.65 rows=108,464 width=73) (actual time=7,993.964..21,499.635 rows=1,105,006 loops=1)

8. 435.688 10,809.045 ↓ 20.4 1,104,911 1

Subquery Scan on c (cost=1,035,790.06..3,821,597.28 rows=54,232 width=77) (actual time=7,993.964..10,809.045 rows=1,104,911 loops=1)

9.          

Initplan (forSubquery Scan)

10. 0.008 0.008 ↑ 1.0 1 1

Seq Scan on motivo_config motivo_config_2 (cost=0.00..1.26 rows=1 width=8) (actual time=0.006..0.008 rows=1 loops=1)

  • Filter: (chave = 'DINHEIRO'::text)
  • Rows Removed by Filter: 20
11. 521.321 8,931.046 ↓ 20.4 1,104,911 1

GroupAggregate (cost=1,035,788.80..1,971,932.04 rows=54,232 width=69) (actual time=7,993.948..8,931.046 rows=1,104,911 loops=1)

  • Filter: (sum(COALESCE(mp.valor, m.valor)) > 0::double precision)
  • Rows Removed by Filter: 1103
12.          

Initplan (forGroupAggregate)

13. 0.002 0.002 ↑ 1.0 1 1

Seq Scan on motivo_config mc1 (cost=0.00..1.26 rows=1 width=8) (actual time=0.001..0.002 rows=1 loops=1)

  • Filter: (chave = 'DINHEIRO'::text)
  • Rows Removed by Filter: 20
14. 2,997.122 8,409.723 ↓ 20.4 1,106,038 1

Sort (cost=1,035,787.54..1,035,923.12 rows=54,232 width=69) (actual time=7,993.899..8,409.723 rows=1,106,038 loops=1)

  • Sort Key: m.empresa, m.data, m.mlid, m.usuario, m.pessoa, m.turno, (COALESCE(mp.motivo, $20)), m.valor, (COALESCE((SubPlan 13), m.valor)), m.conta_debitar
  • Sort Method: external merge Disk: 98288kB
15. 333.416 5,412.601 ↓ 20.4 1,106,038 1

Nested Loop Left Join (cost=1.77..1,031,523.04 rows=54,232 width=69) (actual time=0.027..5,412.601 rows=1,106,038 loops=1)

16. 520.589 655.087 ↓ 20.4 1,106,011 1

Hash Join (cost=1.34..60,091.80 rows=54,232 width=53) (actual time=0.014..655.087 rows=1,106,011 loops=1)

  • Hash Cond: (m.motivo = mc.motivo)
17. 134.491 134.491 ↓ 1.0 1,509,709 1

Seq Scan on movto m (cost=0.00..52,069.76 rows=1,495,676 width=61) (actual time=0.001..134.491 rows=1,509,709 loops=1)

18. 0.000 0.007 ↑ 2.0 1 1

Hash (cost=1.31..1.31 rows=2 width=8) (actual time=0.007..0.007 rows=1 loops=1)

  • Buckets: 1024 Batches: 1 Memory Usage: 1kB
19. 0.007 0.007 ↑ 2.0 1 1

Seq Scan on motivo_config mc (cost=0.00..1.31 rows=2 width=8) (actual time=0.005..0.007 rows=1 loops=1)

  • Filter: ((chave = 'VENDA'::text) OR (chave = 'VENDA_TRR'::text))
  • Rows Removed by Filter: 20
20. 2,212.022 2,212.022 ↓ 0.0 0 1,106,011

Index Scan using movto_mlid_ix on movto mp (cost=0.43..0.69 rows=1 width=28) (actual time=0.002..0.002 rows=0 loops=1,106,011)

  • Index Cond: (m.mlid = mlid)
  • Filter: (conta_creditar = m.conta_debitar)
  • Rows Removed by Filter: 1
21.          

SubPlan (forNested Loop Left Join)

22. 0.000 2,212.076 ↑ 1.0 1 1,106,038

Aggregate (cost=17.21..17.22 rows=1 width=8) (actual time=0.002..0.002 rows=1 loops=1,106,038)

23. 0.000 2,212.076 ↓ 0.0 0 1,106,038

Nested Loop Left Join (cost=0.70..17.20 rows=1 width=8) (actual time=0.002..0.002 rows=0 loops=1,106,038)

  • Filter: CASE WHEN (tv.grid IS NULL) THEN true ELSE false END
24. 899.976 2,212.076 ↓ 0.0 0 1,106,038

Nested Loop (cost=0.57..16.98 rows=1 width=16) (actual time=0.002..0.002 rows=0 loops=1,106,038)

25. 1,106.038 1,106.038 ↓ 0.0 0 1,106,038

Index Scan using movto_mlid_ix on movto m1 (cost=0.43..8.81 rows=1 width=16) (actual time=0.001..0.001 rows=0 loops=1,106,038)

  • Index Cond: (mlid = m.mlid)
  • Filter: (conta_creditar = m.conta_debitar)
  • Rows Removed by Filter: 1
26. 206.062 206.062 ↑ 1.0 1 206,062

Index Scan using motivo_movto_pkey on motivo_movto mm_2 (cost=0.14..8.16 rows=1 width=16) (actual time=0.001..0.001 rows=1 loops=206,062)

  • Index Cond: (grid = m1.motivo)
27. 0.000 0.000 ↓ 0.0 0 206,062

Index Scan using tipo_vale_pkey on tipo_vale tv (cost=0.13..0.21 rows=1 width=8) (actual time=0.000..0.000 rows=0 loops=206,062)

  • Index Cond: (grid = mm_2.tipo_vale)
  • Filter: (tipo = 1)
28.          

SubPlan (forSubquery Scan)

29. 0.095 1.045 ↑ 1.0 1 95

Aggregate (cost=17.02..17.03 rows=1 width=8) (actual time=0.011..0.011 rows=1 loops=95)

30. 0.279 0.950 ↑ 1.0 1 95

Nested Loop (cost=8.97..17.02 rows=1 width=8) (actual time=0.009..0.010 rows=1 loops=95)

31. 0.285 0.475 ↓ 2.0 2 95

HashAggregate (cost=8.82..8.83 rows=1 width=8) (actual time=0.005..0.005 rows=2 loops=95)

32. 0.190 0.190 ↑ 3.5 2 95

Index Scan using movto_mlid_ix on movto x_2 (cost=0.43..8.79 rows=7 width=8) (actual time=0.002..0.002 rows=2 loops=95)

  • Index Cond: (mlid = c.mlid)
33. 0.196 0.196 ↑ 1.0 1 196

Index Scan using motivo_movto_pkey on motivo_movto xx_2 (cost=0.14..8.16 rows=1 width=8) (actual time=0.001..0.001 rows=1 loops=196)

  • Index Cond: (grid = x_2.motivo)
  • Filter: forma_pgto
  • Rows Removed by Filter: 0
34. 205.894 1,441.258 ↑ 1.0 1 205,894

Aggregate (cost=17.02..17.03 rows=1 width=8) (actual time=0.007..0.007 rows=1 loops=205,894)

35. 0.000 1,235.364 ↑ 1.0 1 205,894

Nested Loop (cost=8.97..17.02 rows=1 width=8) (actual time=0.006..0.006 rows=1 loops=205,894)

36. 411.788 823.576 ↓ 2.0 2 205,894

HashAggregate (cost=8.82..8.83 rows=1 width=8) (actual time=0.004..0.004 rows=2 loops=205,894)

37. 411.788 411.788 ↑ 3.5 2 205,894

Index Scan using movto_mlid_ix on movto x_3 (cost=0.43..8.79 rows=7 width=8) (actual time=0.002..0.002 rows=2 loops=205,894)

  • Index Cond: (mlid = c.mlid)
38. 412.898 412.898 ↓ 0.0 0 412,898

Index Scan using motivo_movto_pkey on motivo_movto xx_3 (cost=0.14..8.16 rows=1 width=8) (actual time=0.001..0.001 rows=0 loops=412,898)

  • Index Cond: (grid = x_3.motivo)
  • Filter: forma_pgto
  • Rows Removed by Filter: 1
39. 0.461 10,658.185 ↑ 570.9 95 1

Subquery Scan on c_1 (cost=580,320.00..3,867,796.73 rows=54,232 width=69) (actual time=7,475.186..10,658.185 rows=95 loops=1)

40.          

Initplan (forSubquery Scan)

41. 0.004 0.004 ↑ 1.0 1 1

Seq Scan on motivo_config (cost=0.00..1.26 rows=1 width=8) (actual time=0.003..0.004 rows=1 loops=1)

  • Filter: (chave = 'DINHEIRO'::text)
  • Rows Removed by Filter: 20
42. 0.003 0.003 ↑ 1.0 1 1

Seq Scan on motivo_config motivo_config_1 (cost=0.00..1.26 rows=1 width=8) (actual time=0.001..0.003 rows=1 loops=1)

  • Filter: (chave = 'DINHEIRO'::text)
  • Rows Removed by Filter: 20
43. 577.129 10,656.387 ↑ 570.9 95 1

GroupAggregate (cost=580,317.47..2,017,723.49 rows=54,232 width=69) (actual time=7,475.147..10,656.387 rows=95 loops=1)

  • Filter: (CASE WHEN ((m_1.valor - COALESCE((SubPlan 7), m_1.valor)) > 0::double precision) THEN (m_1.valor - COALESCE((SubPlan 8), m_1.valor)) ELSE 0::double precision END > 0::double precision)
  • Rows Removed by Filter: 1105919
44.          

Initplan (forGroupAggregate)

45. 0.003 0.003 ↑ 1.0 1 1

Seq Scan on motivo_config mc2 (cost=0.00..1.26 rows=1 width=8) (actual time=0.002..0.003 rows=1 loops=1)

  • Filter: (chave = 'DINHEIRO'::text)
  • Rows Removed by Filter: 20
46. 3,262.147 7,866.942 ↓ 20.4 1,106,038 1

Sort (cost=580,316.21..580,451.79 rows=54,232 width=69) (actual time=7,465.335..7,866.942 rows=1,106,038 loops=1)

  • Sort Key: m_1.empresa, m_1.data, m_1.mlid, m_1.usuario, m_1.pessoa, m_1.turno, (COALESCE(mp_1.motivo, $6)), m_1.valor, (COALESCE((SubPlan 6), m_1.valor)), m_1.conta_debitar
  • Sort Method: external merge Disk: 98288kB
47. 0.000 4,604.795 ↓ 20.4 1,106,038 1

Nested Loop Left Join (cost=1.77..576,051.72 rows=54,232 width=69) (actual time=0.036..4,604.795 rows=1,106,038 loops=1)

48. 486.019 592.106 ↓ 20.4 1,106,011 1

Hash Join (cost=1.34..60,091.80 rows=54,232 width=53) (actual time=0.020..592.106 rows=1,106,011 loops=1)

  • Hash Cond: (m_1.motivo = mc_1.motivo)
49. 106.078 106.078 ↓ 1.0 1,509,709 1

Seq Scan on movto m_1 (cost=0.00..52,069.76 rows=1,495,676 width=61) (actual time=0.002..106.078 rows=1,509,709 loops=1)

50. 0.002 0.009 ↑ 2.0 1 1

Hash (cost=1.31..1.31 rows=2 width=8) (actual time=0.009..0.009 rows=1 loops=1)

  • Buckets: 1024 Batches: 1 Memory Usage: 1kB
51. 0.007 0.007 ↑ 2.0 1 1

Seq Scan on motivo_config mc_1 (cost=0.00..1.31 rows=2 width=8) (actual time=0.005..0.007 rows=1 loops=1)

  • Filter: ((chave = 'VENDA'::text) OR (chave = 'VENDA_TRR'::text))
  • Rows Removed by Filter: 20
52. 2,212.022 2,212.022 ↓ 0.0 0 1,106,011

Index Scan using movto_mlid_ix on movto mp_1 (cost=0.43..0.69 rows=1 width=28) (actual time=0.001..0.002 rows=0 loops=1,106,011)

  • Index Cond: (m_1.mlid = mlid)
  • Filter: (conta_creditar = m_1.conta_debitar)
  • Rows Removed by Filter: 1
53.          

SubPlan (forNested Loop Left Join)

54. 1,106.038 2,212.076 ↑ 1.0 1 1,106,038

Aggregate (cost=8.81..8.82 rows=1 width=8) (actual time=0.002..0.002 rows=1 loops=1,106,038)

55. 1,106.038 1,106.038 ↓ 0.0 0 1,106,038

Index Scan using movto_mlid_ix on movto m2 (cost=0.43..8.81 rows=1 width=8) (actual time=0.001..0.001 rows=0 loops=1,106,038)

  • Index Cond: (mlid = m_1.mlid)
  • Filter: (conta_creditar = m_1.conta_debitar)
  • Rows Removed by Filter: 1
56.          

SubPlan (forGroupAggregate)

57. 0.000 2,212.028 ↑ 1.0 1 1,106,014

Aggregate (cost=8.81..8.82 rows=1 width=8) (actual time=0.002..0.002 rows=1 loops=1,106,014)

58. 2,212.028 2,212.028 ↓ 0.0 0 1,106,014

Index Scan using movto_mlid_ix on movto m2_1 (cost=0.43..8.81 rows=1 width=8) (actual time=0.001..0.002 rows=0 loops=1,106,014)

  • Index Cond: (mlid = m_1.mlid)
  • Filter: (conta_creditar = m_1.conta_debitar)
  • Rows Removed by Filter: 1
59. 0.095 0.285 ↑ 1.0 1 95

Aggregate (cost=8.81..8.82 rows=1 width=8) (actual time=0.003..0.003 rows=1 loops=95)

60. 0.190 0.190 ↑ 1.0 1 95

Index Scan using movto_mlid_ix on movto m2_2 (cost=0.43..8.81 rows=1 width=8) (actual time=0.002..0.002 rows=1 loops=95)

  • Index Cond: (mlid = m_1.mlid)
  • Filter: (conta_creditar = m_1.conta_debitar)
  • Rows Removed by Filter: 1
61.          

SubPlan (forSubquery Scan)

62. 0.095 1.330 ↑ 1.0 1 95

Aggregate (cost=17.02..17.03 rows=1 width=8) (actual time=0.014..0.014 rows=1 loops=95)

63. 0.368 1.235 ↑ 1.0 1 95

Nested Loop (cost=8.97..17.02 rows=1 width=8) (actual time=0.012..0.013 rows=1 loops=95)

64. 0.285 0.475 ↓ 2.0 2 95

HashAggregate (cost=8.82..8.83 rows=1 width=8) (actual time=0.005..0.005 rows=2 loops=95)

65. 0.190 0.190 ↑ 3.5 2 95

Index Scan using movto_mlid_ix on movto x (cost=0.43..8.79 rows=7 width=8) (actual time=0.001..0.002 rows=2 loops=95)

  • Index Cond: (mlid = c_1.mlid)
66. 0.392 0.392 ↑ 1.0 1 196

Index Scan using motivo_movto_pkey on motivo_movto xx (cost=0.14..8.16 rows=1 width=8) (actual time=0.002..0.002 rows=1 loops=196)

  • Index Cond: (grid = x.motivo)
  • Filter: forma_pgto
  • Rows Removed by Filter: 0
67. 0.000 0.000 ↓ 0.0 0

Aggregate (cost=17.02..17.03 rows=1 width=8) (never executed)

68. 0.000 0.000 ↓ 0.0 0

Nested Loop (cost=8.97..17.02 rows=1 width=8) (never executed)

69. 0.000 0.000 ↓ 0.0 0

HashAggregate (cost=8.82..8.83 rows=1 width=8) (never executed)

70. 0.000 0.000 ↓ 0.0 0

Index Scan using movto_mlid_ix on movto x_1 (cost=0.43..8.79 rows=7 width=8) (never executed)

  • Index Cond: (mlid = c_1.mlid)
71. 0.000 0.000 ↓ 0.0 0

Index Scan using motivo_movto_pkey on motivo_movto xx_1 (cost=0.14..8.16 rows=1 width=8) (never executed)

  • Index Cond: (grid = x_1.motivo)
  • Filter: forma_pgto
72. 0.006 0.089 ↑ 1.0 68 1

Hash (cost=21.75..21.75 rows=68 width=8) (actual time=0.089..0.089 rows=68 loops=1)

  • Buckets: 1024 Batches: 1 Memory Usage: 3kB
73. 0.083 0.083 ↑ 1.0 68 1

Seq Scan on motivo_movto mm_1 (cost=0.00..21.75 rows=68 width=8) (actual time=0.009..0.083 rows=68 loops=1)

  • Filter: forma_pgto
  • Rows Removed by Filter: 107
74. 0.002 0.368 ↓ 2.0 2 1

Hash (cost=84.20..84.20 rows=1 width=8) (actual time=0.368..0.368 rows=2 loops=1)

  • Buckets: 1024 Batches: 1 Memory Usage: 1kB
75. 0.366 0.366 ↓ 2.0 2 1

Seq Scan on orcamento o (cost=0.00..84.20 rows=1 width=8) (actual time=0.038..0.366 rows=2 loops=1)

  • Filter: (numero = 177::bigint)
  • Rows Removed by Filter: 2482
76. 0.052 0.052 ↑ 1.0 1 2

Index Only Scan using motivo_movto_pkey on motivo_movto mm (cost=0.14..0.17 rows=1 width=8) (actual time=0.025..0.026 rows=1 loops=2)

  • Index Cond: (grid = c.forma_pgto)
  • Heap Fetches: 2