SQLの最適化

以下のクエリで一覧を表示したかったのですが、かなり時間がかかっていました。

select * from "test_order"
where ("order" is null)
and ("cart" in (select "cart" from "test_order_item"))
order by "cart" desc
;

総実行時間: 37,102.619 ミリ秒
とても実用ではありません。

古い PostgreSQL は IN句が極端に遅かったので、以下のように変更してみました。

select * from "test_order"
where ("order" is null)
and (exists(select "cart" from "test_order_item" where "test_order_item"."cart" = "test_order"."cart"))
order by "cart" desc
;

総実行時間: 47,345.715 ミリ秒
さらに悪化しました。

WHERE 条件から JOIN に変更してみました。

select "test_order".*
from "test_order"
join (select "cart" from "test_order_item" group by "cart") as "test_order_item"
on ("test_order_item"."cart" = "test_order"."cart")
where ("test_order"."order" is null)
order by "test_order"."cart" desc
;

総実行時間: 15,586.267 ミリ秒
劇的な改良となりました。

ちなみに GROUP -> DISTINCT とした場合。

select "test_order".*
from "test_order"
join (select distinct "cart" from "test_order_item") as "test_order_item"
on ("test_order_item"."cart" = "test_order"."cart")
where ("test_order"."order" is null)
order by "test_order"."cart" desc
;

総実行時間: 28,382.042 ミリ秒
改悪です。

IN 句よりもサブクエリで GROUP して JOIN の方が良い場合もあるようですね。