explain.depesz.com

PostgreSQL's explain analyze made readable

Result: XULW

Settings
# exclusive inclusive rows x rows loops node
1. 0.066 35,768.183 ↓ 460.0 460 1

Group (cost=847.91..847.92 rows=1 width=8) (actual time=35,768.100..35,768.183 rows=460 loops=1)

  • Group Key: v.id_veiculo, g.id_geometria
2. 0.675 35,768.117 ↓ 460.0 460 1

Sort (cost=847.91..847.92 rows=1 width=8) (actual time=35,768.098..35,768.117 rows=460 loops=1)

  • Sort Key: v.id_veiculo, g.id_geometria
  • Sort Method: quicksort Memory: 46kB
3. 14.061 35,767.442 ↓ 460.0 460 1

Nested Loop Semi Join (cost=153.45..847.90 rows=1 width=8) (actual time=371.763..35,767.442 rows=460 loops=1)

4. 14.066 2,854.253 ↓ 14,654.4 73,272 1

Nested Loop (cost=153.45..801.49 rows=5 width=107) (actual time=0.777..2,854.253 rows=73,272 loops=1)

5. 1.323 1.323 ↓ 213.0 213 1

Index Scan using ind_tb_geometria_idcamada_valor1 on tb_geometria g (cost=0.56..525.61 rows=1 width=99) (actual time=0.238..1.323 rows=213 loops=1)

  • Index Cond: ((id_camada)::integer = 9,994)
  • Filter: ((valor1)::text ~~ '%qui%'::text)
  • Rows Removed by Filter: 441
6. 65.178 2,838.864 ↓ 68.8 344 213

Nested Loop (cost=152.89..275.83 rows=5 width=8) (actual time=0.168..13.328 rows=344 loops=213)

  • Join Filter: ((v.id_veiculo)::integer = (rd.id_veiculo)::integer)
7. 53.497 92.868 ↓ 40.6 406 213

Hash Join (cost=152.48..199.73 rows=10 width=12) (actual time=0.069..0.436 rows=406 loops=213)

  • Hash Cond: ((fv.id_veiculo)::integer = (v.id_veiculo)::integer)
8. 38.979 38.979 ↑ 1.0 813 213

Index Only Scan using pk00_tb_filial_veiculo on tb_filial_veiculo fv (cost=0.29..45.36 rows=831 width=8) (actual time=0.010..0.183 rows=813 loops=213)

  • Index Cond: (id_filial = 55)
  • Heap Fetches: 7,242
9. 0.048 0.392 ↓ 3.1 406 1

Hash (cost=150.57..150.57 rows=130 width=4) (actual time=0.391..0.392 rows=406 loops=1)

  • Buckets: 1,024 Batches: 1 Memory Usage: 23kB
10. 0.179 0.344 ↓ 3.1 406 1

Bitmap Heap Scan on tb_veiculo v (cost=41.04..150.57 rows=130 width=4) (actual time=0.178..0.344 rows=406 loops=1)

  • Recheck Cond: ((rotulos && '{207}'::integer[]) AND ((id_modelo_tipo)::integer = 23))
  • Heap Blocks: exact=94
11. 0.006 0.165 ↓ 0.0 0 1

BitmapAnd (cost=41.04..41.04 rows=130 width=0) (actual time=0.165..0.165 rows=0 loops=1)

12. 0.068 0.068 ↑ 1.0 813 1

Bitmap Index Scan on tb_veiculo_rotulos_idx (cost=0.00..9.70 rows=813 width=0) (actual time=0.068..0.068 rows=813 loops=1)

  • Index Cond: (rotulos && '{207}'::integer[])
13. 0.091 0.091 ↓ 1.0 1,705 1

Bitmap Index Scan on ix04f_tb_veiculo (cost=0.00..31.03 rows=1,699 width=0) (actual time=0.090..0.091 rows=1,705 loops=1)

  • Index Cond: ((id_modelo_tipo)::integer = 23)
14. 2,680.818 2,680.818 ↑ 1.0 1 86,478

Index Scan using ind_tb_rd_idveiculo_dtiniterm_dtfimterm on tb_rd rd (cost=0.41..7.60 rows=1 width=12) (actual time=0.028..0.031 rows=1 loops=86,478)

  • Index Cond: (((id_veiculo)::integer = (fv.id_veiculo)::integer) AND ('["2020-09-01 02:00:00-03","2020-09-02 02:00:00-03"]'::tstzrange && tstzrange(dt_inicio_terminal, dt_fim_terminal, '[]'::text)))
  • Filter: ((id_filial)::integer = 55)
15. 3,535.652 32,899.128 ↓ 0.0 0 73,272

Append (cost=0.00..9.24 rows=4 width=36) (actual time=0.449..0.449 rows=0 loops=73,272)

16. 0.000 0.000 ↓ 0.0 0 73,272

Seq Scan on tb_ponto p (cost=0.00..0.00 rows=1 width=36) (actual time=0.000..0.000 rows=0 loops=73,272)

  • Filter: ((dt_operacao >= '2020-09-01 02:00:00-03'::timestamp with time zone) AND (dt_operacao <= '2020-09-02 02:00:00-03'::timestamp with time zone) AND ((rd.id_rd)::integer = (id_rd)::integer) AND (geom && st_envelope(g.geom)) AND (st_distance(geom, g.geom) <= '0.0005'::double precision) AND _st_intersects(geom, st_envelope(g.geom)))
17. 3,223.968 3,223.968 ↓ 0.0 0 73,272

Index Scan using ind_tbpontocrn_idrd_geom on tb_ponto_clust_recent p_1 (cost=0.56..3.30 rows=1 width=36) (actual time=0.044..0.044 rows=0 loops=73,272)

  • Index Cond: (((id_rd)::integer = (rd.id_rd)::integer) AND (geom && st_envelope(g.geom)))
  • Filter: ((dt_operacao >= '2020-09-01 02:00:00-03'::timestamp with time zone) AND (dt_operacao <= '2020-09-02 02:00:00-03'::timestamp with time zone) AND (st_distance(geom, g.geom) <= '0.0005'::double precision) AND _st_intersects(geom, st_envelope(g.geom)))
  • Rows Removed by Filter: 0
18. 218.436 218.436 ↓ 0.0 0 72,812

Index Scan using ind_tbpontoin_idrd_geom on tb_ponto_in p_2 (cost=0.29..3.03 rows=1 width=36) (actual time=0.003..0.003 rows=0 loops=72,812)

  • Index Cond: (((id_rd)::integer = (rd.id_rd)::integer) AND (geom && st_envelope(g.geom)))
  • Filter: ((dt_operacao >= '2020-09-01 02:00:00-03'::timestamp with time zone) AND (dt_operacao <= '2020-09-02 02:00:00-03'::timestamp with time zone) AND (st_distance(geom, g.geom) <= '0.0005'::double precision) AND _st_intersects(geom, st_envelope(g.geom)))
19. 25,921.072 25,921.072 ↓ 0.0 0 72,812

Index Scan using ind_tbpontonaorastreado_idrd_geom on tb_ponto_nao_rastreado p_3 (cost=0.15..2.89 rows=1 width=36) (actual time=0.356..0.356 rows=0 loops=72,812)

  • Index Cond: (geom && st_envelope(g.geom))
  • Filter: ((dt_operacao >= '2020-09-01 02:00:00-03'::timestamp with time zone) AND (dt_operacao <= '2020-09-02 02:00:00-03'::timestamp with time zone) AND ((rd.id_rd)::integer = (id_rd)::integer) AND (st_distance(geom, g.geom) <= '0.0005'::double precision) AND _st_intersects(geom, st_envelope(g.geom)))
  • Rows Removed by Filter: 44
Planning time : 2.608 ms
Execution time : 35,768.658 ms