NOT EXISTS中のwhere句をcase when で分岐させると取得に時間がかかりすぎる。

Question

前提・実現したいこと

以下のテーブルから
・同一レコードからcreate_dateが最新のものだけを取得したい
・同一レコードかどうかの判断は、
house_codeに値が入っていればhouse_codeが同じもの
house_codeがnullか''なら、product_nameが同じもの。

CREATE TABLE tbl_products (
    product_id integer NOT NULL,
    product_name character varying (100),
    house_code character varying (48),
    create_date timestamp without time zone DEFAULT now() NOT NULL
);

例えばこのようなレコードから
|ID| product_name| house_code| create_date|
|:--|:--:|--:|
1|い|1111|2020-02-18 12:46:37|
2|い| |2020-02-19 12:46:37|
3|は|2222|2020-02-20 12:46:37|
4|は| |2020-02-21 12:46:37|
5|ほ|3333|2020-02-22 12:46:37|
6|へ|3333|2020-02-23 12:46:37|
7|へ| |2020-02-24 12:46:37|

こういった結果のデータを取得したいと思っています。
|ID| product_name| house_code| create_date|
|:--|:--:|--:|
2|い| |2020-02-19 12:46:37|
4|は| |2020-02-21 12:46:37|
6|へ|3333|2020-02-23 12:46:37|
7|へ| |2020-02-24 12:46:37|

そこで現在こちらのsqlを作成したのですが、件数が10000で取得に5分以上かかります。

SELECT *
FROM tbl_products AS a
WHERE NOT EXISTS (
  SELECT 1
  FROM tbl_products AS b
  WHERE
  (CASE 
        WHEN length(a.house_code) > 0 THEN a.product_name = b.product_name
        ELSE a.house_code = b.house_code
        END
  )
  AND a.create_date < b.create_date
);


**NOT EXISTS中のwhere句をcase when で分岐させている**のが遅くさせている原因だと思うのですが、他にやり方が分からず立ち止まっている状態です。
何かほかに良いsqlがあれば教えていただけないでしょうか？

試したこと

サブクエリでgroup byを使って取得するsqlで上手くいくのではないかと、現在試行錯誤しています。

補足情報（FW/ツールのバージョンなど）

現在postgresql9.6で開発をしています。

Comments

[workaholist]

まずグループ化してそのグループごとに最新日付のレコードを取りたいのだと思いますが、
グループ化する条件をかくにんさせてください。

①house_codeに値が入っていればhouse_codeが同じもの
②house_codeがnullか''なら、product_nameが同じもの
の条件がよくわかりませんでした。
比較元がhouse_codeなし、比較先がhouse_codeあり
であればどちらのルールが採用されるのですか？

[m.ts10806]

実行計画とってみてください。

[shakesuke]

返信ありがとうございます。

>>workaholistさん
比較元を採用します。

>>m.ts10806さん
postgresqlのクエリ実行計画を取ったことがないので、少々お時間をください。
取れたらこちらに書き込みます。
ちなみに現在phpPgAdminで直接こちらのsqlを実行しても5分ほどかかってしまいます。

[m.ts10806]

Analyze もつけたほうがどこに問題があるか時間かかっているか観れますので、そちらを。

[shakesuke]

>>m.ts10806さん

アドバイスありがとうございます。
やってみます。

[yureighost]

取得結果で分かりづらいのが、「い」や「は」は一つだけなのに、
「へ」は二つ取れているところですね。
複数のproduct_nameに跨るhouse_codeの場合はproduct_nameが複数取得されるのは容認するってことでしょうか。

Answer 1

通常は別テーブルとして持つべきマスターテーブルとしての情報と履歴テーブルとしての情報をいっしょくたに持っていませんか？そのために同一キー(今回はproduct_name)の中で最新のデータを取得しなければならないのが負荷になっているのでは？

質問にインデックスの情報も追記した方が良いです。

CASE
    WHEN length(a.house_code) > 0 THEN a.product_name = b.product_name
    ELSE a.house_code = b.house_code
END

見るからにパフォーマンスが悪そう。

実行計画 PostgreSQLの実行計画について調べてみた が効率的かも確認しましょう。

EXISTS, NOT EXISTS などの副問合せを多用しなければならない場合もテーブル設計の正規化が間違っている場合が多いです。

Answer 2

主な理由としては履歴テーブルと混在してるせいで処理速度が落ちてる以外なんでもない

実行計画もなにもためさんけどインデックス処理ができるSQLを組みましょう
以下のように二つに分けたほうが早い場合が多いです。

SELECT *
FROM tbl_products AS a
WHERE 

NOT EXISTS (
  SELECT 1
  FROM tbl_products AS b
  WHERE a.product_name = b.product_name
  AND a.house_code IS NULL
  AND a.create_date < b.create_date -- 履歴テーブルは基本別にしましょう。遅い原因の一つです。
  )

AND 

NOT EXISTS (
  SELECT 1
  FROM tbl_products AS b
  WHERE a.house_code = b.house_code
  AND a.create_date < b.create_date -- 同上
  )
);

Answer 3

10000件ではテストできておりませんが。例題とは同じ結果

with house_tbl AS (select a.* from tbl_products a join
(select house_code, max(create_date) create_date from tbl_products where house_code is not null group by house_code having(count(*)>1)) b
using (house_code,create_date)
),    name_tbl AS (select a.* from tbl_products a join
(select product_name, max(create_date) create_date from tbl_products where house_code is null group by 1) b
using (product_name,create_date)
)
select * from house_tbl
union all
select name_tbl.* from name_tbl left join house_tbl using(product_id) where house_tbl.product_id is null
order by 1;

WHERE句のCASEにて、こんな書き方できるんですね。

SELECT a.* FROM tbl_products AS a,
tbl_products AS b
WHERE
  CASE WHEN a.house_code is NULL THEN a.product_name = b.product_name
       ELSE a.house_code = b.house_code
  END
  AND a.create_date>b.create_date
;

Answer 4

https://www.postgresql.jp/document/9.4/html/tutorial-window.html

rank関数を使えばできそうな気がします（PostgreSQLは持っていないので動作未検証ですが）。

-- 同一house_codeで、create_dateが大きい1件を取得する（house_code がnullでも''でもない場合）
SELECT ID, product_name, house_code, create_date
FROM
  (SELECT ID, product_name, house_code, create_date,
          rank() OVER (PARTITION BY house_code ORDER BY create_date DESC) AS pos
     FROM tbl_products 
     WHERE (house_code IS NOT NULL AND house_code <> '')
  ) AS house_code_rank
WHERE pos = 1

-- これの上と下のSELECT結果を連結する
UNION ALL

-- 同一product_nameで、create_dateが大きい1件を取得する（house_code がnullか''の場合）
SELECT ID, product_name, house_code, create_date
FROM
  (SELECT ID, product_name, house_code, create_date,
          rank() OVER (PARTITION BY product_name ORDER BY create_date DESC) AS pos
     FROM tbl_products 
     WHERE (house_code IS NULL OR house_code = '')
  ) AS product_name_rank
WHERE pos = 1;

Comments

[Orlofsky]

URLは https://teratail.com/help/question-tips#questionTips3-7 の [リンク] で [リンク先のタイトル](http...) に修正してください。