explain.depesz.com

PostgreSQL's explain analyze made readable

Result: 6x5a

Settings
# exclusive inclusive rows x rows loops node
1. 0.086 11,131.245 ↓ 597.0 597 1

Group (cost=882.40..882.41 rows=1 width=8) (actual time=11,131.136..11,131.245 rows=597 loops=1)

  • Group Key: v.id_veiculo, g.id_geometria
2. 0.656 11,131.159 ↓ 597.0 597 1

Sort (cost=882.40..882.41 rows=1 width=8) (actual time=11,131.134..11,131.159 rows=597 loops=1)

  • Sort Key: v.id_veiculo, g.id_geometria
  • Sort Method: quicksort Memory: 52kB
3. 10.224 11,130.503 ↓ 597.0 597 1

Nested Loop Semi Join (cost=153.45..882.39 rows=1 width=8) (actual time=47.072..11,130.503 rows=597 loops=1)

4. 12.664 2,527.646 ↓ 12,673.5 76,041 1

Nested Loop (cost=153.45..825.18 rows=6 width=107) (actual time=0.776..2,527.646 rows=76,041 loops=1)

5. 1.156 1.156 ↓ 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.236..1.156 rows=213 loops=1)

  • Index Cond: ((id_camada)::integer = 9,994)
  • Filter: ((valor1)::text ~~ '%qui%'::text)
  • Rows Removed by Filter: 441
6. 19.596 2,513.826 ↓ 59.5 357 213

Nested Loop (cost=152.89..299.51 rows=6 width=8) (actual time=0.168..11.802 rows=357 loops=213)

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

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

  • Hash Cond: ((fv.id_veiculo)::integer = (v.id_veiculo)::integer)
8. 35.145 35.145 ↑ 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.009..0.165 rows=813 loops=213)

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

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

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

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

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

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

12. 0.065 0.065 ↑ 1.0 813 1

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

  • Index Cond: (rotulos && '{207}'::integer[])
13. 0.100 0.100 ↓ 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.100..0.100 rows=1,705 loops=1)

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

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

  • Index Cond: (((id_veiculo)::integer = (fv.id_veiculo)::integer) AND ('["2020-10-15 02:00:00-03","2020-10-16 02:00:00-03"]'::tstzrange && tstzrange(dt_inicio_terminal, dt_fim_terminal, '[]'::text)))
  • Filter: ((id_filial)::integer = 55)
15. 2,455.384 8,592.633 ↓ 0.0 0 76,041

Append (cost=0.00..9.49 rows=4 width=36) (actual time=0.113..0.113 rows=0 loops=76,041)

16. 0.000 0.000 ↓ 0.0 0 76,041

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

  • Filter: ((dt_operacao >= '2020-10-15 02:00:00-03'::timestamp with time zone) AND (dt_operacao <= '2020-10-16 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,345.804 3,345.804 ↓ 0.0 0 76,041

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=76,041)

  • Index Cond: (((id_rd)::integer = (rd.id_rd)::integer) AND (geom && st_envelope(g.geom)))
  • Filter: ((dt_operacao >= '2020-10-15 02:00:00-03'::timestamp with time zone) AND (dt_operacao <= '2020-10-16 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. 1,282.565 1,282.565 ↓ 0.0 0 75,445

Index Scan using ind_tbpontoin_geom_dtoperacao on tb_ponto_in p_2 (cost=0.41..3.16 rows=1 width=36) (actual time=0.017..0.017 rows=0 loops=75,445)

  • Index Cond: ((geom && st_envelope(g.geom)) AND (dt_operacao >= '2020-10-15 02:00:00-03'::timestamp with time zone) AND (dt_operacao <= '2020-10-16 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)))
  • Rows Removed by Filter: 1
19. 1,508.880 1,508.880 ↓ 0.0 0 75,444

Index Scan using ind_tbpontonaorastreado_geom_dtoperacao on tb_ponto_nao_rastreado p_3 (cost=0.28..3.02 rows=1 width=36) (actual time=0.020..0.020 rows=0 loops=75,444)

  • Index Cond: ((dt_operacao >= '2020-10-15 02:00:00-03'::timestamp with time zone) AND (dt_operacao <= '2020-10-16 02:00:00-03'::timestamp with time zone))
  • Filter: (((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)))
  • Rows Removed by Filter: 6
Planning time : 2.154 ms
Execution time : 11,131.680 ms