explain.depesz.com

PostgreSQL's explain analyze made readable

Result: fpXM

Settings
# exclusive inclusive rows x rows loops node
1. 0.100 1,828.881 ↓ 183.0 183 1

Sort (cost=960.37..960.37 rows=1 width=39) (actual time=1,828.868..1,828.881 rows=183 loops=1)

  • Sort Key: (count(*)) DESC
  • Sort Method: quicksort Memory: 39kB
2. 2.440 1,828.781 ↓ 183.0 183 1

GroupAggregate (cost=960.33..960.36 rows=1 width=39) (actual time=1,825.301..1,828.781 rows=183 loops=1)

  • Group Key: rd.id_rd, v.cd_identificador
3. 7.796 1,826.341 ↓ 12,294.0 12,294 1

Sort (cost=960.33..960.34 rows=1 width=31) (actual time=1,825.290..1,826.341 rows=12,294 loops=1)

  • Sort Key: rd.id_rd, v.cd_identificador
  • Sort Method: quicksort Memory: 1,345kB
4. 331.924 1,818.545 ↓ 12,294.0 12,294 1

Nested Loop (cost=711.99..960.32 rows=1 width=31) (actual time=19.087..1,818.545 rows=12,294 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: 83,050
5. 107.743 1,295.933 ↓ 95,344.0 95,344 1

Nested Loop (cost=711.71..957.30 rows=1 width=162) (actual time=1.617..1,295.933 rows=95,344 loops=1)

6. 36.191 997.502 ↓ 95,344.0 95,344 1

Hash Join (cost=711.43..954.67 rows=1 width=155) (actual time=1.604..997.502 rows=95,344 loops=1)

  • Hash Cond: ((p.id_rd)::integer = (rd.id_rd)::integer)
7. 14.067 960.083 ↓ 52.6 97,079 1

Nested Loop (cost=0.43..238.84 rows=1,844 width=135) (actual time=0.369..960.083 rows=97,079 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. 10.950 946.004 ↓ 519.1 97,079 1

Append (cost=0.00..234.11 rows=187 width=36) (actual time=0.356..946.004 rows=97,079 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-10-15 00:00:00-03'::timestamp with time zone) AND (dt_operacao <= '2020-10-15 23:59:00-03'::timestamp with time zone) AND (st_expand(g.geom, '8.9932e-05'::double precision) && geom))
11. 930.105 930.105 ↓ 527.2 97,010 1

Index Scan using ind_tbpontocrn_geom_dtop on tb_ponto_clust_recent p_1 (cost=0.56..227.89 rows=184 width=36) (actual time=0.335..930.105 rows=97,010 loops=1)

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

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

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

Hash (cost=699.86..699.86 rows=891 width=24) (actual time=1.228..1.228 rows=1,178 loops=1)

  • Buckets: 2,048 (originally 1024) Batches: 1 (originally 1) Memory Usage: 84kB
15. 1.031 1.031 ↓ 1.3 1,178 1

Index Scan using ind_tb_rd_idfilial_dtini_idveiculo_idrd on tb_rd rd (cost=0.43..699.86 rows=891 width=24) (actual time=0.027..1.031 rows=1,178 loops=1)

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

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=95,344)

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

Index Scan using pk00_tb_camada on tb_camada c (cost=0.28..2.69 rows=1 width=10) (actual time=0.002..0.002 rows=1 loops=95,344)

  • Index Cond: ((id_camada)::integer = (g.id_camada)::integer)
Planning time : 2.069 ms
Execution time : 1,829.600 ms