Tengo algunas consultas que utilizan condiciones geoespaciales. Estas consultas se están ejecutando sorprendentemente lentas. Inicialmente pensé que era el cálculo geoespacial en sí, pero reduciendo todo a solo ST_POLYGON(TO_GEOGRAPHY(...)), sigue siendo muy lento. Esto tendría sentido si cada fila tuviera su propio polígono, pero la condición usa un polígono estático en la consulta:

SELECT 
    ST_POLYGON(TO_GEOGRAPHY('LINESTRING(-95.75122850074004 28.793166796020444,-95.68622920563344 30.207416499279063,-94.5162418937178 32.56537633083211,-90.94128066286225 34.24734047810797,-88.17881062083825 36.812423897251634,-86.13133282498448 38.15341651409619,-85.28634198860107 38.66275098353796,-84.37635185711038 38.789523129087826,-82.84886842210855 38.4848923369382,-82.32887406125734 37.820427257446994,-82.26387476615074 36.96838022284757,-82.03637723327772 36.00158943485101,-80.99638851157454 35.34155096040939,-78.52641529752944 34.62260477275565,-77.51892622337955 34.005211031324734,-78.26641811710381 31.1020568651834,-80.24889661785029 29.926151366059756,-83.59636031583283 28.793166796020444,-95.75122850074004 28.793166796020444)'))
FROM TABLE(GENERATOR(ROWCOUNT=>1000000))

Snowflake debería poder darse cuenta de que solo necesita calcular este polígono una vez para toda la consulta. Sin embargo, cuantas más filas se agregan, más lento se vuelve. En x-small, esta consulta tarda más de un minuto. Donde esta consulta:

SELECT 
    'LINESTRING(-95.75122850074004 28.793166796020444,-95.68622920563344 30.207416499279063,-94.5162418937178 32.56537633083211,-90.94128066286225 34.24734047810797,-88.17881062083825 36.812423897251634,-86.13133282498448 38.15341651409619,-85.28634198860107 38.66275098353796,-84.37635185711038 38.789523129087826,-82.84886842210855 38.4848923369382,-82.32887406125734 37.820427257446994,-82.26387476615074 36.96838022284757,-82.03637723327772 36.00158943485101,-80.99638851157454 35.34155096040939,-78.52641529752944 34.62260477275565,-77.51892622337955 34.005211031324734,-78.26641811710381 31.1020568651834,-80.24889661785029 29.926151366059756,-83.59636031583283 28.793166796020444,-95.75122850074004 28.793166796020444)'
FROM TABLE(GENERATOR(ROWCOUNT=>3000000))

(se agregaron 2 mm más filas para que coincida con el recuento de bytes)

Se puede completar en 2 segundos.

Intenté "precalcular" el polígono yo mismo con una declaración WITH pero SF se da cuenta de que WITH es redundante y lo descarta. También intenté establecer una variable de sesión, pero no se puede establecer un valor complejo como este como variable.

Creo que esto es un error.

1
micah 4 mar. 2021 a las 01:36

1 respuesta

La mejor respuesta

Las funciones geoespaciales están en vista previa por ahora, y el equipo está trabajando duro en todo tipo de optimizaciones.

Para este caso, quiero señalar que hacer que el polígono sea una tabla de una sola fila ayudaría, pero aún así esperaría un mejor rendimiento a medida que el equipo obtenga esta función de la versión beta.

Déjame crear una tabla con una fila, el polígono:

create or replace temp table poly1
as
select ST_POLYGON(TO_GEOGRAPHY('LINESTRING(-95.75122850074004 28.793166796020444,-95.68622920563344 30.207416499279063,-94.5162418937178 32.56537633083211,-90.94128066286225 34.24734047810797,-88.17881062083825 36.812423897251634,-86.13133282498448 38.15341651409619,-85.28634198860107 38.66275098353796,-84.37635185711038 38.789523129087826,-82.84886842210855 38.4848923369382,-82.32887406125734 37.820427257446994,-82.26387476615074 36.96838022284757,-82.03637723327772 36.00158943485101,-80.99638851157454 35.34155096040939,-78.52641529752944 34.62260477275565,-77.51892622337955 34.005211031324734,-78.26641811710381 31.1020568651834,-80.24889661785029 29.926151366059756,-83.59636031583283 28.793166796020444,-95.75122850074004 28.793166796020444)'
       )) polygon
;

Para ver si esto ayudaría, probé una unión cruzada de un millón de filas:

select *
from poly1, TABLE(GENERATOR(ROWCOUNT=>1000000));

Tarda 14 segundos y, en el generador de perfiles de consultas, puede ver que la mayor parte del tiempo se dedicó a un TO_OBJECT​(​GET_PATH​(​POLY1​.​POLYGON, '_shape'​)​​ interno.

enter image description here

Lo que es interesante notar es que la operación anterior se ocupa principalmente de la representación ascii del polígono. La ejecución de operaciones sobre este polígono es mucho más rápida:

select st_area(polygon)
from poly1, TABLE(GENERATOR(ROWCOUNT=>1000000));

Esta consulta debería haber tomado más tiempo (encontrar el área de un polígono suena más complicado que simplemente seleccionarlo), pero resulta que solo tomó 7 segundos (~ la mitad).

enter image description here

Gracias por el informe y el equipo continuará optimizando casos como este.


Para cualquiera que tenga curiosidad sobre el polígono en particular en la pregunta, es un buen corazón:

enter image description here

3
Felipe Hoffa 4 mar. 2021 a las 04:46