explain.depesz.com

PostgreSQL's explain analyze made readable

Result: zTUH

Settings
# exclusive inclusive rows x rows loops node
1. 0.081 1,676.935 ↓ 183.0 183 1

Sort (cost=985.47..985.48 rows=1 width=39) (actual time=1,676.921..1,676.935 rows=183 loops=1)

  • Sort Key: (count(*)) DESC
  • Sort Method: quicksort Memory: 39kB
2. 1.476 1,676.854 ↓ 183.0 183 1

GroupAggregate (cost=985.44..985.46 rows=1 width=39) (actual time=1,674.568..1,676.854 rows=183 loops=1)

  • Group Key: rd.id_rd, v.cd_identificador
3. 6.445 1,675.378 ↓ 12,294.0 12,294 1

Sort (cost=985.44..985.44 rows=1 width=31) (actual time=1,674.559..1,675.378 rows=12,294 loops=1)

  • Sort Key: rd.id_rd, v.cd_identificador
  • Sort Method: quicksort Memory: 1,345kB
4. 285.898 1,668.933 ↓ 12,294.0 12,294 1

Nested Loop (cost=737.09..985.43 rows=1 width=31) (actual time=57.627..1,668.933 rows=12,294 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: 83,050
5. 127.533 1,287.691 ↓ 95,344.0 95,344 1

Nested Loop (cost=736.82..982.40 rows=1 width=162) (actual time=16.706..1,287.691 rows=95,344 loops=1)

6. 27.702 1,064.814 ↓ 95,344.0 95,344 1

Hash Join (cost=736.53..979.78 rows=1 width=155) (actual time=16.692..1,064.814 rows=95,344 loops=1)

  • Hash Cond: ((p.id_rd)::integer = (rd.id_rd)::integer)
7. 11.610 1,020.800 ↓ 52.6 97,079 1

Nested Loop (cost=0.43..238.84 rows=1,844 width=135) (actual time=0.370..1,020.800 rows=97,079 loops=1)

8. 0.016 0.016 ↑ 1.0 1 1

Index Scan using pk00_tb_geometria on tb_geometria g (cost=0.43..2.85 rows=1 width=99) (actual time=0.015..0.016 rows=1 loops=1)

  • Index Cond: ((id_geometria)::integer = 13,676,902)
9. 8.861 1,009.174 ↓ 519.1 97,079 1

Append (cost=0.00..234.11 rows=187 width=36) (actual time=0.354..1,009.174 rows=97,079 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-10-15 00:00:00-03'::timestamp with time zone) AND (dt_operacao <= '2020-10-15 23:59:00-03'::timestamp with time zone) AND (st_expand(g.geom, '8.9932e-05'::double precision) && geom))
11. 994.688 994.688 ↓ 527.2 97,010 1

Index Scan using ind_tbpontocrn_geom_dtop on tb_ponto_clust_recent p_1 (cost=0.56..227.89 rows=184 width=36) (actual time=0.334..994.688 rows=97,010 loops=1)

  • Index Cond: ((st_expand(g.geom, '8.9932e-05'::double precision) && geom) AND (dt_operacao >= '2020-10-15 00:00:00-03'::timestamp with time zone) AND (dt_operacao <= '2020-10-15 23:59:00-03'::timestamp with time zone))
12. 0.513 0.513 ↓ 69.0 69 1

Index Scan using ind_tbpontoin_geom_dtoperacao on tb_ponto_in p_2 (cost=0.29..2.71 rows=1 width=36) (actual time=0.072..0.513 rows=69 loops=1)

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

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

  • Index Cond: (st_expand(g.geom, '8.9932e-05'::double precision) && geom)
  • Filter: ((dt_operacao >= '2020-10-15 00:00:00-03'::timestamp with time zone) AND (dt_operacao <= '2020-10-15 23:59:00-03'::timestamp with time zone))
  • Rows Removed by Filter: 9,237
14. 0.248 16.312 ↓ 1.3 1,178 1

Hash (cost=724.58..724.58 rows=921 width=24) (actual time=16.312..16.312 rows=1,178 loops=1)

  • Buckets: 2,048 (originally 1024) Batches: 1 (originally 1) Memory Usage: 84kB
15. 16.064 16.064 ↓ 1.3 1,178 1

Index Scan using ind_tb_rd_idfilial_dtini_idveiculo_idrd on tb_rd rd (cost=0.43..724.58 rows=921 width=24) (actual time=1.235..16.064 rows=1,178 loops=1)

  • Index Cond: (((id_filial)::integer = 55) AND (dt_inicio_terminal <= '2020-10-15 23:59:00-03'::timestamp with time zone) AND (dt_inicio_terminal >= '2020-10-14 00:00:00'::timestamp without time zone))
16. 95.344 95.344 ↑ 1.0 1 95,344

Index Scan using pk00_tb_veiculo on tb_veiculo v (cost=0.29..2.62 rows=1 width=15) (actual time=0.001..0.001 rows=1 loops=95,344)

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

Index Scan using pk00_tb_camada on tb_camada c (cost=0.28..2.69 rows=1 width=10) (actual time=0.001..0.001 rows=1 loops=95,344)

  • Index Cond: ((id_camada)::integer = (g.id_camada)::integer)
Planning time : 2.866 ms
Execution time : 1,677.669 ms