質問をすることでしか得られない、回答やアドバイスがある。

15分調べてもわからないことは、質問しよう!

ただいまの
回答率

89.99%

経路列挙モデルにておいて、各ノードと、その子孫となる末端ノードを紐付けたい

解決済

回答 3

投稿

  • 評価
  • クリップ 2
  • VIEW 1,607

gittib_gittib

score 98

今、経路列挙モデルでツリー構造を管理しているテーブルがあるのですが、この各ノードに対して、それに紐づく末端のノードを取得する方法を考えています。
一応、末端の子孫ノードは抽出できているものの、時間がかかりすぎるので効率化したいのですが、お知恵を貸していただけないでしょうか。

モデルのイメージは以下の様な感じです。

-- 経路列挙モデルを採用しているテーブル
select node_id, node_path from t_node

 node_id | node_path
---------+-----------
       1 | 1
       2 | 2
       3 | 1-3
       4 | 1-3-4
       5 | 2-5
       6 | 2-5-6
       7 | 2-5-6-7
       8 | 2-8
       9 | 1-3-9
      10 | 1-3-9-10

-- view_last_node は末端ノードを抽出するビューです
select node_id, node_path from view_last_node

 node_id | node_path
---------+-----------
       4 | 1-3-4
       7 | 2-5-6-7
       8 | 2-8
      10 | 1-3-9-10


ここで今、以下の様なSQLで紐づくノードを抽出しています。マテリアライズド・ビューとしているのですが、現状1万5千レコードほどでもリフレッシュに1分以上かかっています。
node_idはプライマリキーで、node_pathにもインデックスは貼っています。

select
    tn.node_id,
    tn.node_path,
    tlast.node_id as last_node,
    tlast.node_path as last_path
from t_node as tn
inner join t_node as tlast on(tlast.node_path like tn.node_path || '%')
where tlast.node_id in(
    select node_id from view_last_node
);

 node_id | node_path | last_node | last_path
---------+-----------+-----------+-------------
       1 | 1         |         4 | 1-3-4
       1 | 1         |        10 | 1-3-9-10
       2 | 2         |         7 | 2-5-6-7
       2 | 2         |         8 | 2-8
       3 | 1-3       |         4 | 1-3-4
       3 | 1-3       |        10 | 1-3-9-10
       4 | 1-3-4     |         4 | 1-3-4
       5 | 2-5       |         7 | 2-5-6-7
       6 | 2-5-6     |         7 | 2-5-6-7
       7 | 2-5-6-7   |         7 | 2-5-6-7
       8 | 2-8       |         8 | 2-8
       9 | 1-3-9     |        10 | 1-3-9-10
      10 | 1-3-9-10  |        10 | 1-3-9-10


ちょっとした事でも構いませんので、何かアドバイスいただければと思います。
よろしくお願い致します。m(_ _)m

  • 気になる質問をクリップする

    クリップした質問は、後からいつでもマイページで確認できます。

    またクリップした質問に回答があった際、通知やメールを受け取ることができます。

    クリップを取り消します

  • 良い質問の評価を上げる

    以下のような質問は評価を上げましょう

    • 質問内容が明確
    • 自分も答えを知りたい
    • 質問者以外のユーザにも役立つ

    評価が高い質問は、TOPページの「注目」タブのフィードに表示されやすくなります。

    質問の評価を上げたことを取り消します

  • 評価を下げられる数の上限に達しました

    評価を下げることができません

    • 1日5回まで評価を下げられます
    • 1日に1ユーザに対して2回まで評価を下げられます

    質問の評価を下げる

    teratailでは下記のような質問を「具体的に困っていることがない質問」、「サイトポリシーに違反する質問」と定義し、推奨していません。

    • プログラミングに関係のない質問
    • やってほしいことだけを記載した丸投げの質問
    • 問題・課題が含まれていない質問
    • 意図的に内容が抹消された質問
    • 広告と受け取られるような投稿

    評価が下がると、TOPページの「アクティブ」「注目」タブのフィードに表示されにくくなります。

    質問の評価を下げたことを取り消します

    この機能は開放されていません

    評価を下げる条件を満たしてません

    評価を下げる理由を選択してください

    詳細な説明はこちら

    上記に当てはまらず、質問内容が明確になっていない質問には「情報の追加・修正依頼」機能からコメントをしてください。

    質問の評価を下げる機能の利用条件

    この機能を利用するためには、以下の事項を行う必要があります。

回答 3

+1

select
  tn.node_id
  , tn.node_path
  , tlast.node_id as last_node
  , tlast.node_path as last_path 
from
  t_node as tn 
  inner join view_last_node as tlast 
    on (tlast.node_path like tn.node_path || '%');


これで少しは早くなるかもしれません。

後は実行計画を見て遅くなっている原因を探しましょう。
また、likeでインデックスを使うにはロケールが関係しているので確認してみてください。
テキスト検索の方法とインデックス — Let's Postgres

投稿

  • 回答の評価を上げる

    以下のような回答は評価を上げましょう

    • 正しい回答
    • わかりやすい回答
    • ためになる回答

    評価が高い回答ほどページの上位に表示されます。

  • 回答の評価を下げる

    下記のような回答は推奨されていません。

    • 間違っている回答
    • 質問の回答になっていない投稿
    • スパムや攻撃的な表現を用いた投稿

    評価を下げる際はその理由を明確に伝え、適切な回答に修正してもらいましょう。

  • 2016/01/08 18:59

    回答ありがとうございます。
    わずかに速くなりましたが、やはり1%程です。
    like検索ですが、explainで確認する限り、インデックスはちゃんと使われていると思います。

    キャンセル

check解決した方法

0

Fettile Forest モデルという、素晴らしいモデルを見つけたので、そちらを共有して自己解決とさせていただきます。
実際に検証はしていませんが、この方法ならば副問い合わせのSQLたった1文で末端ノードを検出できます。

投稿

  • 回答の評価を上げる

    以下のような回答は評価を上げましょう

    • 正しい回答
    • わかりやすい回答
    • ためになる回答

    評価が高い回答ほどページの上位に表示されます。

  • 回答の評価を下げる

    下記のような回答は推奨されていません。

    • 間違っている回答
    • 質問の回答になっていない投稿
    • スパムや攻撃的な表現を用いた投稿

    評価を下げる際はその理由を明確に伝え、適切な回答に修正してもらいましょう。

0

view_last_node も、マテリアライズドビューでしょうか??

投稿

  • 回答の評価を上げる

    以下のような回答は評価を上げましょう

    • 正しい回答
    • わかりやすい回答
    • ためになる回答

    評価が高い回答ほどページの上位に表示されます。

  • 回答の評価を下げる

    下記のような回答は推奨されていません。

    • 間違っている回答
    • 質問の回答になっていない投稿
    • スパムや攻撃的な表現を用いた投稿

    評価を下げる際はその理由を明確に伝え、適切な回答に修正してもらいましょう。

  • 2016/01/08 15:58

    view_last_node は通常のビューですが、SQL全体からすると1%にも満たない程度のコストしか掛かっていないため、マテリアライズド・ビューにしてもほとんど改善は見込めないと思っています。
    回答ありがとうございます。

    キャンセル

15分調べてもわからないことは、teratailで質問しよう!

  • ただいまの回答率 89.99%
  • 質問をまとめることで、思考を整理して素早く解決
  • テンプレート機能で、簡単に質問をまとめられる