explain.depesz.com

PostgreSQL's explain analyze made readable

Result: V16S

Settings
# exclusive inclusive rows x rows loops node
1. 2.518 3,255.179 ↓ 6.6 1,144 1

Unique (cost=3,812.43..3,813.30 rows=173 width=32) (actual time=3,252.330..3,255.179 rows=1,144 loops=1)

2. 15.640 3,252.661 ↓ 8.7 1,502 1

Sort (cost=3,812.43..3,812.86 rows=173 width=32) (actual time=3,252.328..3,252.661 rows=1,502 loops=1)

  • Sort Key: (ROW(v.id_veiculo, g.id_geometria))
  • Sort Method: quicksort Memory: 166kB
3. 16.341 3,237.021 ↓ 8.7 1,502 1

Nested Loop (cost=236.95..3,806.00 rows=173 width=32) (actual time=34.951..3,237.021 rows=1,502 loops=1)

4. 13.951 76.232 ↓ 128.0 22,144 1

Nested Loop (cost=219.01..696.61 rows=173 width=595) (actual time=22.627..76.232 rows=22,144 loops=1)

5. 0.524 23.241 ↓ 128.0 128 1

Hash Join (cost=218.59..381.87 rows=1 width=8) (actual time=22.597..23.241 rows=128 loops=1)

  • Hash Cond: ((rd.id_veiculo)::integer = (v.id_veiculo)::integer)
6. 0.665 22.402 ↓ 10.5 576 1

Bitmap Heap Scan on tb_rd rd (cost=75.99..238.58 rows=55 width=8) (actual time=21.774..22.402 rows=576 loops=1)

  • Recheck Cond: (('["2019-01-29 00:00:00-02","2019-01-30 00:00:00-02"]'::tstzrange && tstzrange(dt_inicio_terminal, dt_fim_terminal, '[]'::text)) AND ((id_filial)::integer = 194))
  • Heap Blocks: exact=108
7. 0.057 21.737 ↓ 0.0 0 1

BitmapAnd (cost=75.99..75.99 rows=55 width=0) (actual time=21.737..21.737 rows=0 loops=1)

8. 20.097 20.097 ↓ 9.7 2,955 1

Bitmap Index Scan on ind_tb_rd_idveiculo_dtiniterm_dtfimterm (cost=0.00..18.56 rows=304 width=0) (actual time=20.097..20.097 rows=2,955 loops=1)

  • Index Cond: ('["2019-01-29 00:00:00-02","2019-01-30 00:00:00-02"]'::tstzrange && tstzrange(dt_inicio_terminal, dt_fim_terminal, '[]'::text))
9. 1.583 1.583 ↑ 1.0 5,488 1

Bitmap Index Scan on ind_tb_rd_gin_idfilial_tpsituacao (cost=0.00..57.16 rows=5,488 width=0) (actual time=1.583..1.583 rows=5,488 loops=1)

  • Index Cond: ((id_filial)::integer = 194)
10. 0.093 0.315 ↓ 1.0 172 1

Hash (cost=140.45..140.45 rows=171 width=4) (actual time=0.315..0.315 rows=172 loops=1)

  • Buckets: 1024 Batches: 1 Memory Usage: 15kB
11. 0.152 0.222 ↓ 1.0 172 1

Bitmap Heap Scan on tb_veiculo v (cost=13.33..140.45 rows=171 width=4) (actual time=0.078..0.222 rows=172 loops=1)

  • Recheck Cond: (rotulos && '{410,436}'::integer[])
  • Heap Blocks: exact=19
12. 0.070 0.070 ↓ 1.0 172 1

Bitmap Index Scan on tb_veiculo_rotulos_idx (cost=0.00..13.28 rows=171 width=0) (actual time=0.070..0.070 rows=172 loops=1)

  • Index Cond: (rotulos && '{410,436}'::integer[])
13. 39.040 39.040 ↑ 1.0 173 128

Index Scan using tb_geometria_nm_geometria_key on tb_geometria g (cost=0.42..313.02 rows=173 width=587) (actual time=0.020..0.305 rows=173 loops=128)

  • Index Cond: ((id_camada)::integer = 2301)
14. 44.288 3,144.448 ↓ 0.0 0 22,144

Aggregate (cost=17.94..17.95 rows=1 width=8) (actual time=0.142..0.142 rows=0 loops=22,144)

  • Filter: (count(*) > 1)
  • Rows Removed by Filter: 1
15. 0.000 3,100.160 ↓ 0.0 0 22,144

Limit (cost=0.00..17.92 rows=2 width=4) (actual time=0.131..0.140 rows=0 loops=22,144)

16. 22.144 3,100.160 ↓ 0.0 0 22,144

Result (cost=0.00..53.75 rows=6 width=4) (actual time=0.131..0.140 rows=0 loops=22,144)

17. 304.394 3,078.016 ↓ 0.0 0 22,144

Append (cost=0.00..53.69 rows=6 width=0) (actual time=0.130..0.139 rows=0 loops=22,144)

18. 0.000 0.000 ↓ 0.0 0 22,144

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

  • Filter: ((geom && g.geom) AND ((id_rd)::integer = (rd.id_rd)::integer) AND (st_distance(geom, g.geom) <= '0.0005'::double precision))
19. 2,546.560 2,546.560 ↓ 0.0 0 22,144

Index Scan using ind_tbpontocr_idrd_geom on tb_ponto_clust_recent p_1 (cost=0.42..37.10 rows=3 width=0) (actual time=0.106..0.115 rows=0 loops=22,144)

  • Index Cond: (((id_rd)::integer = (rd.id_rd)::integer) AND (geom && g.geom))
  • Filter: (st_distance(geom, g.geom) <= '0.0005'::double precision)
  • Rows Removed by Filter: 1
20. 206.420 206.420 ↓ 0.0 0 20,642

Index Scan using ind_tbpontou2016_idrd_geom on tb_ponto_clust_until_2016 p_2 (cost=0.29..8.37 rows=1 width=0) (actual time=0.010..0.010 rows=0 loops=20,642)

  • Index Cond: (((id_rd)::integer = (rd.id_rd)::integer) AND (geom && g.geom))
  • Filter: (st_distance(geom, g.geom) <= '0.0005'::double precision)
21. 20.642 20.642 ↓ 0.0 0 20,642

Index Scan using ind_tbpontoin_idrd_geom on tb_ponto_in p_3 (cost=0.14..8.23 rows=1 width=0) (actual time=0.001..0.001 rows=0 loops=20,642)

  • Index Cond: (((id_rd)::integer = (rd.id_rd)::integer) AND (geom && g.geom))
  • Filter: (st_distance(geom, g.geom) <= '0.0005'::double precision)