Hola amigos, resulta que tratando de responder a una pregunta en el foro de PHP ahora me surgio una duda a mi, es sobre cual consulta es mas correcta si usar:
Código PHP:
AND SUBSTRING(o.ordenid FROM 1 FOR 1) = 'B'
o
Código PHP:
AND O.ordenid LIKE 'B%'
bueno aca les dejo el plan de ejecucion que me arrojo mi Postgresql y no soy muy bueno en eso de interpretar este tipo de resultados, por eso aca se los dejo a los expertos.
Si alguien podria sacarme de la duda le estare muy agradecido (Disculpen si no es este el foro correcto)
Consulta con substring
Código PHP:
select sum(od.importeunitario) as coniva, (sum(od.importeunitario) /1.10) as siniva,
p.platillo, p.platillosid, tp.claseid, s.sucursal
from platillos p left join tipoplatillo tp on p.tipo = tp.tipoplatilloid, ordendetalles od, orden o, sucursales s
where tp.claseid = 3 and p.platillosid = od.platilloid
and substring(od.ordendetalleid from 1 for 1) = s.sucursalid and od.ordenid = o.ordenid
AND o.horainicio BETWEEN '2007-10-10 04:00:00' AND '2007-10-11 03:59:59' and o.status < 7 and od.status = 1
AND SUBSTRING(o.ordenid FROM 1 FOR 1) = 'B'
group by p.platillo, p.platillosid, tp.claseid, s.sucursal
order by s.sucursal:
QUERY PLAN
Sort (cost=11307.30..11307.31 rows=5 width=54) (actual time=16.405..16.434 rows=10 loops=1)
Sort Key: s.sucursal
-> HashAggregate (cost=11307.20..11307.24 rows=5 width=54) (actual time=16.260..16.317 rows=10 loops=1)
-> Hash Join (cost=11296.76..11307.13 rows=5 width=54) (actual time=11.843..15.564 rows=70 loops=1)
Hash Cond: ("outer".tipo = "inner".tipoplatilloid)
-> Hash Join (cost=11295.47..11305.72 rows=9 width=56) (actual time=11.611..14.394 rows=236 loops=1)
Hash Cond: ("outer".platillosid = "inner".platilloid)
-> Seq Scan on platillos p (cost=0.00..6.74 rows=274 width=31) (actual time=0.005..0.952 rows=274 loops=1)
-> Hash (cost=11295.44..11295.44 rows=9 width=29) (actual time=11.586..11.586 rows=0 loops=1)
-> Hash Join (cost=1.19..11295.44 rows=9 width=29) (actual time=0.366..10.776 rows=236 loops=1)
Hash Cond: ("substring"(("outer".ordendetalleid)::text, 1, 1) = ("inner".sucursalid)::text)
-> Nested Loop (cost=0.00..11293.27 rows=119 width=27) (actual time=0.221..8.478 rows=236 loops=1)
-> Index Scan using idxhorainicioorden on orden o (cost=0.00..10341.06 rows=35 width=14) (actual time=0.166..3.148 rows=64 loops=1)
Index Cond: ((horainicio >= '2007-10-10 04:00:00'::timestamp without time zone) AND (horainicio <= '2007-10-11 03:59:59'::timestamp without time zone))
Filter: ((status < 7) AND ("substring"((ordenid)::text, 1, 1) = 'B'::text))
-> Index Scan using idxordenidordendetalles on ordendetalles od (cost=0.00..27.13 rows=6 width=41) (actual time=0.027..0.050 rows=4 loops=64)
Index Cond: ((od.ordenid)::text = ("outer".ordenid)::text)
Filter: (status = 1)
-> Hash (cost=1.15..1.15 rows=15 width=21) (actual time=0.124..0.124 rows=0 loops=1)
-> Seq Scan on sucursales s (cost=0.00..1.15 rows=15 width=21) (actual time=0.007..0.067 rows=15 loops=1)
-> Hash (cost=1.26..1.26 rows=11 width=6) (actual time=0.103..0.103 rows=0 loops=1)
-> Seq Scan on tipoplatillo tp (cost=0.00..1.26 rows=11 width=6) (actual time=0.017..0.064 rows=10 loops=1)
Filter: (claseid = 3)
Total runtime: 16.647 ms
Consulta con Like
Código PHP:
select sum(od.importeunitario) as coniva, (sum(od.importeunitario) /1.10) as siniva,
p.platillo, p.platillosid, tp.claseid, s.sucursal
from platillos p left join tipoplatillo tp on p.tipo = tp.tipoplatilloid, ordendetalles od, orden o, sucursales s
where tp.claseid = 3 and p.platillosid = od.platilloid
and substring(od.ordendetalleid from 1 for 1) = s.sucursalid and od.ordenid = o.ordenid
AND o.horainicio BETWEEN '2007-10-10 04:00:00' AND '2007-10-11 03:59:59' and o.status < 7 and od.status = 1
AND O.ordenid LIKE 'B%'
group by p.platillo, p.platillosid, tp.claseid, s.sucursal
order by s.sucursal;
QUERY PLAN
Sort (cost=29189.37..29189.59 rows=89 width=54) (actual time=22.338..22.365 rows=10 loops=1)
Sort Key: s.sucursal
-> HashAggregate (cost=29185.82..29186.49 rows=89 width=54) (actual time=22.200..22.256 rows=10 loops=1)
-> Hash Join (cost=13.00..29184.49 rows=89 width=54) (actual time=4.081..21.391 rows=70 loops=1)
Hash Cond: ("outer".platilloid = "inner".platillosid)
-> Hash Join (cost=1.19..29170.89 rows=177 width=29) (actual time=0.820..17.074 rows=236 loops=1)
Hash Cond: ("substring"(("outer".ordendetalleid)::text, 1, 1) = ("inner".sucursalid)::text)
-> Nested Loop (cost=0.00..29150.31 rows=2350 width=27) (actual time=0.676..14.594 rows=236 loops=1)
-> Index Scan using idxhorainicioorden on orden o (cost=0.00..10323.66 rows=692 width=14) (actual time=0.545..6.719 rows=64 loops=1)
Index Cond: ((horainicio >= '2007-10-10 04:00:00'::timestamp without time zone) AND (horainicio <= '2007-10-11 03:59:59'::timestamp without time zone))
Filter: ((status < 7) AND ((ordenid)::text ~~ 'B%'::text))
-> Index Scan using idxordenidordendetalles on ordendetalles od (cost=0.00..27.13 rows=6 width=41) (actual time=0.045..0.088 rows=4 loops=64)
Index Cond: ((od.ordenid)::text = ("outer".ordenid)::text)
Filter: (status = 1)
-> Hash (cost=1.15..1.15 rows=15 width=21) (actual time=0.119..0.119 rows=0 loops=1)
-> Seq Scan on sucursales s (cost=0.00..1.15 rows=15 width=21) (actual time=0.007..0.063 rows=15 loops=1)
-> Hash (cost=11.46..11.46 rows=138 width=29) (actual time=3.187..3.187 rows=0 loops=1)
-> Hash Join (cost=1.29..11.46 rows=138 width=29) (actual time=0.181..2.642 rows=171 loops=1)
Hash Cond: ("outer".tipo = "inner".tipoplatilloid)
-> Seq Scan on platillos p (cost=0.00..6.74 rows=274 width=31) (actual time=0.024..0.962 rows=274 loops=1)
-> Hash (cost=1.26..1.26 rows=11 width=6) (actual time=0.130..0.130 rows=0 loops=1)
-> Seq Scan on tipoplatillo tp (cost=0.00..1.26 rows=11 width=6) (actual time=0.040..0.092 rows=10 loops=1)
Filter: (claseid = 3)
Total runtime: 22.591 ms