explain.depesz.com

PostgreSQL's explain analyze made readable

Result: uzjG

Settings
# exclusive inclusive rows x rows loops node
1. 0.170 35,032.082 ↓ 618.0 618 1

Group (cost=848.55..848.56 rows=1 width=8) (actual time=35,031.869..35,032.082 rows=618 loops=1)

  • Group Key: v.id_veiculo, g.id_geometria
2. 0.957 35,031.912 ↓ 618.0 618 1

Sort (cost=848.55..848.55 rows=1 width=8) (actual time=35,031.866..35,031.912 rows=618 loops=1)

  • Sort Key: v.id_veiculo, g.id_geometria
  • Sort Method: quicksort Memory: 53kB
3. 55.471 35,030.955 ↓ 618.0 618 1

Nested Loop Semi Join (cost=153.45..848.54 rows=1 width=8) (actual time=71.991..35,030.955 rows=618 loops=1)

4. 12.410 3,236.780 ↓ 11,842.8 59,214 1

Nested Loop (cost=153.45..801.49 rows=5 width=107) (actual time=0.965..3,236.780 rows=59,214 loops=1)

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

  • Index Cond: ((id_camada)::integer = 9,994)
  • Filter: ((valor1)::text ~~ '%qui%'::text)
  • Rows Removed by Filter: 441
6. 94.146 3,222.903 ↓ 55.6 278 213

Nested Loop (cost=152.89..275.83 rows=5 width=8) (actual time=0.192..15.131 rows=278 loops=213)

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

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

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

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

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

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

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

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

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

12. 0.102 0.102 ↑ 1.0 813 1

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

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

  • Index Cond: ((id_modelo_tipo)::integer = 23)
14. 3,026.730 3,026.730 ↑ 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.033..0.035 rows=1 loops=86,478)

  • Index Cond: (((id_veiculo)::integer = (fv.id_veiculo)::integer) AND ('["2020-10-01 02:00:00-03","2020-10-02 02:00:00-03"]'::tstzrange && tstzrange(dt_inicio_terminal, dt_fim_terminal, '[]'::text)))
  • Filter: ((id_filial)::integer = 55)
15. 3,168.462 31,738.704 ↓ 0.0 0 59,214

Append (cost=0.00..9.37 rows=4 width=36) (actual time=0.536..0.536 rows=0 loops=59,214)

16. 0.000 0.000 ↓ 0.0 0 59,214

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

  • Filter: ((dt_operacao >= '2020-10-01 02:00:00-03'::timestamp with time zone) AND (dt_operacao <= '2020-10-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,256.770 3,256.770 ↓ 0.0 0 59,214

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=59,214)

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

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.009..0.009 rows=0 loops=58,596)

  • Index Cond: ((geom && st_envelope(g.geom)) AND (dt_operacao >= '2020-10-01 02:00:00-03'::timestamp with time zone) AND (dt_operacao <= '2020-10-02 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)))
19. 24,786.108 24,786.108 ↓ 0.0 0 58,596

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.423..0.423 rows=0 loops=58,596)

  • Index Cond: (geom && st_envelope(g.geom))
  • Filter: ((dt_operacao >= '2020-10-01 02:00:00-03'::timestamp with time zone) AND (dt_operacao <= '2020-10-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.247 ms
Execution time : 35,032.701 ms