explain.depesz.com

PostgreSQL's explain analyze made readable

Result: Db5D

Settings
# exclusive inclusive rows x rows loops node
1. 0.008 271,305.028 ↑ 7.0 2 1

Nested Loop (cost=7,691,421.41..7,692,023.53 rows=14 width=8) (actual time=271,303.533..271,305.028 rows=2 loops=1)

  • Total runtime: 274241.825 ms
2. 89.727 271,304.836 ↑ 8.0 2 1

Hash Join (cost=7,691,421.26..7,692,020.69 rows=16 width=8) (actual time=271,303.344..271,304.836 rows=2 loops=1)

  • Hash Cond: (c.mlid = o.mlid)
3. 2,551.041 271,214.743 ↓ 30.0 1,105,035 1

Sort (cost=7,691,337.05..7,691,429.24 rows=36,878 width=142) (actual time=270,618.123..271,214.743 rows=1,105,035 loops=1)

  • Sort Key: c.data, c.mlid
  • Sort Method: external merge Disk: 130920kB
4. 397.090 268,663.702 ↓ 30.0 1,105,035 1

Hash Join (cost=7,685,226.19..7,688,539.77 rows=36,878 width=142) (actual time=267,738.147..268,663.702 rows=1,105,035 loops=1)

  • Hash Cond: (c.forma_pgto = mm_1.grid)
5. 1,492.802 268,266.514 ↓ 10.2 1,105,036 1

HashAggregate (cost=7,685,203.59..7,686,288.23 rows=108,464 width=73) (actual time=267,738.030..268,266.514 rows=1,105,036 loops=1)

6. 31.634 266,773.712 ↓ 10.2 1,105,039 1

Append (cost=1,033,152.53..7,681,678.51 rows=108,464 width=73) (actual time=253,404.566..266,773.712 rows=1,105,039 loops=1)

7. 412.529 256,500.117 ↓ 20.4 1,104,944 1

Subquery Scan on c (cost=1,033,152.53..3,817,197.21 rows=54,232 width=77) (actual time=253,404.565..256,500.117 rows=1,104,944 loops=1)

8.          

Initplan (forSubquery Scan)

9. 0.005 0.005 ↑ 1.0 1 1

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

  • Filter: (chave = 'DINHEIRO'::text)
  • Rows Removed by Filter: 20
10. 1,801.002 254,027.263 ↓ 20.4 1,104,944 1

HashAggregate (cost=1,033,151.26..1,967,531.96 rows=54,232 width=69) (actual time=253,404.550..254,027.263 rows=1,104,944 loops=1)

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

Initplan (forHashAggregate)

12. 0.004 0.004 ↑ 1.0 1 1

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

  • Filter: (chave = 'DINHEIRO'::text)
  • Rows Removed by Filter: 20
13. 526.096 252,226.257 ↓ 20.4 1,106,071 1

Nested Loop Left Join (cost=1.77..1,031,523.04 rows=54,232 width=69) (actual time=0.080..252,226.257 rows=1,106,071 loops=1)

14. 408.340 93,535.707 ↓ 20.4 1,106,044 1

Hash Join (cost=1.34..60,091.80 rows=54,232 width=53) (actual time=0.037..93,535.707 rows=1,106,044 loops=1)

  • Hash Cond: (m.motivo = mc.motivo)
15. 93,127.357 93,127.357 ↓ 1.0 1,509,771 1

Seq Scan on movto m (cost=0.00..52,069.76 rows=1,495,676 width=61) (actual time=0.017..93,127.357 rows=1,509,771 loops=1)

16. 0.001 0.010 ↑ 2.0 1 1

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

  • Buckets: 1024 Batches: 1 Memory Usage: 1kB
17. 0.009 0.009 ↑ 2.0 1 1

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

  • Filter: ((chave = 'VENDA'::text) OR (chave = 'VENDA_TRR'::text))
  • Rows Removed by Filter: 20
18. 151,528.028 151,528.028 ↓ 0.0 0 1,106,044

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

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

SubPlan (forNested Loop Left Join)

20. 0.000 6,636.426 ↑ 1.0 1 1,106,071

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

21. 1,106.071 6,636.426 ↓ 0.0 0 1,106,071

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

  • Filter: CASE WHEN (tv.grid IS NULL) THEN true ELSE false END
22. 0.000 5,530.355 ↓ 0.0 0 1,106,071

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

23. 5,530.355 5,530.355 ↓ 0.0 0 1,106,071

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

  • Index Cond: (mlid = m.mlid)
  • Filter: (conta_creditar = m.conta_debitar)
  • Rows Removed by Filter: 1
24. 206.067 206.067 ↑ 1.0 1 206,067

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,067)

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

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,067)

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

SubPlan (forSubquery Scan)

27. 0.190 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)

28. 0.279 1.140 ↑ 1.0 1 95

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

29. 0.190 0.665 ↓ 2.0 2 95

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

30. 0.475 0.475 ↑ 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.004..0.005 rows=2 loops=95)

  • Index Cond: (mlid = c.mlid)
31. 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
32. 205.899 2,058.990 ↑ 1.0 1 205,899

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

33. 0.000 1,853.091 ↑ 1.0 1 205,899

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

34. 411.798 1,441.293 ↓ 2.0 2 205,899

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

35. 1,029.495 1,029.495 ↑ 3.5 2 205,899

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

  • Index Cond: (mlid = c.mlid)
36. 412.908 412.908 ↓ 0.0 0 412,908

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,908)

  • Index Cond: (grid = x_3.motivo)
  • Filter: forma_pgto
  • Rows Removed by Filter: 1
37. 0.836 10,241.961 ↑ 570.9 95 1

Subquery Scan on c_1 (cost=577,546.88..3,863,396.66 rows=54,232 width=69) (actual time=5,590.353..10,241.961 rows=95 loops=1)

38.          

Initplan (forSubquery Scan)

39. 0.006 0.006 ↑ 1.0 1 1

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

  • Filter: (chave = 'DINHEIRO'::text)
  • Rows Removed by Filter: 20
40. 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.002..0.003 rows=1 loops=1)

  • Filter: (chave = 'DINHEIRO'::text)
  • Rows Removed by Filter: 20
41. 2,401.942 10,239.026 ↑ 570.9 95 1

HashAggregate (cost=577,544.36..2,013,323.41 rows=54,232 width=69) (actual time=5,590.298..10,239.026 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: 1105952
42.          

Initplan (forHashAggregate)

43. 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
44. 633.729 4,518.560 ↓ 20.4 1,106,071 1

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

45. 444.975 566.645 ↓ 20.4 1,106,044 1

Hash Join (cost=1.34..60,091.80 rows=54,232 width=53) (actual time=0.025..566.645 rows=1,106,044 loops=1)

  • Hash Cond: (m_1.motivo = mc_1.motivo)
46. 121.661 121.661 ↓ 1.0 1,509,771 1

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

47. 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
48. 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
49. 1,106.044 1,106.044 ↓ 0.0 0 1,106,044

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

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

SubPlan (forNested Loop Left Join)

51. 1,106.071 2,212.142 ↑ 1.0 1 1,106,071

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

52. 1,106.071 1,106.071 ↓ 0.0 0 1,106,071

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,071)

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

SubPlan (forHashAggregate)

54. 0.000 3,318.141 ↑ 1.0 1 1,106,047

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

55. 3,318.141 3,318.141 ↓ 0.0 0 1,106,047

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

  • Index Cond: (mlid = m_1.mlid)
  • Filter: (conta_creditar = m_1.conta_debitar)
  • Rows Removed by Filter: 1
56. 0.190 0.380 ↑ 1.0 1 95

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

57. 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
58.          

SubPlan (forSubquery Scan)

59. 0.190 2.090 ↑ 1.0 1 95

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

60. 0.457 1.900 ↑ 1.0 1 95

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

61. 0.570 0.855 ↓ 2.0 2 95

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

62. 0.285 0.285 ↑ 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.002..0.003 rows=2 loops=95)

  • Index Cond: (mlid = c_1.mlid)
63. 0.588 0.588 ↑ 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.003 rows=1 loops=196)

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

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

65. 0.000 0.000 ↓ 0.0 0

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

66. 0.000 0.000 ↓ 0.0 0

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

67. 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)
68. 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
69. 0.009 0.098 ↑ 1.0 68 1

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

  • Buckets: 1024 Batches: 1 Memory Usage: 3kB
70. 0.089 0.089 ↑ 1.0 68 1

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

  • Filter: forma_pgto
  • Rows Removed by Filter: 107
71. 0.002 0.366 ↓ 2.0 2 1

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

  • Buckets: 1024 Batches: 1 Memory Usage: 1kB
72. 0.364 0.364 ↓ 2.0 2 1

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

  • Filter: (numero = 177::bigint)
  • Rows Removed by Filter: 2482
73. 0.184 0.184 ↑ 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.091..0.092 rows=1 loops=2)

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