duda con joins(¿cual de estas consultas es mejor?)

[refreegrata]

Hola lista, tengo una duda teórica.

hay alguna diferencia en hacer algo como

select A.*,B.* FROM A inner join B on (b.subcodigo = a.codigo AND b.subcodigo2=15)

y

select A.*,B.* FROM A inner join B on (b.subcodigo = a.codigo) WHERE b.subcodigo2=15

???
es decir, hacer un filtro para dentro del on o en el Where.

Eso es, saludos

[huesos52]

He tenido la oportunidad de probar este fenómeno en tablas medianas que superan el millón de registros.
Aunque el resultado es el mismo, la diferencia en rendimiento es abismal.

En mis pruebas ha sido muchísimo mas optimo usar la segunda opción.

Analizando un poco la situación, supuse que esto se debe a que normalmente las condiciones establecidas en el ON que realizan un producto cartesiano entre las tablas, está diseñado especialmente para filtrar campos que han sido indexados previamente (Aunque soporte campos no indexados). Esta es una tarea muy rápida que puede ser afectada seriamente en rendimiento cuando se establece una segunda condición con un campo que no está diseñado para este producto cartesiano.

Por el contrario, en la segunda opción, es muy eficiente realizar el producto cartesiano y sobre este resultado ya filtrado realizar una búsqueda especifica.

No he encontrado una fuente oficial y seria que explique y detalle esto. Pero con base en las pruebas está puede ser una razón valida.

saludos

[gnzsoloyo]

Eso no es un filtro. Es la condición de un INNER JOIN, y se debe poner de esa forma, precisamente.
El temas es simple: El FROM es optimizable estadísticamente por el paser y e WHERE no, por lo que eventualmente dos ejecuciones de la misma sentencia pueden tener diferente perfomance, y además el INNER JOIN descarta registros en la medida que los lee.
En cambio, en el WHERE deberá obtener toda la tabla de datos antes de aplicar los filtros, por lo que la performance es baja, ya que al realizar un JOIN sin condiciones ON se generaría un producto cartesiano.

Si quieres probarlo, deberías usar EXPLAIN sentencia, para que te devuelva los datos análisis de la sentencia.

[refreegrata]

Mis consultas son algo así:

opcion 1:

Código PHP:

  SELECT ingresodetalle.*, producto.*, unidad.valordecimal FROM producto
INNER JOIN ingresodetalle ON (
         ingresodetalle.codigo_ingreso=1608 AND
         EXISTS (SELECT ordencompradetalle.codigo FROM ordencompradetalle
                      WHERE ordencompradetalle.codigo_orden=1715 AND
                      ordencompradetalle.codigo=ingresodetalle.ordencompradetalle) AND
         ingresodetalle.codigo_producto = producto.codigo )
INNER JOIN unidad ON ( unidad.detalle=producto.unidad )
WHERE producto.codigo_maestroproducto=2 ORDER BY ingresodetalle.codigo 
   

y la opcion 2:

Código PHP:

  SELECT ingresodetalle.*, producto.*, unidad.valordecimal FROM producto
INNER JOIN ingresodetalle ON (  ingresodetalle.codigo_producto = producto.codigo )
INNER JOIN unidad ON ( unidad.detalle=producto.unidad )
WHERE producto.codigo_maestroproducto=2 AND
            ingresodetalle.codigo_ingreso=1608 AND
            EXISTS (SELECT ordencompradetalle.codigo FROM ordencompradetalle
                         WHERE ordencompradetalle.codigo_orden=1715 AND
                 ordencompradetalle.codigo=ingresodetalle.ordencompradetalle)
ORDER BY ingresodetalle.codigo 
   

el explain:

"Sort (cost=195.86..195.87 rows=2 width=187)"
" Sort Key: ingresodetalle.codigo"
" -> Nested Loop (cost=162.13..195.85 rows=2 width=187)"
" -> Nested Loop (cost=162.13..195.28 rows=2 width=186)"
" -> Nested Loop (cost=162.13..178.71 rows=2 width=69)"
" -> Unique (cost=162.13..162.14 rows=2 width=8)"
" -> Sort (cost=162.13..162.14 rows=2 width=8)"
" Sort Key: ordencompradetalle.codigo"
" -> Seq Scan on ordencompradetalle (cost=0.00..162.12 rows=2 width=8)"
" Filter: (codigo_orden = 1715)"
" -> Index Scan using fki_tiene_or_in on ingresodetalle (cost=0.00..8.27 rows=1 width=69)"
" Index Cond: (ingresodetalle.ordencompradetalle = ordencompradetalle.codigo)"
" Filter: (ingresodetalle.codigo_ingreso = 1608)"
" -> Index Scan using producto_pkey on producto (cost=0.00..8.27 rows=1 width=117)"
" Index Cond: (producto.codigo = ingresodetalle.codigo_producto)"
" Filter: (producto.codigo_maestroproducto = 2)"
" -> Index Scan using unidad_pkey on unidad (cost=0.00..0.28 rows=1 width=12)"
" Index Cond: (unidad.detalle = producto.unidad)"


El "explain" Es exactamente el mismo para ambas consultas, y como se ven iguales es que me asalta la duda acerca de que es en términos generales lo mejor.

Quizás mi comprensión de lectura no es de las mejores, pero me parece que sus opiniones están un poco contrapuestas.

Realmente la duda se me produce porque en la actualidad desplegar una página web en donde se hace uso de esta consulta paso de demorar en desplegarse de unos 4 segundos a unos 18 segundos. Tampoco es que sea una demora excesiva, ni que sea inutilizable esa sección, si no que simplemente me despertó curiosidad.

Saludos.

P.D.1: Uso postgresql 8.4
P.D.2: La razón del tiempo que se toma en desplegar la página es debido a la consulta, a pesar de que la tabla no ha crecido demasiado.
P.D.3: el servidor parece estar configurado de acuerdo a lo recomendado.

[gnzsoloyo]

En realidad, yo la probaría así:

Código MySQL:

SELECT
  I.*, P.*, U.valordecimal
FROM
  (SELECT * FROM producto WHERE codigo_maestroproducto=2) P
  INNER JOIN (SELECT * FROM ingresodetalle WHERE codigo_ingreso=1608) I
    ON P.codigo = I.codigo_producto
  INNER JOIN unidad U ON P.unidad = U.detalle
WHERE
  ordencompradetalle IN (SELECT
      O.codigo
    FROM
      ordencompradetalle O
    WHERE
      O.codigo_orden=1715)
ORDER BY I.codigo
 

[refreegrata]

Gracias por responder. "gnzsoloyo" tu sentencia también arroja el mismo "explain", y ahí estoy un poco perdido en la teoría. Por que todas las consultas arrojan exactamente el mismo "Explain", podría esto cambiar si el número de datos en la tabla variara o los parametros(números enteros colocados) o algo así o es que el planificador ya ha elegido el mejor camino o algo???

Saludos.

[huesos52]

No es que sean opiniones contrapuestas. Gnzsoloyo lo unico que dice es que no es un filtro. Para mi una condición y un filtro tienen un significado muy similar. Es mas, para definir condición, creo que utilizaría la palabra filtro.

Con respecto al comando explain este busca la mejor ruta de ejecución de la consulta y la muestra. Este proceso como lo describe gnzsoloyo lo hace con base en estadisticas de la tabla. Por lo que el siempre toma la información presente en pg_stats para encontrar su mejor ruta.

Mira esta secuencia de comandos y compara explains.

Código SQL:

pruebas=> CREATE TABLE prueba_explain(id INTEGER);
CREATE TABLE
pruebas=> SELECT relname,reltuples FROM pg_class WHERE relname='prueba_explain';
    relname     | reltuples
----------------+-----------
 prueba_explain |         0
(1 fila)
 
pruebas=> INSERT INTO prueba_explain VALUES(8),(9),(10);
INSERT 0 3
 
pruebas=> SELECT COUNT(*) FROM prueba_explain;
 COUNT
-------
     3
(1 fila)
 
pruebas=> EXPLAIN SELECT *FROM prueba_explain;
                            QUERY PLAN
------------------------------------------------------------------
 Seq Scan ON prueba_explain  (cost=0.00..31.40 ROWS=2140 width=4)
(1 fila)
 
 
pruebas=> ANALYZE prueba_explain;
ANALYZE
 
pruebas=> SELECT relname,reltuples FROM pg_class WHERE relname='prueba_explain';
    relname     | reltuples
----------------+-----------
 prueba_explain |         3
(1 fila)
 
pruebas=> EXPLAIN SELECT *FROM prueba_explain;
                          QUERY PLAN
--------------------------------------------------------------
 Seq Scan ON prueba_explain  (cost=0.00..1.03 ROWS=3 width=4)
(1 fila)
 
pruebas=>
 

Prueba actualizando las estadísticas de las tablas y acomodando las tuplas que no uses.
VACUUM ANALYZE.

Cuando dices que una tarda 4 segundos y la otra 18, que consulta te ofrece el mejor tiempo?
En mi caso, la segunda es mucho mas eficiente cuando hablamos de muchos registros. En tablas de pocos registros este tiempo es invisible.

[refreegrata]

Iniciado por huesos52

Cuando dices que una tarda 4 segundos y la otra 18, que consulta te ofrece el mejor tiempo?.

no es que las consultas demoren más, demoran lo mismo, lo que trate de decir es que antes en desplegar la página web con cualquiera de las 2 consultas demora 4 segundos y ahora unos 18 a pesar de que la tabla no ha crecido demasiado. Hablo de la página web entera y no de la consulta, pero el mayor atraso es por la consulta. Se que sería mejor probar las consultas directamente en el equipo y no suponerlo a través del browser, pero yo sólo soy el programador y el acceso al servidor no es algo a lo que esté autorizado y cualquier duda acerca de la configuración, tamaño de la tabla y número de registros debo consultarlo al adminitrador y no probarlo yo mismo, lo cual complica un poco las cosas.

Con respecto al VACUUM me informan que esta configurado para ejecutarse automáticamente.

Evidentemente en la copia local que tengo en mi equipo corre de lujo, pero esa no es una prueba demasiado condfiable.

Saludos.