Hola a todos buenas tardes tengo esta consulta: select distinct on(l.id) l.id ,l.nom_cli,l.v_gral,o.gid,o.sector,o.fechaini,o.fechafin,o.horaini,o.horafin, l.sector as sec,l.clvmzna,l.lote,g.nombre from cat_distrito d join cat_lote l on st_contains(d.the_geom,l.geo_punto) left join sector_operacional1 o on st_contains(o.the_geom,l.geo_punto) join graf_poblacional g on st_contains(g.the_geom,l.geo_punto) where
--- antes estaba con este filtro --- d.distrito = '09' and l.id is not null and (l.id like '0%' or l.id like '1%' or l.id like '2%') d.distrito = '09' and digito is not null and digito between '0' and '2' and length(l.id) > 6 order by id y este es su explain "Unique (cost=74669.40..77500.35 rows=60126 width=122) (actual time=38520.725..38544.180 rows=81932 loops=1)" " -> Sort (cost=74669.40..76084.88 rows=566191 width=122) (actual time=38520.720..38527.005 rows=84427 loops=1)" " Sort Key: l.id" " Sort Method: quicksort Memory: 21459kB" " -> Nested Loop (cost=4.89..20567.22 rows=566191 width=122) (actual time=25.876..37791.435 rows=84427 loops=1)" " -> Nested Loop Left Join (cost=4.63..8112.74 rows=18904 width=139) (actual time=25.327..6901.350 rows=87455 loops=1)" " -> Nested Loop (cost=4.49..149.89 rows=18904 width=91) (actual time=25.231..552.509 rows=86219 loops=1)" " -> Seq Scan on cat_distrito d (cost=0.00..33.17 rows=1 width=4040) (actual time=0.025..0.050 rows=1 loops=1)" " Filter: ((distrito)::text = '09'::text)" " Rows Removed by Filter: 13" " -> Bitmap Heap Scan on cat_lote l (cost=4.49..116.70 rows=2 width=91) (actual time=25.199..534.881 rows=86219 loops=1)" " Recheck Cond: (d.the_geom && geo_punto)" " Filter: ((digito IS NOT NULL) AND (digito >= '0'::bpchar) AND (digito <= '2'::bpchar) AND (length((id)::text) > 6) AND _st_contains(d.the_geom, geo_punto))" " Rows Removed by Filter: 42774" " -> Bitmap Index Scan on idx_cat_lote_geo_punto (cost=0.00..4.48 rows=27 width=0) (actual time=23.634..23.634 rows=128993 loops=1)" " Index Cond: (d.the_geom && geo_punto)" " -> Index Scan using sector_operacional1_idx on sector_operacional1 o (cost=0.14..0.41 rows=1 width=680) (actual time=0.070..0.072 rows=1 loops=86219)" " Index Cond: (the_geom && l.geo_punto)" " Filter: _st_contains(the_geom, l.geo_punto)" " Rows Removed by Filter: 1" " -> Index Scan using idx_graf_poblacional_the_geom on graf_poblacional g (cost=0.27..0.65 rows=1 width=3536) (actual time=0.283..0.352 rows=1 loops=87455)" " Index Cond: (the_geom && l.geo_punto)" " Filter: _st_contains(the_geom, l.geo_punto)" " Rows Removed by Filter: 3" "Total runtime: 38548.873 ms" La consulta tarda alrededor de 40 segundos. Si me pudieran dar unos tips para mejorar la consulta y hacer que esta sea mas rapida saludos -- José Mercedes Venegas Acevedo cel claro 940180540 mails: jvenegasp...@gmail.com
