車の通過情報を保管するテーブル設計

問題点

車のレースのシミュレーションをするアプリを趣味で作っています。
ゲーム(Unityなど)ではなく、単純なシミュレーション的なアプリです。

複数のポイントを通過した車のみの情報を取得するSQLを発行したところ今までのDB設計では、
SQLの実行時間が非常に遅くなってしまい、困っています。

実現したいこと

• SQLの実行時間ができるだけ早くなるようなテーブル設計または、SQLを発行したい。

前提

TypeScript, Express, TypeORM, Postgresでレースのシミュレーションシステムを作っています。

設計したテーブルは下記の通りです。

※お約束カラムは省略しています。(created_at など, *がついているのは、PKやFKです。

• check_point_manageテーブル

物理名	型	備考
*race_id	integer	レースID
*check_point_id	integer	チェックポイントのID
race_start_time	timestamp	レースの開始時間

• car_manageテーブル

物理名	型	備考
*check_point_id	integer	チェックポイントのID
*car_id	integer	車のID（レースＡの車両1番 ... ）
pass_time	timestamp	チェックポイントの通過時間
prev_check_point_id	integer	ひとつ前に通過したチェックポイントのID(通過する車が保持しているデータ)

仕様面

・チェックポイント１が スタート地点, チェックポイントMAXが ゴール地点の想定
・チェックポイントNを複数回通過できてそのデータも保存される(スタートとゴール以外
・チェックポイント通過したが、データが記録されなかったら欠落として扱い、抽出しない
・1回のレースでだいたい3万台かつチェックポイントは8つが最大
・通過時間は気にしない、チェックポイントを順番に全て通っているデータであればよい

問題のSQL

以下のようにチェックポイント１～N までを通過しているのかを保証した車の情報のみを抽出したい。
将来的には、チェックポイントごとの情報連結条件が増える想定である。(prev_check_point_id = check_point_id 以外の条件も増えそう...

SELECT cm_10.* // DISTINCTがいる？ FROM ( SELECT cm.* FROM check_point_manage as cpm INNER JOIN car_manage as cm ON cpm.check_point_id _ cm.check_point_id WHERE cpm.race_id = 100 AND cm.check_point_id = 10 // チェックポイント１０ ) as cm_10 INNER JOIN ( SELECT cm.* FROM check_point_manage as cpm INNER JOIN car_manage as cm ON cpm.check_point_id _ cm.check_point_id WHERE cpm.race_id = 100 AND cm.check_point_id = 9 // チェックポイント９ ) as cm_9 // チェックポイント10と9のIDを比較 ON cm_10.prev_check_point_id = cm_9.check_point_id INNER JOIN ( ... チェックポイント１まで繰り返し

試したこと

• EXPLAIN, ANALYZEなどを確認してprev_check_point_idにインデックスを付けたが実行時間早くならず...チェックポイントごとの別名テーブル同士の比較が何度も発生している。
• すべてのチェックポイントを通過したこと証明するものをまた別のテーブルでデータを持つか、そもそもテーブル構造が悪いか考えましたが、良い案が浮かばず...という状況。

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

ver.

• postgreSQL 15.4