explain.depesz.com

PostgreSQL's explain analyze made readable

Result: 6gns

Settings
# exclusive inclusive rows x rows loops node
1. 0.095 5,034.945 ↓ 194.0 194 1

Sort (cost=805.79..805.79 rows=1 width=39) (actual time=5,034.931..5,034.945 rows=194 loops=1)

  • Sort Key: (count(*)) DESC
  • Sort Method: quicksort Memory: 40kB
2. 2.466 5,034.850 ↓ 194.0 194 1

GroupAggregate (cost=805.76..805.78 rows=1 width=39) (actual time=5,031.361..5,034.850 rows=194 loops=1)

  • Group Key: rd.id_rd, v.cd_identificador
3. 7.577 5,032.384 ↓ 12,417.0 12,417 1

Sort (cost=805.76..805.76 rows=1 width=31) (actual time=5,031.350..5,032.384 rows=12,417 loops=1)

  • Sort Key: rd.id_rd, v.cd_identificador
  • Sort Method: quicksort Memory: 1,355kB
4. 361.214 5,024.807 ↓ 12,417.0 12,417 1

Nested Loop (cost=207.02..805.75 rows=1 width=31) (actual time=3,347.221..5,024.807 rows=12,417 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: 80,811
5. 119.097 4,477.137 ↓ 93,228.0 93,228 1

Nested Loop (cost=206.74..802.72 rows=1 width=162) (actual time=3,346.975..4,477.137 rows=93,228 loops=1)

6. 823.480 4,171.584 ↓ 93,228.0 93,228 1

Hash Join (cost=206.46..800.09 rows=1 width=155) (actual time=3,346.952..4,171.584 rows=93,228 loops=1)

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

Index Scan using ind_tb_rd_idfilial_dtini_idveiculo_idrd on tb_rd rd (cost=0.43..591.24 rows=751 width=24) (actual time=0.016..1.433 rows=1,238 loops=1)

  • Index Cond: (((id_filial)::integer = 55) AND (dt_inicio_terminal <= '2020-09-01 23:59:00-03'::timestamp with time zone) AND (dt_inicio_terminal >= '2020-08-31 00:00:00'::timestamp without time zone))
8. 510.499 3,346.671 ↓ 65.3 93,603 1

Hash (cost=188.10..188.10 rows=1,434 width=135) (actual time=3,346.671..3,346.671 rows=93,603 loops=1)

  • Buckets: 32,768 (originally 2048) Batches: 8 (originally 1) Memory Usage: 79,348kB
9. 19.784 2,836.172 ↓ 65.9 94,464 1

Nested Loop (cost=0.43..188.10 rows=1,434 width=135) (actual time=0.238..2,836.172 rows=94,464 loops=1)

10. 0.015 0.015 ↑ 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.012..0.015 rows=1 loops=1)

  • Index Cond: ((id_geometria)::integer = 13,676,902)
11. 10.141 2,816.373 ↓ 647.0 94,464 1

Append (cost=0.00..183.79 rows=146 width=36) (actual time=0.223..2,816.373 rows=94,464 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-01 00:00:00-03'::timestamp with time zone) AND (dt_operacao <= '2020-09-01 23:59:00-03'::timestamp with time zone) AND (st_expand(g.geom, '8.9932e-05'::double precision) && geom))
13. 2,801.943 2,801.943 ↓ 660.4 94,442 1

Index Scan using ind_tbpontocrn_geom_dtop on tb_ponto_clust_recent p_1 (cost=0.56..177.77 rows=143 width=36) (actual time=0.198..2,801.943 rows=94,442 loops=1)

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

  • Index Cond: (st_expand(g.geom, '8.9932e-05'::double precision) && geom)
  • Filter: ((dt_operacao >= '2020-09-01 00:00:00-03'::timestamp with time zone) AND (dt_operacao <= '2020-09-01 23:59:00-03'::timestamp with time zone))
  • Rows Removed by Filter: 9,215
16. 186.456 186.456 ↑ 1.0 1 93,228

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=93,228)

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

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=93,228)

  • Index Cond: ((id_camada)::integer = (g.id_camada)::integer)
Planning time : 2.328 ms
Execution time : 5,040.320 ms