Snowflakeで地理空間系関数(ST_*）をJOINの条件に利用すると激重になる件の解決方法

はじめに

データウェアハウスのSnowflakeでは地理空間データ型もサポートされており、様々な地理空間系の解析を行うことができます。

今回は、テーブル結合で地理空間系関数を利用した際に、処理時間が想定以上に掛かってしまった事象とその対応策について紹介します！

発生したトラブル

以下のように、ジオメトリを持っているログデータにメッシュコードテーブルを外部結合するクエリを実行した際に、想定以上に処理に時間がかかってしまいました。
感覚としては、PostGISで実行すると約10秒程度で完了するはずの処理が、Snowflakeでは5分程度かかってしまうイメージです。

-- 処理が遅くなってしまった際のクエリのイメージ
SELECT
    log_id,
    log_time,
    mc.code as mesh_code,
    g.geom
FROM
    gps_log g
    LEFT JOIN mesh mc ON st_within(g.geom, mc.geom) --メッシュコードのテーブルを外部結合
;

この件について、Snowflakeのサポートに問い合わせさせていただいたところ、下記のとおり回答をいただきました。

　現状、ST_ から始まる​地理空間関数を結合条件に使用した場合の最適化が内部結合 (INNER JOIN) にのみ適用される。
　外部結合 (OUTER JOIN)、準結合 (Semi-join)、アンチ結合 (Anti-join) については、現在実装中。

※2023年5月2日時点で回答いただいた内容であることにご留意ください。

対応策

今回の対応策は単純に「外部結合で地理空間関数を使わない」です！
今回実行したクエリでは、LEFT JOINでログデータのテーブルの情報は全て残す形でデータを抽出したいので、以下の流れでデータ抽出する流れに変更しました。

1. "INNER JOIN"で（内部結合で！）、ログテーブルとメッシュコードテーブルを結合して抽出した結果をWITH句で保持する。
2. メッシュコードテーブルとJOINされなかったレコードをログテーブルから抽出する。
3. １と2のデータをユニオンする。

-- 改善版のクエリのイメージ
WITH within_logs as (
    SELECT
        log_id,
        log_time,
        mc.code as mesh_code,
        g.geom
    FROM
        gps_log g
        JOIN mesh mc ON st_within(g.geom, mc.geom) -- INNER JOINに変更
    ),
    without_logs as (
        SELECT
        log_id,
        log_time,
        -- mc.code as mesh_code,　メッシュコードテーブルをJOINしないためselectしない
        geom
    FROM
        gps_log g
    WHERE
        g.log_id not in (select log_id from within_logs) -- JOINされなかったレコードのみを選択
)
SELECT
    log_id,
    log_time,
    mesh_code,
    geom
FROM
    within_logs
UNION ALL
SELECT
    log_id,
    log_time,
    null as mesh_code,
    geom
FROM
    without_logs
;

かなり冗長なクエリになってしまっていますが、速度は改善されました！！！
もう少しスマートな方法があればコメントでご教授いただけると嬉しいです🙇‍♂️