Extenediendo SQL: Operadores

Postgres soporta operadores unitarios izquierdos, unitarios derechos y binarios. Los operadores pueden ser sobrecargados; eso es, el mismo nombre de operador puede ser usado para diferentes operadores que tengan diferentes n�mero y tipo de argumentos. Si hay una situaci�n ambigua y el sistema no puede determinar el operador correcto a usar, retornar� un error. Tu puedes tener los tipos de operadores izquierdo y/o derecho para ayudar a entender que operadores tienen significado usar.

Cada operador es "azucar sint�ctico" por una llamada hacia una funci�n subyacente que hace el trabajo real; entonces tu debes primero crear la funci�n subyacente antes de que puedas crear el operador. Sin embargo, un operador no es s�lo un azucar sint�ctico, porque carga informaci�n adicional que ayuda al planeador de consultas a obtimizar consultas que usa el operador. Mucho de este cap�tulo, ser� dedicado a explicar esa informaci�n adicional. merely syntactic sugar, because it carries additional information

Este es un ejemplo de crear un operador para sumar dos n�meros complejos. Nosotros aumimos que ya hemos creado la definici�n del tipo complejo. Primero necesitamos una funci�n que haga el tragajo; entonces podemos crear el operador.

CREATE FUNCTION complex_add(complex, complex)
    RETURNS complex
    AS '$PWD/obj/complex.so'
    LANGUAGE 'c';

CREATE OPERATOR + (
    leftarg = complex,
    rightarg = complex,
    procedure = complex_add,
    commutator = +
);
   

Ahora podemos hacer:

SELECT (a + b) AS c FROM test_complex;

+----------------+
|c               |
+----------------+
|(5.2,6.05)      |
+----------------+
|(133.42,144.95) |
+----------------+
   

Nosotros hemos mostrado como crear un operador binario aqu�. Para crear un operador unario, solamente omite un argumento izquierdo (para unario izquierdo) o un argumento derecho (para unario derecho). El procedimietno y las sentencias del argumneto son los �nicos items requeridos para CREATE OPERATOR (Crear operador). La sentencia COMMUTATOR (conmutador) mostrada en esta ejemplo es una indicaci�n opcional para optimizar la consulta. Adem�s de los detalles acerca de COMMUTATOR, aparecen otras indicaciones de optimizaci�n.

Informaci�n de optimizaci�n de operador

NotaAutor
 

Escrito por Tom Lane.

Una definici�n de un operador Postgres puede incluir muchas sentencias opcionales que dicen al sistema cosas �tiles acerca de como el operador se comporta. Estas sentencias deben ser proveidas siempre que sea apropiado, porque ellas pueden hacer considerablemente una m�s r�pida ejecuci�n de la consulta que usa el operador. Pero si tu las provees, debes estar seguro que est�n bien! Un incorrecto uso de una sentencia de optimizaci�n puede resultar en choques finales, salidas sutilmente err�neas, o otras cosas malas. Tu puedes no poner las sentencias de optimizaci�n si no est�s seguro de ellas, la �nica consecuencia es que las consultas pueden ejecutarse m�s lentamente de lo necesario.

Sentencias de optimizaci�n adicionales pueden ser agregadas en versiones posteriores de Postgres. Las descr�ptas aqu� son todas aquellas que la versi�n 6.5 entinede.

COMMUTATOR (conmutador)

La sentencia COMMUTATOR ,si es proveida, nombra un operador que es el conmutador del operador que est� siendo definido. Decimos que el operador A es el conmutador del operador B si (x A y) es igual (y B x) para todos los posibles valores de entrada x,y. Nota que B es tambi�n el conmutador de A. Por ejemplo, operadores '<' and '>' (y l�gico) para un tipo de dato particular son usualmente entre s� conmutadores, y el operador '+' es usualmente conmutativo con sigo mismo.Pero el operador '-' no es conmutativo con nada.

El tipo de argumento izquierdo de un operador conmutado es el mismo que el tipo de argumento derecho de su conmutador, y viceversa. Entonces el nombre del operador conmutador es todo lo que Postgres necesita para ser dado de alta el conmutador y eso es todo lo que necesita ser proveido en la sentencia COMMUTATOR.

Cuando tu estas definiendo un operador conmutador por si mismo (self-conmutative), tu solamente hazlo.Cuando tu estas definiendo un par de operadores conmutadores, las cosas son un poquito mas enga�osas: C�mo pede el primero ser definido refiri�ndose a otro, que no ha sido definido todav�a? hay dos soluciones a este problema:

  • Un m�todo es omitir la sentencia COMMUTATOR en el primer operador que tu defines, y entonces proveer uno en la segunda definici�n de operador. Desde que Postgres sabe que operadores conmutativos vienen de a pares, cuando ve la segunda definici�n automaticamente volver� y llenar� en la sentencia COMMUTATOR faltante en la primera definici�n.

  • La otra, una manera mas honesta es solamente incluir la sentencia COMMUTATOR en ambas definiciones. Cuando Postgresprocesa la primera definici�n y se da cuenta COMMUTATOR hace referencia a un opereador inexistenete, el sistema har� una entrada silenciosa para ese operador en la tabla (relaci�n) pg_operator del sistema. Esta entrada silenciosa tendr� datos v�lidos solamente para el nombre del operador, tipos de argumentos izquierdo y derechos, y un tipo de resultado, debido qeu es todo lo que Postgrespuede deducir en ese punto. La primera entrada la cat�logo del operador enlazar� hacia esa entrada silenciosa. Mas tarde, cuando tu definas el segundo operador, el sistema actualiza la entrada silenciosa con la informaci�n adicional de la segunda definici�n. Si tu tratas de usar el operador silenciosa antes de que sea llenado, tu solo obtendras un mensaje de error. (Nota: Este procedimiento no funciona correctamente en versiones de Postgres anteriores a la 6.5, pero es ahora la manera recomendada de hacer las cosas.)

NEGATOR(negador)

La sentencia NEGATOR, si es proveida, nombra a un operador que es el negador del operador que est� siendo definido. Nosotros decimos que un operador A es el negdor de un operador B si ambos retornan resultados bouleanos y (x A y) no es igual (x B y) para todas las posibles entradas x,y. Nota que B es tambi�n el negador de A. Por ejemplo, '<' and '>=' son un par de negadores para la mayor�a de los tipos de datos.

A diferencia de CONMMUTATOR, un par de operadores unarios, pueden ser validamente marcados como negadores entre si; eso significar�a (A x) no es igual (B x) para todo x, o el equivalente para operadores unarios derechos.

Un operador de negaci�n debe tener el mismo tipo de argumento derecho y/o izquierdo como el operador en si mismo, entonces al igual que con COMMUTATOR, solo el nombre del operador necesita ser dado en la sentencia NEGATOR.

Proveer NEGATOR es de mucha ayuda para la optimizaci�n de las consultas desde que permite expresiones como NOT (x=y) ser simplificadas en x <> y. Esto aparece mas seguido de los que tu debes pensar, porque NOTs pueden ser insertados como una consecuencia de otras reconstrucciones.

Pares de operadores negadores pueden ser definidos usando el mismo m�todo explicado para pares de conmutadores.

RESTRICT (Restringir)

La sentencia RESTRICT, si es proveida, nombra una funci�n de estimaci�n selectiva de restricci�n para el operador (nota que esto es un nombre de funci�n, no un nombre de un operador). Las sentencias RESTRICT solamente tienen sentido para operadores binarios que retornan valores bouleanos. La idea detr�s de un estimador selectivo de restricci�n es suponer que fracci�n de las filas en una tabla satisfacer�n una sentencia de condici�n WHERE en el formulario RESTRICT clauses only make sense for

    		field OP constant
   
para el operador corriente y un valor constante particualr. Esto asiste al optimizador dandole alguna idea de como tantas filas van a ser eliminadas por la sentencia WHERE que tiene este formulario. (Qu� pasa si la constante est� a la izquierda, debes puedes estar pregunt�ndote? bien, esa es una de las cosas para las que sirve CONMMUTATOR...)

Escribiendo nuevas funciones de estimaci�n selectivas de restricci�n est� m�s all� del alcance de este cap�tulo, pero afortunadamente tu puedes usualmente solamente usar un estimador estandar del sistema para muchos de tus propios operadores. Estos son los estimadores estandars:

	eqsel		for =
	neqsel		for <>
	scalarltsel	for < or <=
	scalargtsel	for > or >=
   
Puede parecer un poco curioso que estas son las categorias, pero ellas tienen sentido si tu piensas acerca de ellas. '=' tipicamente aceptar� solamente una fracci�n peque�a de las filas en la tabla; '<>' tipicamente rechazar� solamente una fracci�n peque�a. '<' aceptara una fracci�n que depende de donde las constantes dadas quedan en el rango de valores para es columna de la tabla (la cual, solo ha pasado, es informaci�n recogida por el ANALIZADOR VACUUM y puesta disponible para el estimador selectivo). '<=' aceptar� una fracci�n un poquito mas laraga que '<' para las mismas constantes comparadas, pero son lo suficientemente cerradas para no ser costosas, especialmente desde que nosotros no estamos probalemente haciendo mejor que una aspera suposici�n de cualquier manera. Los mismos comentarios son aplicados a '>' y '>='.

Tu puedes frecuentemente escaparse de usar eqsel o neqsel para operadores que tienen muy alta o muy baja selectividad, incluso si ellas no son realmente equivalentes o no equivalentes. Por ejemplok, la expresi�n regular operadores emparejados (~,~*, etc.) usa eqsel sobre la suposici�n que ellos usualmente solo emparejen una peque�a fracci�nde entradas en una tabla.

Tu puedes usar scalarltsel y scalargtsel para comparaciones sobre tipos de datos que tienen cierto significado sencible de ser convertido en escalares num�ricos para comparaciones de rango. Es posible, a�adir el tipo de dato a aquellos entendidos por la rutina convert_to_scalar() in src/backend/utils/adt/selfuncs.c. (Eventualmente, esta rutina debe ser reemplazada por funciones per-datatype identificadas a trav�z de una columna de la tabla pg_type; pero eso no ha pasado todav�a.)Si tu no haces esto, las cosas seguiran funcionando, pero la estimaci�n del optimizador no estar�n tan bien como podrian.

Hay funciones adicionales de selectividad dise�adas para operadores geom�tricos en src/backend/adt/geo_selfuncs.c: areasel, positionsel, y contsel. En este escrito estas son solo nombradas, pero tu puedes querer usarlas (o incluso mejormejorarlas).

JOIN (unir)

La sentencia JOIN, si es proveida, nombra una funci�n de estimaci�n selectiva join para el operador (nota que esto es un nombre de una funci�n, no un nombre de un operador). Las sentencias JOIN solamente tienen sentido para operadores binarios que retorna valores bouleanos. La idea detr�s de un estimador selectivo join es suponer que fracci�n de las filas de un par de tablas satisfacer�n la condici�n de la sentencia WHERE del formulario

                table1.field1 OP table2.field2
     
para el operador corriente. Como la sentencia RESTRICT, esta ayuda al optimizador muy sustancialmente permitiendole deducir cual de las posibles secuencias join es probable que tome el menor trabajo.

como antes, este cap�tulo no procurar� explicar como escribir una funci�n estimadora selectiva join, pero solamente sugeriremos que tu uses uno de los estimadores estandars si alguna es aplicable:

	eqjoinsel	for =
	neqjoinsel	for <>
	scalarltjoinsel	for < or <=
	scalargtjoinsel	for > or >=
	areajoinsel	for 2D area-based comparisons
	positionjoinsel	for 2D position-based comparisons
	contjoinsel	for 2D containment-based comparisons
    

HASHES(desmenusamiento)

La sentencia HASHES, si est� presente, le dice al sistema que est� bien usar un metodo hash join para uno basado en join sobre este operador. HASHES solamente tiene sentido para operadores binarios que retornan valores binario, y en la pr�ctica el operador tiene que estar igualado a alg�n tipo de datos.

La suposici�n subyacente de hash join es que el operador join puede selamente retornar TRUE (verdadero) para pares de valores izquierdos o derechos. Si dos valores puestos en diferentes recipientes hash, El join nunca los comparar� a ellos del todo, implicitamente asumiendo que el resultado del operadior join debe ser FALSE (falso). Entonces nunca tiene sentido especificar HASHES para operadores que no se representan igualmente.

De hecho, la igualdad l�gica no es suficientemente buena; el operador tuvo mejor representaci�n por igualdad pura bit a bit, porque la funci�n hash sera computada sobre la representaci�n de la memoria de los valores sin tener en cuenta que significan los bits. Por ejemplo, igualdad de intervalos de tiempo no es igualdad bit a bit; el operador de igualdad de intervalo considera dos intervalos de tiempos iguales si ellos tienen la misma duraci�n, si son o no son sus puntos finales id�nticos. Lo que esto significa es que el uso de join "=" entre campos de intervalos producir�a resultados diferentes si es implementado como un hash join o si es implementado en otro modo, porque una fracci�n larga de los pares que deber�an igualar resultar�n en valores diferentes y nunca ser�n comparados por hash join. Pero si el optimizador elige usar una clase diferente de join, todoslos pares que el operador de igualdad dija que son iguales ser�n encontrados. No queremos ese tipo de inconsistencia, entonces no marcamos igualdad de intervalos como habilitados para hash.

Hay tambi�n modos de dependencia de m�quina en cuales un hash join puede fallar en hacer las cosas bien. Por ejemplo, si tu tipo de dato es una estructura en la cual puede haber bloque de bits sin interes, es inseguro marcar el operador de iguladad HASHES. (al menos, quizas, tu escribes tu otro operador para asegurarte que los bits sin uso son iguales a zero). Otro ejemplo es que los tipo de datos de punto flotante son inseguros para hash joins. Sobre m�quinas que cumplan los est�ndares de la IEEE de puntos flotantes, menos cero y mas cero son dos valores diferentes (diferentes patrones de bit) pero ellos est�n definidos para compararlos igual. Entonces, si la igualdad de punto flotante estuviese marcada, hashes, un mnos cero y un mas cero probablemente no ser�an igualados por hash join, pero ellos ser�an igualados por cualquier otro proceso join.

La �ltima linea es para la cual tu probablemente deber�as usar unicamente HASEHES para igualdad de operadores que son (o podr�a ser ) implementada por memcmp().

SORT1 and SORT2 (orden1 y orden2)

La sentencia SORT, si est� presente, le dice al sistema que esta permitido usar el m�todo merge join (unir join) para un join basado sobre el operador corriente. Ambos deben ser especificados si tampoco est�. El operador corriente debe ser igual para algunos pares de tipo de datos, y las sentencias SORT1 Y SORT2 nombran el operador de orden ('<' operador) para tipos de datos izquierdo y derecho respectivamente.

Merge join est� basado sobre la �dea de ordenar las tablas izquierdas y derechas en un orden y luego inspecionarlas en paralelo. Entonces, ambos tipos de datos deben ser aptos de ser ordenados completamente, y el operador join debe ser uno que pueda solamente tener �xito con pares de valores que caigan en el mismo lugar en la busqueda de orden. En pr�ctica esto significa que el operador join debe comportarse como iguldad. Pero distinto de hashjoin, cuando los tipos de datos izquierdos y derechos tuvieron qe ser mejor el mismo. ( o al menos iquivalentes bit a bit), es posible unir dos tipos de datos distintos tanto como sean ellos compatibles logicamente. Por ejemplo, el operador de igualdad int2-versus-int4 es unible. Solo necesitamos operadores de oprden que traigna ambos tipos de datos en una secuencia l�gica compatible.

Cuando se especifican operadores operadores sort merge, el operador corriente y ambos operadores referenciados deben retornar valores bouleanos; el operador SORT1 debe tener ambos tipo de datos de entrada iguales al tipo de argumento izquierdo del operador corriente y el operador SORT2 debe tener ambos tipos de datos de entrada iguales al tipo de argumento derecho del operador corriente. (como con COMMUTATOR y NEGATOR, esto significa que el nombre del operador es suficiente para especificar el operador, y el sistema es capaz de hacer entradas de operador silenciosas si tu definiste el operador de igualda antes que los otros.

En pr�ctica tu debes solo escribier sentencias SORT para un operador '=', y los dos operadores referenciados deben ser siempre nombrados '<'. Tratando de usar merge join con operadores nombrados nada mas resultar� en confusiones inesperadas, por razones que veremos en un momento.

Hay restricciones adicionales sobre operadores que tu marcas mergegoinables. Estas restricciones no son corrientemente chequeadas por CREATE OPERATE, pero un merge join puede fallar en tiempo de ejecuci�n si alguna no es verdad:

  • El operador de igualdad mergejoinable debe tener un conmutador (El mismo si los dos tipos de datos son iguales, o un operador de igualdad relativo si son diferentes.).

  • Debe haber operadores de orden '<' and '>' teniendo los mismos tipos de datos izquierdo y derecho de entrada como el operados mergejinable en si mismo. Estos operadores debenser nombrados '<' and '>'; tu no tienes opcion en este problema, desde que no hay provici�n para especificarlos explicitamente. Nota que si los tipo de datos izquierdo y derechos son diferentes, ninguno de estos operadors es el mismo que cualquier operador SORT. pero ellos tuvieron mejores ordenados la compativilidad de los valores de dato con los operadores SORT, o o mergejoin fallar� al funcionar.