explain.depesz.com

PostgreSQL's explain analyze made readable

Result: rPVG

Settings
# exclusive inclusive rows x rows loops node
1. 0.119 141,682.678 ↓ 198.0 198 1

Sort (cost=917.87..917.87 rows=1 width=39) (actual time=141,682.665..141,682.678 rows=198 loops=1)

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

GroupAggregate (cost=917.84..917.86 rows=1 width=39) (actual time=141,679.451..141,682.559 rows=198 loops=1)

  • Group Key: rd.id_rd, v.cd_identificador
3. 6.150 141,680.320 ↓ 11,520.0 11,520 1

Sort (cost=917.84..917.84 rows=1 width=31) (actual time=141,679.437..141,680.320 rows=11,520 loops=1)

  • Sort Key: rd.id_rd, v.cd_identificador
  • Sort Method: quicksort Memory: 1,285kB
4. 349.114 141,674.170 ↓ 11,520.0 11,520 1

Nested Loop (cost=221.91..917.83 rows=1 width=31) (actual time=140,177.317..141,674.170 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. 82.481 141,122.884 ↓ 101,086.0 101,086 1

Nested Loop (cost=221.63..914.80 rows=1 width=158) (actual time=140,175.897..141,122.884 rows=101,086 loops=1)

6. 563.337 140,838.231 ↓ 101,086.0 101,086 1

Hash Join (cost=221.34..912.17 rows=1 width=151) (actual time=140,175.859..140,838.231 rows=101,086 loops=1)

  • Hash Cond: ((rd.id_rd)::integer = (p.id_rd)::integer)
7. 266.641 266.641 ↓ 1.4 1,238 1

Index Scan using ind_tb_rd_idfilial_dtini_idveiculo_idrd on tb_rd rd (cost=0.43..687.95 rows=880 width=24) (actual time=4.797..266.641 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))
8. 787.824 140,008.253 ↓ 66.3 101,895 1

Hash (cost=201.71..201.71 rows=1,536 width=131) (actual time=140,008.252..140,008.253 rows=101,895 loops=1)

  • Buckets: 32,768 (originally 2048) Batches: 8 (originally 1) Memory Usage: 79,348kB
9. 46.695 139,220.429 ↓ 68.3 104,874 1

Nested Loop (cost=0.43..201.71 rows=1,536 width=131) (actual time=31.335..139,220.429 rows=104,874 loops=1)

10. 0.900 0.900 ↑ 1.0 1 1

Index Scan using pk00_tb_geometria on tb_geometria g (cost=0.43..2.85 rows=1 width=95) (actual time=0.898..0.900 rows=1 loops=1)

  • Index Cond: ((id_geometria)::integer = 13,676,902)
11. 20.112 139,172.834 ↓ 668.0 104,874 1

Append (cost=0.00..197.29 rows=157 width=36) (actual time=30.432..139,172.834 rows=104,874 loops=1)

12. 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))
13. 139,035.680 139,035.680 ↓ 681.0 104,874 1

Index Scan using ind_tbpontocrn_geom_dtop on tb_ponto_clust_recent p_1 (cost=0.56..191.22 rows=154 width=36) (actual time=30.395..139,035.680 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))
14. 0.076 0.076 ↓ 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.076..0.076 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))
15. 116.964 116.964 ↓ 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=116.964..116.964 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,078
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. 202.172 202.172 ↑ 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.002..0.002 rows=1 loops=101,086)

  • Index Cond: ((id_camada)::integer = (g.id_camada)::integer)
Planning time : 34.458 ms
Execution time : 141,687.107 ms