Why is the 'DELETE' operation on a PostgreSQL database table unusually very slow?

I have encountered a significant performance issue when executing the 'DELETE' operation on a PostgreSQL database table. The execution time for deleting 15488 records was 79423.768 ms which is very slow compared to other operations like 'INSERT' or 'SELECT'. I would appreciate any insights or suggestions on why this might be happening and possible approaches to optimize the delete operation. Background: I am using PostgreSQL Engine version 12.14 as the backend for my application, and I have noticed that deleting records from one table takes an unexpectedly long time. The tables involved have indexes and constraints defined, and the database size is relatively small, expected to grow upto a few GBs. However, the issue appears to be more pronounced for this specific table, while others perform adequately. **Hardware** is AWS db.t2.micro instance with 1 cpu core, 1 (GiB) of memory and 20 (GiB) of General Purpose SSD for storage. column_name_loading Table schema, the table we trying to delete from. | Column Name | Data Type | Description | |-------------|-----------|-------------| | id | TEXT | Primary key | | hash | TEXT | Primary key | | date_from | TIMESTAMP | Primary key | | date_to | TIMESTAMP | | | measurement_location_uuid| UUID | Primary Key, Foreign key | | column_name | TEXT | Not null | | statistic_type_id | TEXT | | | is_ignored | BOOLEAN | | | notes | TEXT | | | update_at | TIMESTAMP | | | updated_by| UUID | | As you can see, the above table has a composite primary key involving 4 columns. There are two tables that are having a foreign key reference to the column_name_loading table First table

ALTER  TABLE
logger_main_config_column_name_loading 
ADD
CONSTRAINT column_name_loading_fkey FOREIGN KEY (
column_name_loading_measurement_location_uuid,
column_name_loading_id,
column_name_loading_hash,
column_name_loading_date_from
) REFERENCES column_name_loading(measurement_location_uuid, id, hash, date_from);

Second table

ALTER  TABLE
logger_measurement_config_column_name_loading
ADD
CONSTRAINT column_name_loading_fkey FOREIGN KEY (
column_name_loading_measurement_location_uuid,
column_name_loading_id,
column_name_loading_hash,
column_name_loading_date_from
) REFERENCES column_name_loading(measurement_location_uuid, id, hash, date_from);

The measurement_location_location_uuid foreign keys in the above tables refer to the same table that the column_name_loading refers to. #### Delete query

DELETE FROM column_name_loading WHERE measurement_location_uuid='7f925e5c-3d34-417e-8782-052a69692b2b'

#### Postgres Query Analysis

"Delete on column_name_loading  (cost=0.00..1232.60 rows=15476 width=6) (actual time=44.797..44.801 rows=0 loops=1)"
"  Buffers: shared hit=31799 dirtied=462"
"  ->  Seq Scan on column_name_loading  (cost=0.00..1232.60 rows=15476 width=6) (actual time=0.016..16.843 rows=15488 loops=1)"
"        Filter: (measurement_location_uuid = 'ed67b48b-c48a-4727-87cd-5a5f4d27fa7a'::uuid)"
"        Rows Removed by Filter: 17280"
"        Buffers: shared hit=823"
"Planning Time: 0.103 ms"
"Trigger for constraint column_name_loading_fkey: time=39562.957 calls=15488"
"Trigger for constraint column_name_loading_fkey: time=39759.667 calls=15488"
"Execution Time: 79423.768 ms"

The column_name_loading table can have a few million records in future and also the tables that refer to it through foreign keys. We want to be able to do select and delete operations in a few minutes at least. #### Things we tried - Dropping the cascade delete on measurement_location_uuid foreign key and we had no performance improvement - Dropping the cascade delete on the two foreign keys referencing the column_name_loading table and we had no performance improvement - Created an index on measurement_location_uuid foreign key, showed some improvement but the delete for those many records still causes a timeout. We have also referred to this question for some insights and tried the indexing and dropping cascade delete. https://dba.stackexchange.com/questions/37034/very-slow-delete-in-postgresql-workaround I would really appreciate some help on the following: 1. Why is the 'DELETE' operation on certain tables in my PostgreSQL database noticeably slower compared to other operations? 2. Are there any factors within the database schema or configuration that might contribute to this slow performance? 3. What strategies or optimizations can I implement to improve the efficiency and speed of the 'DELETE' operation on these tables? Please note that I have already examined the query execution plans, and there doesn't appear to be any obvious bottlenecks or long-running queries causing the delay. Additionally, I have ensured that all relevant indexes are properly maintained and up to date. I would appreciate any guidance or suggestions on how to troubleshoot and optimize the 'DELETE' operation on PostgreSQL database tables. Thank you! **EDIT:** On trying the indexes mentioned in the answer below, there was some improvement in the delete from column_name_loading query. Following are the results: "Delete on column_name_loading (cost=176.99..1313.87 rows=0 width=0) (actual time=276.497..276.498 rows=0 loops=1)" " -> Bitmap Heap Scan on column_name_loading (cost=176.99..1313.87 rows=13510 width=6) (actual time=0.596..100.690 rows=12800 loops=1)" " Recheck Cond: (measurement_location_uuid = '68dd4fae-c2bf-413d-ba3c-cb63b062307f'::uuid)" " Heap Blocks: exact=334" " -> Bitmap Index Scan on idx_column_name_loading_measurement_location_uuid (cost=0.00..173.61 rows=13510 width=0) (actual time=0.280..0.280 rows=12800 loops=1)" " Index Cond: (measurement_location_uuid = '68dd4fae-c2bf-413d-ba3c-cb63b062307f'::uuid)" "Planning Time: 0.066 ms" "Trigger for constraint column_name_loading_fkey: time=256.829 calls=12800" "Trigger for constraint column_name_loading_fkey: time=243.210 calls=12800" "Execution Time: 778.337 ms" On trying with more number of records in the column_name_loading (102,528) and the logger_measurement_config_column_name_loading table: "Delete on column_name_loading (cost=317.62..3212.88 rows=0 width=0) (actual time=260.420..260.421 rows=0 loops=1)" " -> Bitmap Heap Scan on column_name_loading (cost=317.62..3212.88 rows=25461 width=6) (actual time=2.778..11.249 rows=25600 loops=1)" " Recheck Cond: (measurement_location_uuid = 'd6f9d978-20e0-49a1-a6fc-cad0865500d9'::uuid)" " Heap Blocks: exact=657" " -> Bitmap Index Scan on idx_column_name_loading_measurement_location_uuid (cost=0.00..311.25 rows=25461 width=0) (actual time=2.663..2.664 rows=25600 loops=1)" " Index Cond: (measurement_location_uuid = 'd6f9d978-20e0-49a1-a6fc-cad0865500d9'::uuid)" "Planning Time: 7.639 ms" "Trigger for constraint column_name_loading_fkey: time=881.762 calls=25600" "Trigger for constraint column_name_loading_fkey: time=659.424 calls=25600" "Execution Time: 1806.113 ms" "Delete on column_name_loading (cost=474.61..5140.76 rows=0 width=0) (actual time=622.069..622.071 rows=0 loops=1)" " -> Bitmap Heap Scan on column_name_loading (cost=474.61..5140.76 rows=38492 width=6) (actual time=66.150..89.596 rows=38400 loops=1)" " Recheck Cond: (measurement_location_uuid = '4736f9df-3d53-4896-bc72-e48a118bbfab'::uuid)" " Heap Blocks: exact=1004" " -> Bitmap Index Scan on idx_column_name_loading_measurement_location_uuid (cost=0.00..464.99 rows=38492 width=0) (actual time=65.912..65.913 rows=38400 loops=1)" " Index Cond: (measurement_location_uuid = '4736f9df-3d53-4896-bc72-e48a118bbfab'::uuid)" "Planning Time: 54.825 ms" "Trigger for constraint column_name_loading_fkey: time=1118.807 calls=38400" "Trigger for constraint column_name_loading_fkey: time=805.790 calls=38400" "Execution Time: 2553.990 ms" "Delete on column_name_loading (cost=815.01..9706.90 rows=0 width=0) (actual time=693.918..693.919 rows=0 loops=1)" " -> Bitmap Heap Scan on column_name_loading (cost=815.01..9706.90 rows=67431 width=6) (actual time=10.708..212.115 rows=64000 loops=1)" " Recheck Cond: (measurement_location_uuid = 'f902ce5b-baf8-4260-bad7-83df2a283a0f'::uuid)" " Heap Blocks: exact=1614" " -> Bitmap Index Scan on idx_column_name_loading_measurement_location_uuid (cost=0.00..798.15 rows=67431 width=0) (actual time=10.024..10.024 rows=64000 loops=1)" " Index Cond: (measurement_location_uuid = 'f902ce5b-baf8-4260-bad7-83df2a283a0f'::uuid)" "Planning Time: 7.719 ms" "Trigger for constraint column_name_loading_fkey: time=1727.786 calls=64000" "Trigger for constraint column_name_loading_fkey: time=1275.343 calls=64000" "Execution Time: 3707.070 ms" The queries run faster, if the cascade delete is removed and the index is added on the entire foreign key. However, it still took roughly 2 seconds to delete a few records from 100,000 records and it only increases as the number of records increases. Around 4 seconds to delete a few thousand records from around 400,000 records. Is that speed normal? This table can have up to 9-10 million records. Now imagine the time it would take to delete a few thousand records from this table.