explain.depesz.com

PostgreSQL's explain analyze made readable

Result: bC8i

Settings
# exclusive inclusive rows x rows loops node
1. 0.070 2,273.790 ↓ 198.0 198 1

Sort (cost=792.99..792.99 rows=1 width=39) (actual time=2,273.780..2,273.790 rows=198 loops=1)

  • Sort Key: (count(*)) DESC
  • Sort Method: quicksort Memory: 40kB
2. 1.176 2,273.720 ↓ 198.0 198 1

GroupAggregate (cost=792.95..792.98 rows=1 width=39) (actual time=2,271.937..2,273.720 rows=198 loops=1)

  • Group Key: rd.id_rd, v.cd_identificador
3. 5.501 2,272.544 ↓ 11,520.0 11,520 1

Sort (cost=792.95..792.96 rows=1 width=31) (actual time=2,271.925..2,272.544 rows=11,520 loops=1)

  • Sort Key: rd.id_rd, v.cd_identificador
  • Sort Method: quicksort Memory: 1,285kB
4. 294.642 2,267.043 ↓ 11,520.0 11,520 1

Nested Loop (cost=573.69..792.94 rows=1 width=31) (actual time=71.888..2,267.043 rows=11,520 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: 89,566
5. 37.585 1,871.315 ↓ 101,086.0 101,086 1

Nested Loop (cost=573.41..789.92 rows=1 width=162) (actual time=1.527..1,871.315 rows=101,086 loops=1)

6. 29.954 1,631.558 ↓ 101,086.0 101,086 1

Hash Join (cost=573.13..787.29 rows=1 width=155) (actual time=1.516..1,631.558 rows=101,086 loops=1)

  • Hash Cond: ((p.id_rd)::integer = (rd.id_rd)::integer)
7. 12.919 1,600.314 ↓ 65.2 104,874 1

Nested Loop (cost=0.43..210.37 rows=1,608 width=135) (actual time=0.220..1,600.314 rows=104,874 loops=1)

8. 0.012 0.012 ↑ 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.011..0.012 rows=1 loops=1)

  • Index Cond: ((id_geometria)::integer = 13,676,902)
9. 9.734 1,587.383 ↓ 639.5 104,874 1

Append (cost=0.00..205.88 rows=164 width=36) (actual time=0.206..1,587.383 rows=104,874 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-09-15 00:00:00-03'::timestamp with time zone) AND (dt_operacao <= '2020-09-15 23:59:00-03'::timestamp with time zone) AND (st_expand(g.geom, '8.9932e-05'::double precision) && geom))
11. 1,574.708 1,574.708 ↓ 651.4 104,874 1

Index Scan using ind_tbpontocrn_geom_dtop on tb_ponto_clust_recent p_1 (cost=0.56..199.78 rows=161 width=36) (actual time=0.184..1,574.708 rows=104,874 loops=1)

  • Index Cond: ((st_expand(g.geom, '8.9932e-05'::double precision) && geom) AND (dt_operacao >= '2020-09-15 00:00:00-03'::timestamp with time zone) AND (dt_operacao <= '2020-09-15 23:59:00-03'::timestamp with time zone))
12. 0.015 0.015 ↓ 0.0 0 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.015..0.015 rows=0 loops=1)

  • Index Cond: ((st_expand(g.geom, '8.9932e-05'::double precision) && geom) AND (dt_operacao >= '2020-09-15 00:00:00-03'::timestamp with time zone) AND (dt_operacao <= '2020-09-15 23:59:00-03'::timestamp with time zone))
13. 2.924 2.924 ↓ 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=2.924..2.924 rows=0 loops=1)

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

Hash (cost=563.74..563.74 rows=716 width=24) (actual time=1.290..1.290 rows=1,238 loops=1)

  • Buckets: 2,048 (originally 1024) Batches: 1 (originally 1) Memory Usage: 89kB
15. 1.083 1.083 ↓ 1.7 1,238 1

Index Scan using ind_tb_rd_idfilial_dtini_idveiculo_idrd on tb_rd rd (cost=0.43..563.74 rows=716 width=24) (actual time=0.023..1.083 rows=1,238 loops=1)

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

Index Scan using pk00_tb_veiculo on tb_veiculo v (cost=0.29..2.62 rows=1 width=15) (actual time=0.002..0.002 rows=1 loops=101,086)

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

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=101,086)

  • Index Cond: ((id_camada)::integer = (g.id_camada)::integer)
Planning time : 2.152 ms
Execution time : 2,274.386 ms