如何為引用表正確創建索引

我有一個包含數千條車輛數據記錄的表，該表被該表引用，其中儲存了這些車輛的圖像。

第二張表大約有數百萬條記錄，因為每輛車都有大約 15 張圖像。

現在我面臨一個問題 - 當我想刪除車輛時，大約需要 5 分鐘。車輛設置為 ON DELETE CASCADE。

你能幫我嗎，要創建哪個索引以及在哪裡創建，以便快速刪除？

\d catalog_vehicle
                                          Table "public.catalog_vehicle"
      Column        |           Type           | Collation | Nullable |                   Default
---------------------+--------------------------+-----------+----------+---------------------------------------------
id                  | bigint                   |           | not null | nextval('catalog_vehicle_id_seq'::regclass)
type                | smallint                 |           | not null |
trim                | character varying(64)    |           | not null |
slug                | character varying(32)    |           | not null |
year                | smallint                 |           | not null |

...
...
... 


Indexes:
   "catalog_vehicle_pkey" PRIMARY KEY, btree (id)
   "catalog_vehicle_slug_key" UNIQUE CONSTRAINT, btree (slug)
   "catalog_vehicle_color_id_5691a4b9" btree (color_id)
...
...
...
   "catalog_vehicle_slug_508ef2db_like" btree (slug varchar_pattern_ops)
Check constraints:
...
...
...
Foreign-key constraints:
...
...
...
Referenced by:
   TABLE "catalog_image" CONSTRAINT "catalog_image_vehicle_id_16ba1633_fk_catalog_vehicle_id" FOREIGN KEY (vehicle_id) REFERENCES catalog_vehicle(id) DEFERRABLE INITIALLY DEFERRED


soldvehicle=# \d catalog_image
                                     Table "public.catalog_image"
  Column   |          Type          | Collation | Nullable |                  Default
------------+------------------------+-----------+----------+-------------------------------------------
id         | bigint                 |           | not null | nextval('catalog_image_id_seq'::regclass)
image      | character varying(100) |           | not null |
width      | integer                |           | not null |
height     | integer                |           | not null |
vehicle_id | bigint                 |           | not null |
Indexes:
   "catalog_image_pkey" PRIMARY KEY, btree (id)
   "catalog_image_vehicle_id_16ba1633" btree (vehicle_id)
Check constraints:
   "catalog_image_height_check" CHECK (height >= 0)
   "catalog_image_width_check" CHECK (width >= 0)
Foreign-key constraints:
   "catalog_image_vehicle_id_16ba1633_fk_catalog_vehicle_id" FOREIGN KEY (vehicle_id) REFERENCES catalog_vehicle(id) DEFERRABLE INITIALLY DEFERRED

您需要一個表上的索引，該索引引用您要從中刪除的表：

CREATE INDEX ON catalog_image (vehicle_id);

這是因為每個DELETEoncatalog_vehicle都必須確保不引用即將刪除的行。它通過執行相當於

SELECT EXISTS (SELECT 1 FROM ONLY catalog_image WHERE vehicle_id = ...);

catalog_image如果包含許多行，則該語句必須執行慢速順序掃描。

引用自：https://dba.stackexchange.com/questions/301587