Azure Database for PostgreSQL + Apache AGEのインデックス

追記（2024.10.28）：最新版

インデックスの張り方が良く分からなかったので、AGEのデータがどのようになっているのかを調べてみた。

psqlで接続後、サーチパスを設定してrelationを見る。

postgres=> SET search_path = ag_catalog, "$user", public;
SET
postgres=> \d
              リレーション一覧
  スキーマ  |   名前   |  タイプ  | 所有者  
------------+----------+----------+---------
 ag_catalog | ag_graph | テーブル | azuresu
 ag_catalog | ag_label | テーブル | azuresu
(2 行)

postgres=> select * from ag_graph;
 graphid |    name    | namespace  
---------+------------+------------
   29862 | actorfilms | actorfilms
(1 行)

postgres=> select * from ag_label;
       name       | graph | id | kind |          relation           |        seq_name         
------------------+-------+----+------+-----------------------------+-------------------------
 _ag_label_vertex | 29862 |  1 | v    | actorfilms._ag_label_vertex | _ag_label_vertex_id_seq
 _ag_label_edge   | 29862 |  2 | e    | actorfilms._ag_label_edge   | _ag_label_edge_id_seq
 Actor            | 29862 |  3 | v    | actorfilms."Actor"          | Actor_id_seq
 Film             | 29862 |  4 | v    | actorfilms."Film"           | Film_id_seq
 ACTED_IN         | 29862 |  5 | e    | actorfilms."ACTED_IN"       | ACTED_IN_id_seq
(5 行)

要はActor / Film / ACTED_INというテーブルが作られると。actorfilms._*にまとめて入ってて、それぞれをviewで切り出してるのかな？

postgres=> select * from actorfilms._ag_label_vertex limit 10;
       id        |         properties          
-----------------+-----------------------------
 844424930131969 | {"name": "Fred Astaire"}
 844424930131970 | {"name": "Lauren Bacall"}
 844424930131971 | {"name": "Brigitte Bardot"}
 844424930131972 | {"name": "John Belushi"}
 844424930131973 | {"name": "Ingmar Bergman"}
 844424930131974 | {"name": "Ingrid Bergman"}
 844424930131975 | {"name": "Humphrey Bogart"}
 844424930131976 | {"name": "Marlon Brando"}
 844424930131977 | {"name": "Richard Burton"}
 844424930131978 | {"name": "James Cagney"}
(10 行)

postgres=> select * from actorfilms._ag_label_edge limit 10;
        id        |    start_id     |      end_id      | properties 
------------------+-----------------+------------------+------------
 1407374883553281 | 844424930131969 | 1125899906842625 | {}
 1407374883553282 | 844424930131969 | 1125899906842626 | {}
 1407374883553283 | 844424930131969 | 1125899906842627 | {}
 1407374883553284 | 844424930131969 | 1125899906842628 | {}
 1407374883553285 | 844424930131969 | 1125899906842629 | {}
 1407374883553286 | 844424930131969 | 1125899906842630 | {}
 1407374883553287 | 844424930131969 | 1125899906842631 | {}
 1407374883553288 | 844424930131969 | 1125899906842632 | {}
 1407374883553289 | 844424930131969 | 1125899906842633 | {}
 1407374883553290 | 844424930131969 | 1125899906842634 | {}
(10 行)

postgres=> select * from actorfilms."Actor" limit 10;
       id        |         properties          
-----------------+-----------------------------
 844424930131969 | {"name": "Fred Astaire"}
 844424930131970 | {"name": "Lauren Bacall"}
 844424930131971 | {"name": "Brigitte Bardot"}
 844424930131972 | {"name": "John Belushi"}
 844424930131973 | {"name": "Ingmar Bergman"}
 844424930131974 | {"name": "Ingrid Bergman"}
 844424930131975 | {"name": "Humphrey Bogart"}
 844424930131976 | {"name": "Marlon Brando"}
 844424930131977 | {"name": "Richard Burton"}
 844424930131978 | {"name": "James Cagney"}
(10 行)

postgres=> select * from actorfilms."ACTED_IN" limit 10;
        id        |    start_id     |      end_id      | properties 
------------------+-----------------+------------------+------------
 1407374883553281 | 844424930131969 | 1125899906842625 | {}
 1407374883553282 | 844424930131969 | 1125899906842626 | {}
 1407374883553283 | 844424930131969 | 1125899906842627 | {}
 1407374883553284 | 844424930131969 | 1125899906842628 | {}
 1407374883553285 | 844424930131969 | 1125899906842629 | {}
 1407374883553286 | 844424930131969 | 1125899906842630 | {}
 1407374883553287 | 844424930131969 | 1125899906842631 | {}
 1407374883553288 | 844424930131969 | 1125899906842632 | {}
 1407374883553289 | 844424930131969 | 1125899906842633 | {}
 1407374883553290 | 844424930131969 | 1125899906842634 | {}
(10 行)

なるほど、childなのか。

postgres=> \d+ actorfilms._ag_label_vertex
                                                                                            テーブル"actorfilms._ag_label_vertex"
     列     | タイプ  | 照合順序 | Null 値を許容 |                                                             デフォルト                                                              | ストレージ | 圧縮 | 統計目標 | 説明 
------------+---------+----------+---------------+-------------------------------------------------------------------------------------------------------------------------------------+------------+------+----------+------
 id         | graphid |          | not null      | _graphid(_label_id('actorfilms'::name, '_ag_label_vertex'::name)::integer, nextval('actorfilms._ag_label_vertex_id_seq'::regclass)) | plain      |      |          | 
 properties | agtype  |          | not null      | agtype_build_map()                                                                                                                  | extended   |      |          | 
インデックス:
    "_ag_label_vertex_pkey" PRIMARY KEY, btree (id)
子テーブル: actorfilms."Actor",
            actorfilms."Film"
アクセスメソッド: heap

インデックスをActorに張るとなると、propertiesかな？

postgres=> \d actorfilms."Actor"
                                                                    テーブル"actorfilms.Actor"
     列     | タイプ  | 照合順序 | Null 値を許容 |                                                   デフォルト                                                    
------------+---------+----------+---------------+-----------------------------------------------------------------------------------------------------------------
 id         | graphid |          | not null      | _graphid(_label_id('actorfilms'::name, 'Actor'::name)::integer, nextval('actorfilms."Actor_id_seq"'::regclass))
 properties | agtype  |          | not null      | agtype_build_map()
継承元: actorfilms._ag_label_vertex

で、以下、と。

postgres=> CREATE INDEX ON actorfilms."Actor" USING GIN (properties);
CREATE INDEX

出来たみたい。

postgres=> \d actorfilms."Actor"
                                                                    テーブル"actorfilms.Actor"
     列     | タイプ  | 照合順序 | Null 値を許容 |                                                   デフォルト                                                    
------------+---------+----------+---------------+-----------------------------------------------------------------------------------------------------------------
 id         | graphid |          | not null      | _graphid(_label_id('actorfilms'::name, 'Actor'::name)::integer, nextval('actorfilms."Actor_id_seq"'::regclass))
 properties | agtype  |          | not null      | agtype_build_map()
インデックス:
    "Actor_properties_idx" gin (properties)
継承元: actorfilms._ag_label_vertex

EXPLAINしてみる。

postgres=> EXPLAIN
    SELECT * FROM cypher('actorfilms', $$
        MATCH (n:Actor {name:'Keanu Reeves'})-[r:ACTED_IN]->(m:Film)
        RETURN n,r,m
        LIMIT 1 $$)
        AS (n agtype, r agtype, m agtype);
                                                  QUERY PLAN                                                   
---------------------------------------------------------------------------------------------------------------
 Limit  (cost=28.35..668.78 rows=1 width=96)
   ->  Nested Loop  (cost=28.35..128114.73 rows=200 width=96)
         Join Filter: (r.end_id = m.id)
         ->  Hash Join  (cost=28.35..4079.60 rows=200 width=67)
               Hash Cond: (r.start_id = n.id)
               ->  Seq Scan on "ACTED_IN" r  (cost=0.00..3329.73 rows=191873 width=29)
               ->  Hash  (cost=28.22..28.22 rows=10 width=38)
                     ->  Bitmap Heap Scan on "Actor" n  (cost=11.57..28.22 rows=10 width=38)
                           Recheck Cond: (properties @> '{"name": "Keanu Reeves"}'::agtype)
                           ->  Bitmap Index Scan on "Actor_properties_idx"  (cost=0.00..11.57 rows=10 width=0)
                                 Index Cond: (properties @> '{"name": "Keanu Reeves"}'::agtype)
         ->  Materialize  (cost=0.00..984.75 rows=41050 width=41)
               ->  Seq Scan on "Film" m  (cost=0.00..779.50 rows=41050 width=41)
(13 行)

Bitmap Index Scan on "Actor_properties_idx"となっているので、一応インデックスは利いてるのかな。propertiesに対するGIN（汎用転置）インデックスではなく、nameに対するbtreeインデックスが欲しいが。

postgres=> CREATE INDEX ON actorfilms."Actor" ((properties->>'name'));
ERROR:  invalid input syntax for type agtype
行 1: CREATE INDEX ON actorfilms."Actor" ((properties->>'name'));
                                                        ^
DETAIL:  Expected agtype value, but found "name".
CONTEXT:  agtype data, line 1: name

「agtypeの値が欲しいのに、何”name”とか渡してるんだ、こら」って怒られた。じゃあ、”Actor”と同じように、ダブルクォートか。

postgres=> CREATE INDEX ON actorfilms."Actor" ((properties->>'"name"'));
CREATE INDEX

postgres=> \d actorfilms."Actor"
                                                                    テーブル"actorfilms.Actor"
     列     | タイプ  | 照合順序 | Null 値を許容 |                                                   デフォルト                                                    
------------+---------+----------+---------------+-----------------------------------------------------------------------------------------------------------------
 id         | graphid |          | not null      | _graphid(_label_id('actorfilms'::name, 'Actor'::name)::integer, nextval('actorfilms."Actor_id_seq"'::regclass))
 properties | agtype  |          | not null      | agtype_build_map()
インデックス:
    "Actor_expr_idx" btree ((properties ->> '"name"'::agtype))
    "Actor_properties_idx" gin (properties)
継承元: actorfilms._ag_label_vertex

いぇーいw
先に作った方は要らん。

postgres=> DROP INDEX actorfilms."Actor_properties_idx";
DROP INDEX

改めて、EXPLAIN。

postgres=> EXPLAIN
    SELECT * FROM cypher('actorfilms', $$
        MATCH (n:Actor {name:'Keanu Reeves'})-[r:ACTED_IN]->(m:Film)
        RETURN n,r,m
        LIMIT 1 $$)
        AS (n agtype, r agtype, m agtype);
                                      QUERY PLAN                                       
---------------------------------------------------------------------------------------
 Limit  (cost=0.00..781.75 rows=1 width=96)
   ->  Nested Loop  (cost=0.00..156349.02 rows=200 width=96)
         Join Filter: (r.end_id = m.id)
         ->  Nested Loop  (cost=0.00..32313.89 rows=200 width=67)
               Join Filter: (n.id = r.start_id)
               ->  Seq Scan on "ACTED_IN" r  (cost=0.00..3329.73 rows=191873 width=29)
               ->  Materialize  (cost=0.00..203.24 rows=10 width=38)
                     ->  Seq Scan on "Actor" n  (cost=0.00..203.19 rows=10 width=38)
                           Filter: (properties @> '{"name": "Keanu Reeves"}'::agtype)
         ->  Materialize  (cost=0.00..984.75 rows=41050 width=41)
               ->  Seq Scan on "Film" m  (cost=0.00..779.50 rows=41050 width=41)
(11 行)

あ、ダメだ。”Actor”をSeq Scanしてらぁ。

結論
追記（2024.10.28）
1. 関連

結論

ひとまず、propertiesに対するGINで。

CREATE INDEX ON actorfilms."Actor" USING GIN (properties);

というのが、少し書き直してみた（6.4倍ぐらい速くなった）のコードの裏側です…

追記（2024.10.28）

これ、ひょっとしてACTED_INの、start_idにbtreeインデックスを張ったら、速くなる？ Seq Scanのコストがバカ高い…。

postgres=> CREATE INDEX ON actorfilms."ACTED_IN" (start_id);
CREATE INDEX

postgres=> \d+ actorfilms."ACTED_IN"
                                                                                         テーブル"actorfilms.ACTED_IN"
     列     | タイプ  | 照合順序 | Null 値を許容 |                                                      デフォルト                                                       | 
ストレージ | 圧縮 | 統計目標 | 説明 
------------+---------+----------+---------------+-----------------------------------------------------------------------------------------------------------------------+------------+------+----------+------
 id         | graphid |          | not null      | _graphid(_label_id('actorfilms'::name, 'ACTED_IN'::name)::integer, nextval('actorfilms."ACTED_IN_id_seq"'::regclass)) | plain      |      |          | 
 start_id   | graphid |          | not null      |                                                                                                                       | plain      |      |          | 
 end_id     | graphid |          | not null      |                                                                                                                       | plain      |      |          | 
 properties | agtype  |          | not null      | agtype_build_map()                                                                                                    | extended   |      |          | 
インデックス:
    "ACTED_IN_start_id_idx" btree (start_id)
継承元: actorfilms._ag_label_edge
アクセスメソッド: heap

postgres=> EXPLAIN
    SELECT * FROM cypher('actorfilms', $$
        MATCH (n:Actor {name:'Keanu Reeves'})-[r:ACTED_IN]->(m:Film)
        RETURN n,r,m
        LIMIT 1 $$)
        AS (n agtype, r agtype, m agtype);
                                                      QUERY PLAN                                                      
----------------------------------------------------------------------------------------------------------------------
 Limit  (cost=81.81..86.75 rows=1 width=96)
   ->  Hash Join  (cost=81.81..1071.56 rows=200 width=96)
         Hash Cond: (m.id = r.end_id)
         ->  Seq Scan on "Film" m  (cost=0.00..779.50 rows=41050 width=41)
         ->  Hash  (cost=79.31..79.31 rows=200 width=67)
               ->  Nested Loop  (cost=11.99..79.31 rows=200 width=67)
                     ->  Bitmap Heap Scan on "Actor" n  (cost=11.57..28.22 rows=10 width=38)
                           Recheck Cond: (properties @> '{"name": "Keanu Reeves"}'::agtype)
                           ->  Bitmap Index Scan on "Actor_properties_idx"  (cost=0.00..11.57 rows=10 width=0)
                                 Index Cond: (properties @> '{"name": "Keanu Reeves"}'::agtype)
                     ->  Index Scan using "ACTED_IN_start_id_idx" on "ACTED_IN" r  (cost=0.42..4.87 rows=24 width=29)
                           Index Cond: (start_id = n.id)
(12 行)

あ、やっぱりそうですよね…。

これは反対に、FilmからActorに”ACTED_BY”というEdgeを作って、そっちもインデックスを張った方が良い気もするが、Filmのスキャンコストを下げるか。

postgres=> CREATE INDEX ON actorfilms."Film" (id);
CREATE INDEX
postgres=> EXPLAIN
    SELECT * FROM cypher('actorfilms', $$
        MATCH (n:Actor {name:'Keanu Reeves'})-[r:ACTED_IN]->(m:Film)
        RETURN n,r,m
        LIMIT 1 $$)
        AS (n agtype, r agtype, m agtype);
                                                   QUERY PLAN                                                   
----------------------------------------------------------------------------------------------------------------
 Limit  (cost=0.71..2.32 rows=1 width=96)
   ->  Nested Loop  (cost=0.71..321.75 rows=200 width=96)
         ->  Nested Loop  (cost=0.42..254.24 rows=200 width=67)
               ->  Seq Scan on "Actor" n  (cost=0.00..203.19 rows=10 width=38)
                     Filter: (properties @> '{"name": "Keanu Reeves"}'::agtype)
               ->  Index Scan using "ACTED_IN_start_id_idx" on "ACTED_IN" r  (cost=0.42..4.87 rows=24 width=29)
                     Index Cond: (start_id = n.id)
         ->  Index Scan using "Film_id_idx" on "Film" m  (cost=0.29..0.31 rows=1 width=41)
               Index Cond: (id = r.end_id)
(9 行)

結局、こうなるかなぁ。

CREATE INDEX ON actorfilms."Actor" USING GIN (properties);
CREATE INDEX ON actorfilms."Actor" (id);
CREATE INDEX ON actorfilms."Film" USING GIN (properties);
CREATE INDEX ON actorfilms."Film" (id);
CREATE INDEX ON actorfilms."ACTED_IN" (start_id);
CREATE INDEX ON actorfilms."ACTED_IN" (end_id);

結論

追記（2024.10.28）

関連