explain.depesz.com

PostgreSQL's explain analyze made readable

Result: aDbv

Settings
# exclusive inclusive rows x rows loops node
1. 0.117 10,150.550 ↓ 785.0 785 1

Group (cost=807.16..807.17 rows=1 width=8) (actual time=10,150.400..10,150.550 rows=785 loops=1)

  • Group Key: v.id_veiculo, g.id_geometria
2. 0.920 10,150.433 ↓ 786.0 786 1

Sort (cost=807.16..807.17 rows=1 width=8) (actual time=10,150.398..10,150.433 rows=786 loops=1)

  • Sort Key: v.id_veiculo, g.id_geometria
  • Sort Method: quicksort Memory: 61kB
3. 54.287 10,149.513 ↓ 786.0 786 1

Nested Loop Semi Join (cost=149.31..807.15 rows=1 width=8) (actual time=275.352..10,149.513 rows=786 loops=1)

4. 11.558 2,073.436 ↓ 14,994.0 74,970 1

Nested Loop (cost=149.31..760.11 rows=5 width=103) (actual time=42.774..2,073.436 rows=74,970 loops=1)

5. 10.598 10.598 ↓ 210.0 210 1

Index Scan using ind_tb_geometria_idcamada_valor1 on tb_geometria g (cost=0.56..476.14 rows=1 width=95) (actual time=5.344..10.598 rows=210 loops=1)

  • Index Cond: ((id_camada)::integer = 8,343)
  • Filter: ((valor1)::text ~~ '%qua%'::text)
  • Rows Removed by Filter: 441
6. 64.680 2,051.280 ↓ 71.4 357 210

Nested Loop (cost=148.75..283.92 rows=5 width=8) (actual time=0.388..9.768 rows=357 loops=210)

  • Join Filter: ((v.id_veiculo)::integer = (rd.id_veiculo)::integer)
7. 40.683 110.880 ↓ 45.1 406 210

Hash Join (cost=148.33..204.80 rows=9 width=12) (actual time=0.278..0.528 rows=406 loops=210)

  • Hash Cond: ((fv.id_veiculo)::integer = (v.id_veiculo)::integer)
8. 28.140 35.490 ↓ 1.0 779 210

Bitmap Heap Scan on tb_filial_veiculo fv (cost=9.76..64.23 rows=758 width=8) (actual time=0.080..0.169 rows=779 loops=210)

  • Recheck Cond: ((id_filial)::integer = 55)
  • Heap Blocks: exact=2,520
9. 7.350 7.350 ↓ 1.0 779 210

Bitmap Index Scan on pk00_tb_filial_veiculo (cost=0.00..9.57 rows=758 width=0) (actual time=0.035..0.035 rows=779 loops=210)

  • Index Cond: ((id_filial)::integer = 55)
10. 0.070 34.707 ↓ 3.2 406 1

Hash (cost=137.00..137.00 rows=126 width=4) (actual time=34.707..34.707 rows=406 loops=1)

  • Buckets: 1,024 Batches: 1 Memory Usage: 23kB
11. 4.549 34.637 ↓ 3.2 406 1

Bitmap Heap Scan on tb_veiculo v (cost=34.54..137.00 rows=126 width=4) (actual time=30.104..34.637 rows=406 loops=1)

  • Recheck Cond: ((rotulos && '{207}'::integer[]) AND ((id_modelo_tipo)::integer = 23))
  • Heap Blocks: exact=78
12. 0.008 30.088 ↓ 0.0 0 1

BitmapAnd (cost=34.54..34.54 rows=126 width=0) (actual time=30.088..30.088 rows=0 loops=1)

13. 0.068 0.068 ↑ 1.0 779 1

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

  • Index Cond: (rotulos && '{207}'::integer[])
14. 30.012 30.012 ↑ 1.0 1,667 1

Bitmap Index Scan on ix04f_tb_veiculo (cost=0.00..24.79 rows=1,667 width=0) (actual time=30.012..30.012 rows=1,667 loops=1)

  • Index Cond: ((id_modelo_tipo)::integer = 23)
15. 1,875.720 1,875.720 ↑ 1.0 1 85,260

Index Scan using ind_tb_rd_idveiculo_dtiniterm_dtfimterm on tb_rd rd (cost=0.41..8.78 rows=1 width=12) (actual time=0.021..0.022 rows=1 loops=85,260)

  • Index Cond: (((id_veiculo)::integer = (fv.id_veiculo)::integer) AND ('["2020-06-24 02:00:00-03","2020-06-25 02:00:00-03"]'::tstzrange && tstzrange(dt_inicio_terminal, dt_fim_terminal, '[]'::text)))
  • Filter: ((id_filial)::integer = 55)
16. 2,637.312 8,021.790 ↓ 0.0 0 74,970

Append (cost=0.00..9.37 rows=4 width=36) (actual time=0.107..0.107 rows=0 loops=74,970)

17. 0.000 0.000 ↓ 0.0 0 74,970

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

  • Filter: ((dt_operacao >= '2020-06-24 02:00:00-03'::timestamp with time zone) AND (dt_operacao <= '2020-06-25 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)))
18. 4,123.350 4,123.350 ↓ 0.0 0 74,970

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.055..0.055 rows=0 loops=74,970)

  • Index Cond: (((id_rd)::integer = (rd.id_rd)::integer) AND (geom && st_envelope(g.geom)))
  • Filter: ((dt_operacao >= '2020-06-24 02:00:00-03'::timestamp with time zone) AND (dt_operacao <= '2020-06-25 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
19. 890.208 890.208 ↓ 0.0 0 74,184

Index Scan using ind_tbpontoin_idrd_geom on tb_ponto_in p_2 (cost=0.41..3.16 rows=1 width=36) (actual time=0.012..0.012 rows=0 loops=74,184)

  • Index Cond: (((id_rd)::integer = (rd.id_rd)::integer) AND (geom && st_envelope(g.geom)))
  • Filter: ((dt_operacao >= '2020-06-24 02:00:00-03'::timestamp with time zone) AND (dt_operacao <= '2020-06-25 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
20. 370.920 370.920 ↓ 0.0 0 74,184

Index Scan using ind_tbpontonaorastreado_geom_dtoperacao on tb_ponto_nao_rastreado p_3 (cost=0.15..2.89 rows=1 width=36) (actual time=0.005..0.005 rows=0 loops=74,184)

  • Index Cond: ((geom && st_envelope(g.geom)) AND (dt_operacao >= '2020-06-24 02:00:00-03'::timestamp with time zone) AND (dt_operacao <= '2020-06-25 02:00:00-03'::timestamp with time zone))
  • Filter: (((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)))
Planning time : 7.652 ms
Execution time : 10,151.144 ms