0

I have a table with thousands of records with vehicles data, and this table is referenced by the table, where images are stored for these vehicles.

This second table about millions of records because every vehicle has about 15 images.

Now I am facing an issue - when I want to delete a vehicle, it takes about 5 minutes. There is set ON DELETE CASCADE for a vehicle.

Can you help me, which index would be created and where, to make that deletion fast, please?

\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
1
  • 2
    catalog_image_vehicle_id_16ba1633 should be sufficient. If it not, try an EXPLAIN (ANALYZE, BUFERS) too see what is going on.
    – jjanes
    Oct 24, 2021 at 21:42

1 Answer 1

0

You need an index on the table that is referencing the table from which you want to delete:

CREATE INDEX ON catalog_image (vehicle_id);

This is because every DELETE on catalog_vehicle has to make sure that the row about to be deleted is not referenced. It does that by running a statements equivalent to

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

This statement has to perform a slow sequential scan if catalog_image contains many rows.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.