explain.depesz.com

PostgreSQL's explain analyze made readable

Result: MaKr

Settings
# exclusive inclusive rows x rows loops node
1. 0.023 23,894.663 ↓ 16.0 16 1

Sort (cost=647.97..647.97 rows=1 width=39) (actual time=23,894.663..23,894.663 rows=16 loops=1)

  • Sort Key: (count(*)) DESC
  • Sort Method: quicksort Memory: 26kB
2. 0.107 23,894.640 ↓ 16.0 16 1

GroupAggregate (cost=647.93..647.96 rows=1 width=39) (actual time=23,894.510..23,894.640 rows=16 loops=1)

  • Group Key: rd.id_rd, v.cd_identificador
3. 0.805 23,894.533 ↓ 931.0 931 1

Sort (cost=647.93..647.94 rows=1 width=31) (actual time=23,894.492..23,894.533 rows=931 loops=1)

  • Sort Key: rd.id_rd, v.cd_identificador
  • Sort Method: quicksort Memory: 97kB
4. 113.738 23,893.728 ↓ 931.0 931 1

Nested Loop (cost=447.14..647.92 rows=1 width=31) (actual time=2,341.012..23,893.728 rows=931 loops=1)

  • Join Filter: CASE c.tipo_camada WHEN 'LINE'::text THEN (st_distance(g.geom, p.geom) <= '8.9932e-05'::double precision) ELSE ((g.geom ~ p.geom) AND _st_contains(g.geom, p.geom)) END
  • Rows Removed by Join Filter: 31256
5. 23.747 23,715.616 ↓ 32,187.0 32,187 1

Nested Loop (cost=446.86..644.90 rows=1 width=157) (actual time=56.728..23,715.616 rows=32,187 loops=1)

6. 24.452 23,563.121 ↓ 32,187.0 32,187 1

Hash Join (cost=446.57..642.28 rows=1 width=150) (actual time=56.709..23,563.121 rows=32,187 loops=1)

  • Hash Cond: ((p.id_rd)::integer = (rd.id_rd)::integer)
7. 8.027 23,484.720 ↓ 22.3 32,767 1

Nested Loop (cost=0.43..192.29 rows=1,469 width=130) (actual time=2.746..23,484.720 rows=32,767 loops=1)

8. 2.341 2.341 ↑ 1.0 1 1

Index Scan using pk00_tb_geometria on tb_geometria g (cost=0.43..2.85 rows=1 width=94) (actual time=2.340..2.341 rows=1 loops=1)

  • Index Cond: ((id_geometria)::integer = 13731699)
9. 5.961 23,474.352 ↓ 218.4 32,767 1

Append (cost=0.00..187.94 rows=150 width=36) (actual time=0.403..23,474.352 rows=32,767 loops=1)

10. 0.002 0.002 ↓ 0.0 0 1

Seq Scan on tb_ponto p (cost=0.00..0.00 rows=1 width=36) (actual time=0.002..0.002 rows=0 loops=1)

  • Filter: ((dt_operacao >= '2020-02-11 00:00:00-03'::timestamp with time zone) AND (dt_operacao <= '2020-02-11 23:59:00-03'::timestamp with time zone) AND (st_expand(g.geom, '8.9932e-05'::double precision) && geom))
11. 0.378 0.378 ↓ 2.0 2 1

Index Scan using ind_tbpontoin_geom_dtoperacao on tb_ponto_in p_1 (cost=0.29..2.71 rows=1 width=36) (actual time=0.374..0.378 rows=2 loops=1)

  • Index Cond: ((st_expand(g.geom, '8.9932e-05'::double precision) && geom) AND (dt_operacao >= '2020-02-11 00:00:00-03'::timestamp with time zone) AND (dt_operacao <= '2020-02-11 23:59:00-03'::timestamp with time zone))
12. 23,466.473 23,466.473 ↓ 222.9 32,765 1

Index Scan using ind_tbpontocrn_geom_dtop on tb_ponto_clust_recent p_2 (cost=0.55..182.66 rows=147 width=36) (actual time=16.663..23,466.473 rows=32,765 loops=1)

  • Index Cond: ((st_expand(g.geom, '8.9932e-05'::double precision) && geom) AND (dt_operacao >= '2020-02-11 00:00:00-03'::timestamp with time zone) AND (dt_operacao <= '2020-02-11 23:59:00-03'::timestamp with time zone))
13. 1.538 1.538 ↓ 0.0 0 1

Index Scan using ind_tbpontonaorastreado_idrd_geom on tb_ponto_nao_rastreado p_3 (cost=0.14..2.57 rows=1 width=36) (actual time=1.538..1.538 rows=0 loops=1)

  • Index Cond: (st_expand(g.geom, '8.9932e-05'::double precision) && geom)
  • Filter: ((dt_operacao >= '2020-02-11 00:00:00-03'::timestamp with time zone) AND (dt_operacao <= '2020-02-11 23:59:00-03'::timestamp with time zone))
  • Rows Removed by Filter: 574
14. 0.180 53.949 ↓ 1.0 584 1

Hash (cost=439.05..439.05 rows=567 width=24) (actual time=53.949..53.949 rows=584 loops=1)

  • Buckets: 1024 Batches: 1 Memory Usage: 43kB
15. 53.769 53.769 ↓ 1.0 584 1

Index Scan using ind_tb_rd_idfilial_dtini_idveiculo_idrd on tb_rd rd (cost=0.43..439.05 rows=567 width=24) (actual time=5.042..53.769 rows=584 loops=1)

  • Index Cond: (((id_filial)::integer = 55) AND (dt_inicio_terminal <= '2020-02-11 23:59:00-03'::timestamp with time zone) AND (dt_inicio_terminal >= '2020-02-10 00:00:00'::timestamp without time zone))
16. 128.748 128.748 ↑ 1.0 1 32,187

Index Scan using pk00_tb_veiculo on tb_veiculo v (cost=0.29..2.61 rows=1 width=15) (actual time=0.004..0.004 rows=1 loops=32,187)

  • Index Cond: ((id_veiculo)::integer = (rd.id_veiculo)::integer)
  • Filter: (rotulos && '{207}'::integer[])
17. 64.374 64.374 ↑ 1.0 1 32,187

Index Scan using pk00_tb_camada on tb_camada c (cost=0.28..2.69 rows=1 width=10) (actual time=0.002..0.002 rows=1 loops=32,187)

  • Index Cond: ((id_camada)::integer = (g.id_camada)::integer)
Planning time : 17.176 ms
Execution time : 23,895.204 ms