explain.depesz.com

PostgreSQL's explain analyze made readable

Result: PtC

Settings
# exclusive inclusive rows x rows loops node
1. 14.838 13,575.274 ↓ 38.6 309 1

Group (cost=209,914.67..211,574.51 rows=8 width=521) (actual time=13,558.406..13,575.274 rows=309 loops=1)

  • Group Key: dv.data_hora_ultima_atualizacao, dv.id_device, dv.cod_device, dv.cod_coordenador, dv.modo_operacao, dv.id_agendamento_device, dv.latitude, dv.longitude, agd.desc_agendamento, parque.id_parque_servico, dv.cod_tipo_device, (COALESCE(dv.id_grupo_device, '-1'::integer)), sd.id_status_device, sd.desc_status_device, ps.id_ponto_servico, (COALESCE(ps.numero_identificacao, (('*'::text || (ps.id_ponto_servico)::text))::character varying)), (COALESCE(((CASE WHEN (posicao_geografica.tipo_latitude = 'S'::bpchar) THEN '-1.0000'::numeric ELSE 1.0000 END * (((posicao_geografica.latitude_graus)::numeric + ((posicao_geografica.latitude_minutos)::numeric / 60.0000)) + (posicao_geografica.latitude_segundos / 3600.0000)))), dv.latitude)), (COALESCE(((CASE WHEN (posicao_geografica.tipo_longitude = 'O'::bpchar) THEN '-1.0000'::numeric ELSE 1.0000 END * (((posicao_geografica.longitude_graus)::numeric + ((posicao_geografica.longitude_minutos)::numeric / 60.0000)) + (posicao_geografica.longitude_segundos / 3600.0000)))), dv.longitude)), posicao_geografica.id_posicao_geografica, td.cod_tipo_device, td.desc_tipo_device, dv.data_alteracao, dv.online, ((SubPlan 1)), dv.data_hora_ultima_atualizacao, (COALESCE(date_part('epoch'::text, (now() - (dv.data_hora_ultima_atualizacao)::timestamp with time zone)), '99999'::double precision)), ((tad.data_troca_agendamento + tad.hora_troca_agendamento)), adt.desc_agendamento, dv.id_agendamento_device_default, gd.nome_grupo_device, dv.desc_device, dv.id_device_dojot, dv.templates_dojot, gran.nome_grandeza, md.valor_medicao, md.data_hora_medicao, dv.id_agendamento_device_atual, dv.data_hora_alteracao_atual, dv.data_hora_recebido, dv.modo_atual, dv.potencia_atual, dv.ultima_atualizacao_status, (CASE WHEN ((agd.id_agendamento_device IS NULL) OR (dv.id_agendamento_device_atual IS NULL) OR (agd.id_agendamento_device <> dv.id_agendamento_device_atual) OR ((agd.data_alteracao - dv.data_hora_alteracao_atual) > '00:01:00'::interval)) THEN '-1'::integer WHEN ((agd.id_agendamento_device IS NOT NULL) AND (dv.id_agendamento_device_atual IS NOT NULL) AND (agd.id_agendamento_device = dv.id_agendamento_device_atual) AND ((agd.data_alteracao - dv.data_hora_alteracao_atual) < '00:01:00'::interval) AND (dv.data_hora_recebido > (now() - '7 days'::interval))) THEN 1 WHEN ((agd.id_agendamento_device IS NOT NULL) AND (dv.id_agendamento_device_atual IS NOT NULL) AND (agd.id_agendamento_device = dv.id_agendamento_device_atual) AND ((agd.data_alteracao - dv.data_hora_alteracao_atual) < '00:01:00'::interval) AND (dv.data_hora_recebido > now())) THEN 2 ELSE 0 END)
  • Buffers: shared hit=797,712 read=74,556 dirtied=29
2. 114.401 13,560.436 ↓ 956.8 7,654 1

Sort (cost=209,914.67..209,914.69 rows=8 width=521) (actual time=13,558.402..13,560.436 rows=7,654 loops=1)

  • Sort Key: dv.data_hora_ultima_atualizacao DESC, dv.id_device, dv.cod_device, dv.cod_coordenador, dv.modo_operacao, dv.id_agendamento_device, dv.latitude, dv.longitude, agd.desc_agendamento, parque.id_parque_servico, dv.cod_tipo_device, (COALESCE(dv.id_grupo_device, '-1'::integer)), sd.id_status_device, sd.desc_status_device, ps.id_ponto_servico, (COALESCE(ps.numero_identificacao, (('*'::text || (ps.id_ponto_servico)::text))::character varying)), (COALESCE(((CASE WHEN (posicao_geografica.tipo_latitude = 'S'::bpchar) THEN '-1.0000'::numeric ELSE 1.0000 END * (((posicao_geografica.latitude_graus)::numeric + ((posicao_geografica.latitude_minutos)::numeric / 60.0000)) + (posicao_geografica.latitude_segundos / 3600.0000)))), dv.latitude)), (COALESCE(((CASE WHEN (posicao_geografica.tipo_longitude = 'O'::bpchar) THEN '-1.0000'::numeric ELSE 1.0000 END * (((posicao_geografica.longitude_graus)::numeric + ((posicao_geografica.longitude_minutos)::numeric / 60.0000)) + (posicao_geografica.longitude_segundos / 3600.0000)))), dv.longitude)), posicao_geografica.id_posicao_geografica, td.cod_tipo_device, td.desc_tipo_device, dv.data_alteracao, dv.online, ((SubPlan 1)), dv.data_hora_ultima_atualizacao, (COALESCE(date_part('epoch'::text, (now() - (dv.data_hora_ultima_atualizacao)::timestamp with time zone)), '99999'::double precision)), ((tad.data_troca_agendamento + tad.hora_troca_agendamento)), adt.desc_agendamento, dv.id_agendamento_device_default, gd.nome_grupo_device, dv.desc_device, dv.id_device_dojot, dv.templates_dojot, gran.nome_grandeza, md.valor_medicao, md.data_hora_medicao, dv.id_agendamento_device_atual, dv.data_hora_alteracao_atual, dv.data_hora_recebido, dv.modo_atual, dv.potencia_atual, dv.ultima_atualizacao_status, (CASE WHEN ((agd.id_agendamento_device IS NULL) OR (dv.id_agendamento_device_atual IS NULL) OR (agd.id_agendamento_device <> dv.id_agendamento_device_atual) OR ((agd.data_alteracao - dv.data_hora_alteracao_atual) > '00:01:00'::interval)) THEN '-1'::integer WHEN ((agd.id_agendamento_device IS NOT NULL) AND (dv.id_agendamento_device_atual IS NOT NULL) AND (agd.id_agendamento_device = dv.id_agendamento_device_atual) AND ((agd.data_alteracao - dv.data_hora_alteracao_atual) < '00:01:00'::interval) AND (dv.data_hora_recebido > (now() - '7 days'::interval))) THEN 1 WHEN ((agd.id_agendamento_device IS NOT NULL) AND (dv.id_agendamento_device_atual IS NOT NULL) AND (agd.id_agendamento_device = dv.id_agendamento_device_atual) AND ((agd.data_alteracao - dv.data_hora_alteracao_atual) < '00:01:00'::interval) AND (dv.data_hora_recebido > now())) THEN 2 ELSE 0 END)
  • Sort Method: quicksort Memory: 4,044kB
  • Buffers: shared hit=797,712 read=74,556 dirtied=29
3. 74.184 13,446.035 ↓ 956.8 7,654 1

Nested Loop (cost=173,312.11..209,914.55 rows=8 width=521) (actual time=5,456.016..13,446.035 rows=7,654 loops=1)

  • Buffers: shared hit=797,712 read=74,556 dirtied=29
4. 12.450 5,886.239 ↓ 956.8 7,654 1

Hash Join (cost=173,311.68..208,192.15 rows=8 width=511) (actual time=5,455.036..5,886.239 rows=7,654 loops=1)

  • Buffers: shared hit=17,004 read=74,555 dirtied=29
5. 80.021 424.644 ↓ 956.8 7,654 1

Nested Loop (cost=1,620.88..36,493.88 rows=8 width=511) (actual time=5.877..424.644 rows=7,654 loops=1)

  • Buffers: shared hit=16,921
6. 61.632 344.623 ↓ 1,275.7 91,848 1

Merge Join (cost=1,620.88..36,491.69 rows=72 width=503) (actual time=5.87..344.623 rows=91,848 loops=1)

  • Buffers: shared hit=16,920
7. 66.289 265.159 ↓ 1,275.7 91,848 1

Nested Loop (cost=1,619.43..48,112.65 rows=72 width=499) (actual time=5.852..265.159 rows=91,848 loops=1)

  • Buffers: shared hit=16,919
8. 66.389 198.870 ↓ 1,275.7 91,848 1

Nested Loop (cost=1,619.43..1,996.14 rows=72 width=409) (actual time=5.848..198.87 rows=91,848 loops=1)

  • Buffers: shared hit=16,919
9. 0.037 0.037 ↓ 6.0 12 1

Index Only Scan using tipo_medicao_tipo_device_pkey on tipo_medicao_tipo_device tmtd (cost=0.13..12.16 rows=2 width=8) (actual time=0.006..0.037 rows=12 loops=1)

  • Heap Fetches: 12
  • Buffers: shared hit=2
10. 27.208 132.444 ↓ 212.6 7,654 12

Materialize (cost=1,619.31..1,982.99 rows=36 width=405) (actual time=0.487..11.037 rows=7,654 loops=12)

  • Buffers: shared hit=16,917
11. 10.027 105.236 ↓ 212.6 7,654 1

Nested Loop (cost=1,619.31..1,982.81 rows=36 width=405) (actual time=5.837..105.236 rows=7,654 loops=1)

  • Buffers: shared hit=16,917
12. 18.668 56.939 ↓ 212.6 7,654 1

Nested Loop (cost=1,619.16..1,976.05 rows=36 width=315) (actual time=5.828..56.939 rows=7,654 loops=1)

  • Buffers: shared hit=1,609
13. 8.570 38.271 ↓ 212.6 7,654 1

Hash Join (cost=1,618.87..1,944.89 rows=36 width=251) (actual time=5.824..38.271 rows=7,654 loops=1)

  • Buffers: shared hit=1,609
14. 6.290 29.689 ↓ 1.1 7,654 1

Hash Join (cost=1,615.29..1,913.64 rows=7,285 width=255) (actual time=5.806..29.689 rows=7,654 loops=1)

  • Buffers: shared hit=1,606
15. 14.383 19.458 ↓ 1.1 7,654 1

Hash Join (cost=574.43..845.45 rows=7,285 width=241) (actual time=1.852..19.458 rows=7,654 loops=1)

  • Buffers: shared hit=687
16. 3.237 3.237 ↓ 1.0 7,459 1

Seq Scan on alerta_device ad (cost=0..170.85 rows=7,285 width=8) (actual time=0.005..3.237 rows=7,459 loops=1)

  • Buffers: shared hit=98
17. 0.195 1.838 ↑ 12.1 309 1

Hash (cost=527.51..527.51 rows=3,754 width=241) (actual time=1.838..1.838 rows=309 loops=1)

  • Buffers: shared hit=589
18. 0.140 1.643 ↑ 12.1 309 1

Hash Join (cost=32.71..527.51 rows=3,754 width=241) (actual time=0.039..1.643 rows=309 loops=1)

  • Buffers: shared hit=589
19. 0.217 1.501 ↑ 12.1 309 1

Nested Loop (cost=4.48..485.2 rows=3,754 width=173) (actual time=0.032..1.501 rows=309 loops=1)

  • Buffers: shared hit=588
20. 0.002 0.002 ↓ 2.0 2 1

Seq Scan on tipo_device td (cost=0..1.01 rows=1 width=8) (actual time=0.002..0.002 rows=2 loops=1)

  • Buffers: shared hit=1
21. 0.318 1.282 ↑ 12.1 309 2

Hash Join (cost=4.48..437.26 rows=3,754 width=165) (actual time=0.015..0.641 rows=309 loops=2)

  • Buffers: shared hit=587
22. 0.460 0.960 ↑ 12.1 309 2

Hash Join (cost=3.42..384.58 rows=3,754 width=155) (actual time=0.011..0.48 rows=309 loops=2)

  • Buffers: shared hit=586
23. 0.490 0.490 ↑ 12.1 309 2

Seq Scan on device dv (cost=0..329.54 rows=3,754 width=151) (actual time=0.004..0.245 rows=309 loops=2)

  • Buffers: shared hit=584
24. 0.003 0.010 ↑ 63.0 1 1

Hash (cost=2.63..2.63 rows=63 width=11) (actual time=0.01..0.01 rows=1 loops=1)

  • Buffers: shared hit=2
25. 0.007 0.007 ↑ 63.0 1 1

Seq Scan on coordenador coord (cost=0..2.63 rows=63 width=11) (actual time=0.007..0.007 rows=1 loops=1)

  • Buffers: shared hit=2
26. 0.003 0.004 ↓ 1.3 4 1

Hash (cost=1.03..1.03 rows=3 width=14) (actual time=0.004..0.004 rows=4 loops=1)

  • Buffers: shared hit=1
27. 0.001 0.001 ↓ 1.3 4 1

Seq Scan on status_device sd (cost=0..1.03 rows=3 width=14) (actual time=0.001..0.001 rows=4 loops=1)

  • Buffers: shared hit=1
28. 0.001 0.002 ↑ 810.0 1 1

Hash (cost=18.1..18.1 rows=810 width=72) (actual time=0.002..0.002 rows=1 loops=1)

  • Buffers: shared hit=1
29. 0.001 0.001 ↑ 810.0 1 1

Seq Scan on grupo_device gd (cost=0..18.1 rows=810 width=72) (actual time=0.001..0.001 rows=1 loops=1)

  • Buffers: shared hit=1
30. 1.454 3.941 ↓ 1.0 5,421 1

Hash (cost=973.16..973.16 rows=5,416 width=18) (actual time=3.941..3.941 rows=5,421 loops=1)

  • Buffers: shared hit=919
31. 2.487 2.487 ↓ 1.0 5,421 1

Seq Scan on ponto_servico ps (cost=0..973.16 rows=5,416 width=18) (actual time=0.002..2.487 rows=5,421 loops=1)

  • Buffers: shared hit=919
32. 0.002 0.012 ↑ 1.0 1 1

Hash (cost=3.56..3.56 rows=1 width=4) (actual time=0.012..0.012 rows=1 loops=1)

  • Buffers: shared hit=3
33. 0.010 0.010 ↑ 1.0 1 1

Seq Scan on parque_servico parque (cost=0..3.56 rows=1 width=4) (actual time=0.006..0.01 rows=1 loops=1)

  • Filter: (parque.id_parque_servico = 6)
  • Buffers: shared hit=3
34. 0.000 0.000 ↓ 0.0 0 7,654

Index Scan using pk_posicao_geografica on posicao_geografica posicao_geografica (cost=0.29..0.86 rows=1 width=68) (actual time=0..0 rows=0 loops=7,654)

  • Index Cond: (ps.id_posicao_geografica = posicao_geografica.id_posicao_geografica)
35. 38.270 38.270 ↑ 1.0 1 7,654

Index Scan using agendamento_device_pkey on agendamento_device agd (cost=0.15..0.18 rows=1 width=90) (actual time=0.004..0.005 rows=1 loops=7,654)

  • Index Cond: (agd.id_agendamento_device = dv.id_agendamento_device)
  • Buffers: shared hit=15,308
36. 0.000 0.000 ↓ 0.0 0 91,848

Materialize (cost=0..46,115.43 rows=1 width=94) (actual time=0..0 rows=0 loops=91,848)

37. 0.000 0.001 ↓ 0.0 0 1

Nested Loop (cost=0..46,115.43 rows=1 width=94) (actual time=0.001..0.001 rows=0 loops=1)

38. 0.001 0.001 ↓ 0.0 0 1

Seq Scan on troca_agendamento_device tad (cost=0..46,094.63 rows=1 width=20) (actual time=0.001..0.001 rows=0 loops=1)

  • Filter: ((tad.executado = 0) AND (tad.id_troca_agendamento = (SubPlan 2)))
39.          

SubPlan (for Seq Scan)

40. 0.000 0.000 ↓ 0.0 0 0

Limit (cost=31.76..31.77 rows=1 width=16) (never executed)

41. 0.000 0.000 ↓ 0.0 0 0

Sort (cost=31.76..31.77 rows=1 width=16) (never executed)

  • Sort Key: ((tad1.data_troca_agendamento + tad1.hora_troca_agendamento))
42. 0.000 0.000 ↓ 0.0 0 0

Seq Scan on troca_agendamento_device tad1 (cost=0..31.75 rows=1 width=16) (never executed)

  • Filter: ((tad1.id_device = tad.id_device) AND (tad1.executado = 0))
43. 0.000 0.000 ↓ 0.0 0 0

Seq Scan on agendamento_device adt (cost=0..14.8 rows=480 width=82) (never executed)

44. 17.826 17.832 ↓ 8,419.5 84,195 1

Sort (cost=1.27..1.29 rows=10 width=8) (actual time=0.014..17.832 rows=84,195 loops=1)

  • Sort Key: tmd.id_tipo_medicao_device
  • Sort Method: quicksort Memory: 25kB
  • Buffers: shared hit=1
45. 0.006 0.006 ↓ 1.2 12 1

Seq Scan on tipo_medicao_device tmd (cost=0..1.1 rows=10 width=8) (actual time=0.003..0.006 rows=12 loops=1)

  • Buffers: shared hit=1
46. 0.000 0.000 ↑ 1.0 1 91,848

Materialize (cost=0..1.12 rows=1 width=16) (actual time=0..0 rows=1 loops=91,848)

  • Buffers: shared hit=1
47. 0.005 0.005 ↑ 1.0 1 1

Seq Scan on grandeza gran (cost=0..1.11 rows=1 width=16) (actual time=0.004..0.005 rows=1 loops=1)

  • Filter: ((gran.nome_grandeza)::text = 'Corrente'::text)
  • Buffers: shared hit=1
48. 1.061 5,449.145 ↑ 1.0 3,708 1

Hash (cost=171,635.17..171,635.17 rows=3,708 width=12) (actual time=5,449.145..5,449.145 rows=3,708 loops=1)

  • Buffers: shared hit=83 read=74,555 dirtied=29
49. 1.098 5,448.084 ↑ 1.0 3,708 1

Subquery Scan on max_medicao (cost=171,561.02..171,635.17 rows=3,708 width=12) (actual time=5,446.068..5,448.084 rows=3,708 loops=1)

  • Buffers: shared hit=83 read=74,555 dirtied=29
50. 2,347.808 5,446.986 ↑ 1.0 3,708 1

HashAggregate (cost=171,561.02..171,598.1 rows=3,708 width=12) (actual time=5,446.067..5,446.986 rows=3,708 loops=1)

  • Group Key: medicao_device.id_device, medicao_device.id_tipo_medicao_device
  • Buffers: shared hit=83 read=74,555 dirtied=29
51. 3,099.178 3,099.178 ↑ 1.0 5,535,174 1

Seq Scan on medicao_device medicao_device (cost=0..130,022.58 rows=5,538,458 width=12) (actual time=0.757..3,099.178 rows=5,535,174 loops=1)

  • Buffers: shared hit=83 read=74,555 dirtied=29
52. 53.578 53.578 ↑ 1.0 1 7,654

Index Scan using medicao_device_pkey on medicao_device md (cost=0.43..7.92 rows=1 width=18) (actual time=0.006..0.007 rows=1 loops=7,654)

  • Index Cond: (md.id_medicao_device = max_medicao.id_medicao_device)
  • Buffers: shared hit=30,616 read=1
53.          

SubPlan (for Nested Loop)

54. 15.308 7,432.034 ↓ 0.0 0 7,654

Limit (cost=0..207.27 rows=1 width=0) (actual time=0.971..0.971 rows=0 loops=7,654)

  • Buffers: shared hit=750,092
55. 7,416.726 7,416.726 ↓ 0.0 0 7,654

Seq Scan on alerta_device ad_1 (cost=0..207.27 rows=1 width=0) (actual time=0.969..0.969 rows=0 loops=7,654)

  • Filter: ((ad_1.id_device = dv.id_device) AND (ad_1.processado = 0))
  • Buffers: shared hit=750,092
Planning time : 7.039 ms
Execution time : 13,575.927 ms